CODEWITHSUNDEEP
sqlrestore
user2704019
user2704019

Reputation: 71

Seeking to restore from multiple bak files to a test SQL database

we have a large SQL db that we split into 4 separate bak files in a nightly backup so it can be more easily sent offsite. We use this statement (db names changed)

BACKUP DATABASE [Data] TO
DISK = 'd:\back\data1.bak',
DISK = 'd:\back\data2.bak',
DISK = 'd:\back\data3.bak',
DISK = 'd:\back\data4.bak'
WITH  INIT, NOUNLOAD, NAME = 'Data backup',  NOSKIP ,  STATS = 10,  NOFORMAT

All four of the backups have the same logical names for the mdf and ldf files in the bak.

I want to be able to restore these four backups into a different database on the server for testing. I found a t-sql script in this post which I think will do this but I am not sure. Can someone help?

I'm thinking I could adapt and run the script as follows:

RESTORE DATABASE Data_test FROM
DISK = 'd:\back\data1.bak',
DISK = 'd:\back\data2.bak',
DISK = 'd:\back\data3.bak',
DISK = 'd:\back\data4.bak'
WITH MOVE 'Prod_Data' TO 'D:\SQLDb\Data_Test1.mdf',
MOVE 'Prod_Data' TO 'D:\SQLDb\Data_Test2.ndf',
MOVE 'Prod_Data' TO 'D:\SQLDb\Data_Test3.ndf',
MOVE 'Prod_Data' TO 'D:\SQLDb\Data_Test4.ndf',
MOVE 'Prod_Log' TO 'C:\SQLtlogs\Data_test1.ldf'

Do you think this would work? And will this test db not conflict with the prod db from which it was restored? Any help would be great, thanks.

Upvotes: 4

Views: 23780

Answers (2)

CrimeWire
CrimeWire

Reputation: 54

I used this TRANSACT-SQL command to do almost the same thing you requested. The only difference is that I was only moving each logical file to a single physical file. My command (modified to use your example) looks like this:

RESTORE DATABASE Data_test FROM
DISK = 'd:\back\data1.bak',
DISK = 'd:\back\data2.bak',
DISK = 'd:\back\data3.bak',
DISK = 'd:\back\data4.bak'
WITH MOVE 'Prod_Data' TO 'D:\SQLDb\Data_Test1.mdf',
MOVE 'Prod_Log' TO 'C:\SQLtlogs\Data_test1.ldf'

Upvotes: 1

user2704019
user2704019

Reputation: 71

OK, I actually got this to work with the GUI for SSMS. All you have to do is choose Tasks, Restore Database, choose from device, then add all four files of the separate bak files, then click OK and do the restore with Overwrite, modifying the file names for mdf and ldf as needed so they are for the test db and not production. SSMS knows the four files are part of the same media set and reassembles them into the mdf and ldf file. Looks like I did not need a script after all.

Upvotes: 3

Related Questions