August 25, 2009 at 12:58 pm
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
August 25, 2009 at 1:03 pm
You cannot combine files during the restore.
August 25, 2009 at 1:05 pm
Then can I restore it with all the file and filegroups, back it up (somehow), and then restore it to one mdf file?
Thanks!!
August 25, 2009 at 1:38 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 25, 2009 at 2:47 pm
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?
August 25, 2009 at 3:02 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 25, 2009 at 3:15 pm
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!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply