October 11, 2012 at 6:54 am
I have a SQL Server 2008 r2 database comprised of a single mdf and ldf file.
Is is possible to restore a backup (bak) of this database to a database consisting of multiple mdf files and, if so, what is the best way to do this?
Any help would be appreciated.
Thanks
October 11, 2012 at 7:17 am
October 11, 2012 at 7:42 am
Forgive my ignorance, as this is something new for me.
I performed a backup of a database consisting of a single mdf and ldf (the backup file has a .bak extension).
I need to be able to copy the backup file (.bak) to another server and use the backup file to do a restore to a database that has more than one data file.
Is this possible? I did not get this from the link. If it is possible, how would I go about this?
October 11, 2012 at 7:51 am
It's not possible in a single operation. You could restore the database then create new files in the restored database. Can I ask why you need to do this? It might help us to make suggestions for you.
John
October 11, 2012 at 7:52 am
You are going to have to restore it based on the backup file.
If you want to move objects to an ndf then you will need to do so after the restore.
You will need to specify the WITH MOVE Option:
RESTORE DATABASE Test2005
FROM DISK = 'H:\Backups\Complete\TEST2005_backup_201209111035.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'Test2005' TO 'G:\MSSQL\Data\Test2005.mdf',
MOVE 'Test2005_log' TO 'G:\MSSQL\Log\Test2005_log.ldf'
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply