SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


restore several .ndf files to one mdf file


restore several .ndf files to one mdf file

Author
Message
George Dickson
George Dickson
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6068 Visits: 11771
You cannot combine files during the restore.
George Dickson
George Dickson
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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!!
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8131 Visits: 9974
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

George Dickson
George Dickson
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8131 Visits: 9974
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

George Dickson
George Dickson
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search