Getting DB to test server

[Sully]

Hi All,

We have a test server, each night a job runs which drops a specific DB on the test server, runs a DTS package to copy the same DB from the live server back to the test server, and then does a backup of the DB on the test server. The problem is, the copy does not work if anything is accessing the live DB, and the drop has already taken place. I can recreate the DB on test, but this seems more difficult than it should be. I'm thinking I could have a backup of the live DB executed, placing the file on the test server, then run the restore to the test DB. But, I don't know if I can do that in one job, so I can check the success/failure of the previous step. Also, is there a better way to accomplish this? Any help would be appreciated.

[szlamany]

This series of backup commands will backup a database (even if open) and create a "new copy" with a different name on the same server.

BACKUP DATABASE Acctfiles 
   TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Acctfiles_Copy.bak'
RESTORE FILELISTONLY 
   FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Acctfiles_Copy.bak'
RESTORE DATABASE Acctfiles_Copy
   FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Acctfiles_Copy.bak'
   WITH MOVE 'Acctfiles_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Acctfiles_Copy.mdf',
   MOVE 'Acctfiles_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Acctfiles_Copy.ldf'
GO

To do this to a different server, do only the first BACKUP command on the production server - copy the file as the second step of the JOB to a disk that can be reached by the other server.

Then on the other server do the last two commands the RESTORE (need to drop the DB on that server first).

Simply remove the _COPY parts and you get an exact image of the DB on the test server.

We do stuff like this all the time - test servers, laptops - all over the place.

[edit] - BTW - it's got to be two JOBS - each server does there own part...

Careful renaming of the files after the copy and the restore will allow you to see that the handshake is working every night.

[Sully]

Thanks for replying szlamany...in fact I had seen this kind of response while I was researching this, and I am currently implementing it in case nothing else came up...I was hoping for something cleaner. But I do appreciate your help!

[Sully]

Yeah, I went ahead and implemented this strategy...I created the backup file name with a date stamp so I could ensure the restore step would fail if the backup had failed. So the live server backs up the DB and places the backup file on the test server, with the date within the name. Approximately 15 minutes later the test server job executes, clearing any connections to the DB, and then restoring the file with the current month and day in the name. It worked fine over night, but if anyone has another idea, I would love to hear it. Thanks.