+ Reply to Thread
Results 1 to 7 of 7

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

  1. 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




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



    "frostbb" 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




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



    "frostbb" 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




  4. !Re: How to create a single 2005 .mdf file from a 2000 .mdf and multipl

    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" 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




  5. !Re: How to create a single 2005 .mdf file from a 2000 .mdf and multipl

    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" 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




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

    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" meat@hotmail.com> wrote in
    message news:ODTolWCNHHA.320@TK2MSFTNGP06.phx.gbl...
    >
    >
    > "frostbb" 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
    >
    >
    >




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

    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" meat@hotmail.com> wrote in
    message news:ODTolWCNHHA.320@TK2MSFTNGP06.phx.gbl...
    >
    >
    > "frostbb" 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
    >
    >
    >




+ Reply to Thread