Scripting an automated SQLServer 2008 DR move

Question

We use the built in logshipping in SQLServer to logship to our DR site but once in a month do a DR test which requires us to move back and forth between our Live and BAckup servers. We run multiple (30) databases on the system so manually backing up the final logs and disabling the jobs is too much work and takes too long. I though no problem, I will script it but have run into trouble with it always complaninig that the final logship is too early to apply even though I dont export the final log until putting the database into norecovery mode. Firstly, does any one no a simple and reliable way of doing this? I have lokoed at some 3rd party software (redgate sqlbackup I think it was) but that didnt make it easy in this situation either. What I want to be able to do is basically run a script (a series of stored procedures) to get me to DR and run another to get me back with no dataloss. My scripts are very simplistic at the moment but here they are:

2 servers Primary Paris Secondary ParisT

The StartAgentJobAndWait is a script written by someone else (ta) and just checks the jobs have finished or quits it if it never ends.

At the moment I am just using a test database called BOB2 but if I can get it working will pass in the database and job names.

from PARIS:

/* Disable backup job */
exec msdb..sp_update_job @job_name = 'LSBackup_BOB2', @enabled = 0
exec PARIST.msdb..sp_update_job @job_name = 'LSCopy_PARIS_BOB2', @enabled = 0
exec PARIST.msdb..sp_update_job @job_name = 'LSRestore_PARIS_BOB2', @enabled = 0

exec PARIST.master.dbo.DRStage2

ParisT DRStage2
DECLARE @RetValue varchar (10)

EXEC @RetValue = StartAgentJobAndWait  LSCopy_PARIS_BOB2 , 2
SELECT ReturnValue=@RetValue

if @RetValue = 1
 begin
  print 'The Copy Task completed Succesffuly'
 END

ELSE
 print 'The Copy task failed, This may or may not be a problem, check restore state of database'

SELECT @RetValue = 0

EXEC @RetValue = StartAgentJobAndWait  LSRestore_PARIS_BOB2 , 2
SELECT ReturnValue=@RetValue

if @RetValue = 1
 begin
  print 'The Restore Task completed Succesffuly'
 END

ELSE
 print 'The Copy task failed, This may or may not be a problem, check restore state of database'

exec PARIS.master.dbo.DRStage3

/* Do the last logship and move it to Trumpington */
BACKUP log "BOB2"
to disk='c:\drlogshipping\BOB2.bak'
with compression, norecovery
EXEC xp_cmdshell 'copy c:\drlogshipping \\192.168.7.11\drlogshipping'

EXEC PARIST.master.dbo.DRTransferFinish


AS
BEGIN

restore database "BOB2"
from disk='c:\drlogshipping\bob2.bak'
with recovery

Answer 1

Does this sometimes work though? I have had to do the same kind of thing for DR testing, but haven't run into this. What you are doing should technically work.

Have you checked if the LSBackup job is currently running on that database before you start the LSCopy job? It sounds like you are just missing the most recent backup prior to your norecovery backup. The LSCopy job may just not be getting everything.

Try running LSCopy again after you run the norecovery backup, by that time the scheduler will have finished all of the backups and you should be fine. Also, if you set the LSCopy job to save the output to a file, I believe you will see an error if there is a log backup it can't copy over (like if it is in use because a backup is currently writing to it).

Answer 2

Just to give you an idea about creating auto-generation scripts for restores, here is one I wrote to automate the generation of a restore script for a particular database.

http://tenbulls.co.uk/2011/02/08/t-sql-tuesday-015-%E2%80%93-spend-time-now-to-save-time-later/

Please note that much if not all you want to do can be done through a mixture of Powershell, SMO and T-SQL. It just depends on whether the time is better spent on a total or partial solution. Personally I'd go for partial so you will always want to have a manual element involved when performing tasks like these.

Regards, Mark.