How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.
> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files.
>> This was basically because we backed the files up to a UNIX system that
>> had
>> a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape
>> backup system and it would seem preferable to append all the 2G .ndf
>> files
>> into one 10G .mdf file on the new system.
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want
>> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf
>> files
>> on the new Sql Server 2005 server.
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
>> Is there some tool or method I can use that will allow me to create a
>> single
>> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
>> appended .ndf Sql Server 2000 data files '
> Once the files are empty you can use ALTER DATABASE to remove the file.
>> Thanks in advance.
>> Barry
>> in Oregon
> HTH
> John|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files. This was basically because we backed the files up to a UNIX system
>> that had a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape backup system and it would seem preferable to append all the 2G .ndf
>> files into one 10G .mdf file on the new system.
> There isn't any pressing need to do that.
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want to move and recreate the original 2G .mdf and the 6 additional 2G
>> .ndf files on the new Sql Server 2005 server.
>> Is there some tool or method I can use that will allow me to create a
>> single .mdf on the Sql Server 2005 side that consists of the original
>> .mdf and appended .ndf Sql Server 2000 data files '
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>