Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restore several .ndf files to one mdf file Expand / Collapse
Author
Message
Posted Tuesday, August 25, 2009 12:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 10:21 AM
Points: 20, Visits: 169
I have a backup from another server that has nine secondary files (ndf). It is a small db and I see no reason for it to be broke up since we have only two drives on new server. How can I restore this db combining all the ndf files into one mdf file?

fromdb D:\Databases\todb.mdf D PRIMARY
fromdb_data_file1 D:\Databases\todb_data_file1.ndf D file1
fromdb_data_file2 D:\Databases\todb_data_file2.ndf D file2
fromdb_data_file3 D:\Databases\todb_data_file3.ndf D file3
fromdb_data_file4 D:\Databases\todb_data_file4.ndf D file4
fromdb_data_file5 D:\Databases\todb_data_file5.ndf D file5
fromdb_data_file6 D:\Databases\todb_data_file6.ndf D file6
fromdb_data_file7 D:\Databases\todb_data_file7.ndf D file7
fromdb_data_file8 D:\Databases\todb_data_file8.ndf D file8
fromdb_data_file9 D:\Databases\todb_data_file9.ndf D file9
fromdb_log E:\DataLogs\fromdb_log.ldf L NULL

Post #777003
Posted Tuesday, August 25, 2009 1:03 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 3,113, Visits: 11,540
You cannot combine files during the restore.

Post #777010
Posted Tuesday, August 25, 2009 1:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 10:21 AM
Points: 20, Visits: 169
Then can I restore it with all the file and filegroups, back it up (somehow), and then restore it to one mdf file?

Thanks!!
Post #777013
Posted Tuesday, August 25, 2009 1:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
No - you cannot restore a database with multiple files and reduce the number of files. What you have to do is restore the database with all of the files, then remove the extra files using shrinkfile and emptyfile (look it up in help).

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #777034
Posted Tuesday, August 25, 2009 2:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 10:21 AM
Points: 20, Visits: 169
I have checked out SHRINKFILE and EMPTYFILE and they would work fine as long as the files were in the same filegroup. In my case, each file is in its own file group so I would have to change all the files to be in one filegroup in order to get the above mentioned items to work.

How do I get all my files into one filegroup?
Post #777089
Posted Tuesday, August 25, 2009 3:02 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
The only way to remove those files is to move the objects in those filegroups to the other filegroup. Drop and recreating the indexes on those filegroups should take care of it.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #777107
Posted Tuesday, August 25, 2009 3:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 16, 2011 10:21 AM
Points: 20, Visits: 169
OK. I give up. SQL 2005 wins 1-0, this time. I created a new database with one mdf file and one ldf file and imported all the tables over to the new database. Seems to have worked fine.

Thanks everyone for your help!!
Post #777113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse