Scripting an automated SQLServer 2008 DR move
- by ItsAMystery
Hi All
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
