SQL Server Performance Forum – Threads Archive

restore multiple files db to db with 1 file

Hi I have a database that have couple of mdf,ndf files : they all located
on one drive. Can I restore this database to have only one mdf file and one ldf And how?
thank you

Try this: Create a database with any name. Use EM to restore multiple files to new database and set Force Restore over existing database.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I don’t think you can do this. you need to restore both. ensure there’s enough space in the first one for ALL the data, then use dbcc shrinkfile with the EMPTYFILE clause on the 2nd file. Then you should be able to do an ALTER DATABASE .. MODIFY FILEGROUP … REMOVE FILE statement to get rid of the 2nd file that you don’t want. If I recall correctly, if your db has 2 data files when backed up, it has to be restored with both files. An alternative is to create a new database and use DTS/transfer objects to copy all the data into the new one. Tom Pullen
DBA, Oxfam GB

thank you thomas: it worked at least in development.
Still running on production ( 3 hours only one file ) use testDB
go
dbcc shrinkfile (testFile,EMPTYFILE)
go alter database testDB
remove file testFile

]]>