Technical Article

Script to create a standby database server.

,

This script will do a full backup and transaction log backup of all the user database on the primary server and restore it over to the secondary server. The full backup/restore runs once every day and the transaction log backup/restore runs every minutes. Just provide the name of the primary and the secondary server and run the scripts on the secondary server. It will create two stored procedures and five jobs on the secondary server. Usp_restoreforstandby stored procedure can also be used for simply creating a backup copy of the primmary server databases.

/* ---------------------------------------------------------------------------------------------------- */
/* Instructions		:	Read the instructions below before executing this scripts. 		*/
/* ---------------------------------------------------------------------------------------------------- */
/* Script Name		:	StandBySecondaryServer.SQL						*/
/* Owner		:	Bodhayan K. Sharma							*/
/* Created On		:	July 10, 2002								*/
/* ---------------------------------------------------------------------------------------------------- */
/* Purpose		:	To Setup the Secondary Server as the StandyBY database server for all	*/
/*				user databases on the primary server. This script will install all the 	*/
/*				SQL Server jobs required to perform the standby function.		*/
/* Pre-requisites	:	Both the servers i.e. primary and the secondary server should be running*/
/*				under a domain user account.						*/
/* Search & Replace 	:	Search and replace the following variables with the right values:-	*/
/*				1. PrimaryServerName							*/
/*				2. SecondaryServerName							*/
/* Execution		:	Press Ctrl+E to execute this scripts.					*/
/* Error Reporting	:	Contact the owner for any assistance or reporting an error.		*/
/* FailoverToSecondary	:	Using Enterprise Manager run the FailoverToSecondary job if the primary */
/*				server is no longer available. Make changes to your application to point*/
/*				to the secondary server after running this job.				*/
/* FailoverToPrimary	:	Using Enterprise Manager run the FailoverToPrimary job if the primary   */
/*				is back online. Make changes to your application to point to the primary*/
/*				server after running this job.						*/
/* ---------------------------------------------------------------------------------------------------- */
/* Modified By		:										*/
/* Modified On		:										*/
/* Modification Details :										*/
/* ---------------------------------------------------------------------------------------------------- */

/* Create the Usp_RestoreForStandBy Stored Procedure */
/* ------------------------------------------------- */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_RestoreForStandBy]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_RestoreForStandBy]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create procedure 
		Usp_RestoreForStandBy
		(
			@lsPrimarySQLServerName 	varchar(50),
			@lsSecondarySQLServerName	varchar(50),
			@lsBackupType			varchar(3) = 'DB',
			@lsStandBy 			varchar(5) = 'FALSE'
		)
as
begin

	/* declare local variables */
	/* ----------------------- */
	declare @object 		int
	declare @objBackup		int
	declare @objRestore		int
	declare @src 			varchar(255)
	declare @desc 			varchar(255)
	declare @hr			int
	declare @lsDBName		sysname
	declare @liDBCount		int
	declare @liDBincr		int
	declare @lsCommand		varchar(4000)
	declare @tblDBList	 	table (DBName 	sysname)
	declare @liRetry		int
	declare @lsBackupDirectory	varchar(255)
	declare @lsBackupFileName	sysname
	declare @liBackupAction		int
	declare @liRestoreAction	int
	declare @lsMachineName		sysname
--	declare @lsSecondarySQLServerName	varchar(50)

	/* declare the cursor to get the list of all the user database on the primary server */
	/* --------------------------------------------------------------------------------- */
	declare 
		lcurDBList
	cursor for
		select * from @tblDBList


	/* initialize variables */
	/* -------------------- */

--	select @lsSecondarySQLServerName = @@SERVERNAME 

	if Upper(@lsBackupType) = 'DB' 		-- initialize the backyp type i.e. database or log
	begin
		set @liBackupAction = 0
		set @liRestoreAction = 0
	end
	else
	begin
		set @liBackupAction = 3
		set @liRestoreAction = 2
	end


	/* create an instance of primary server */
	/* ------------------------------------ */
	EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
	IF @hr <> 0 goto DestroyObj

	/* create an instance of backup object */
	/* ----------------------------------- */
	EXEC @hr = sp_OACreate 'SQLDMO.Backup', @objBackup OUT
	IF @hr <> 0 goto DestroyObj

	/* connect to the primary server */
	/* ----------------------------- */
	EXEC @hr = sp_OASetProperty @object, 'LoginSecure',True
	IF @hr <> 0 goto DestroyObj

	set @liRetry = 1
	set @hr = 1
	while (@liRetry <= 5 and @hr <> 0)
	begin
		EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @lsPrimarySQLServerName
		if @hr <> 0 waitfor delay '000:00:15'
		set @liRetry = @liRetry + 1
	end
	IF @hr <> 0 goto DestroyObj

	/* get the count of user database on the primary server */
	/* ---------------------------------------------------- */
	EXEC @hr = sp_OAGetProperty @Object, 'databases.count', @liDBCount OUT
	IF @hr <> 0 goto DestroyObj

	/* get the database name of each user database on the primary server */
	/* ----------------------------------------------------------------- */
	set @liDBincr = 1
	while (@liDBincr <= @liDBCount)
	begin
		select @lsCommand = 'databases.item(' + convert(varchar(2),@liDBincr) + ').name'
		EXEC @hr = sp_OAGetProperty @Object,@lsCommand, @lsDBName OUT		
		IF @hr <> 0 goto DestroyObj

		/* work only on user databases */
		/* --------------------------- */
		if lower(@lsDBName) <> 'master' and lower(@lsDBName) <> 'model' and lower(@lsDBName) <> 'msdb' and lower(@lsDBName) <> 'tempdb' and lower(@lsDBName) <> 'pubs' and lower(@lsDBName) <> 'northwind'
		begin
			insert @tblDBList values (@lsDBName)			
		end
		set @liDBincr = @liDBincr + 1
	end


	/* get the backup directory of the primary server */
	/* ----------------------------------------------- */
	select @lsCommand = 'registry.BackupDirectory'
	EXEC @hr = sp_OAGetProperty @Object,@lsCommand, @lsBackupDirectory OUT		
	IF @hr <> 0 goto DestroyObj

	/* get the machine name of the secondary server */
	/* -------------------------------------------- */
	EXEC @hr = sp_OAGetProperty @Object,'NetName', @lsMachineName OUT		
	if @hr <> 0 goto DestroyObj


	/* for each user database on the primary server perform the full or log backup */
	/* --------------------------------------------------------------------------- */
	open lcurDBlist
	fetch next from
		lcurDBlist
	into
		@lsDBName


	while (@@fetch_status = 0)
	begin
	
		/* set the database name property of the backup object */
		/* --------------------------------------------------- */
		EXEC @hr = sp_OASetProperty @objBackup, 'Database',@lsDBName
		if @hr <> 0 goto DestroyObj
		
		/* set the database action property of the backup object */
		/* ----------------------------------------------------- */
		EXEC @hr = sp_OASetProperty @objBackup, 'Action',@liBackupAction
		if @hr <> 0 goto DestroyObj
		
		/* set the database initialize property of the backup object */
		/* --------------------------------------------------------- */
		EXEC @hr = sp_OASetProperty @objBackup, 'Initialize',True
		if @hr <> 0 goto DestroyObj
		
		/* set the files property of the backup object */
		/* ------------------------------------------- */
		if @liBackupAction = 0
		begin
			set @lsBackupFileName = @lsBackupDirectory + '\' + @lsDBName + 'DailyFullBackup.bak'
		end
		else
		begin
			set @lsBackupFileName = @lsBackupDirectory + '\' + @lsDBName + 'DailyTlogBackup.trn'
		end
		EXEC @hr = sp_OASetProperty @objBackup, 'Files',@lsBackupFileName
		if @hr <> 0 goto DestroyObj

		/* call the sqlbackup method to start the backup */
		/* --------------------------------------------- */
		EXEC @hr = sp_OAMethod @objBackup,'SQLBackup',null,@object
		IF @hr <> 0 goto DestroyObj

		fetch next from
			lcurDBlist
		into
			@lsDBName
	end

	close lcurDBlist
		

	/* disconnect and destory the instance of primary server and backup object */
	/* ----------------------------------------------------------------------- */
	EXEC @hr = sp_OAMethod @object, 'DisConnect'
	EXEC @hr = sp_OADestroy @object
	Exec @hr = sp_OADestroy @objbackup

	/* create an instance of secondary server */
	/* -------------------------------------- */
	EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
	IF @hr <> 0 goto DestroyObj

	/* create a instance of restore object */
	/* ----------------------------------- */
	EXEC @hr = sp_OACreate 'SQLDMO.Restore', @objRestore OUT
	IF @hr <> 0 goto DestroyObj

	/* connect to the secondary server */
	/* ------------------------------- */
	EXEC @hr = sp_OASetProperty @object, 'LoginSecure',True
	IF @hr <> 0 goto DestroyObj

	set @liRetry = 1
	set @hr = 1
	while (@liRetry <= 5 and @hr <> 0)
	begin
		EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @lsSecondarySQLServerName
		if @hr <> 0 waitfor delay '000:00:15'
		set @liRetry = @liRetry + 1
	end
	IF @hr <> 0 goto DestroyObj


	/* for each user database on the seconary server perform the full or log restore */
	/* ----------------------------------------------------------------------------- */
	open lcurDBlist
	fetch next from
		lcurDBlist
	into
		@lsDBName

	while (@@fetch_status = 0)
	begin
	
		/* set the database name property of the restore object */
		/* ---------------------------------------------------- */
		EXEC @hr = sp_OASetProperty @objRestore, 'Database',@lsDBName
		if @hr <> 0 goto DestroyObj
		
		/* set the database action property of the restore object */
		/* ------------------------------------------------------ */
		EXEC @hr = sp_OASetProperty @objRestore, 'Action',@liRestoreAction
		if @hr <> 0 goto DestroyObj
		

		/* replace the colon with a dollar sign */
		/* ------------------------------------ */
		set @lsBackupDirectory = replace(@lsBackupDirectory,':','$')
		set @lsBackupFileName = '\\'+@lsMachineName+'\'+@lsBackupDirectory + '\' + @lsDBName

		/* set the files property of the restore object */
		/* -------------------------------------------- */

		if @liRestoreAction = 0
		begin
			set @lsBackupFileName = @lsBackupFileName + 'DailyFullBackup.bak'
		end
		else
		begin
			set @lsBackupFileName = @lsBackupFileName + 'DailyTlogBackup.trn'
		end


		EXEC @hr = sp_OASetProperty @objRestore, 'Files',@lsBackupFileName
		if @hr <> 0 goto DestroyObj
		
		/* set the last restore property based on action property */
		/* ------------------------------------------------------ */
		if Upper(@lsStandBy) = 'FALSE'
		begin
			EXEC @hr = sp_OASetProperty @objRestore, 'LastRestore',True
			IF @hr <> 0 goto DestroyObj
		end
		else
		begin
			EXEC @hr = sp_OASetProperty @objRestore, 'LastRestore',False
			IF @hr <> 0 goto DestroyObj
		end

		/* call the sqlbackup method to start the backup */
		/* --------------------------------------------- */
		EXEC @hr = sp_OAMethod @objRestore,'SQLRestore',null,@object
		IF @hr <> 0 goto DestroyObj

		fetch next from
			lcurDBlist
		into
			@lsDBName
	end

	close lcurDBlist
	deallocate lcurDBlist
		
	/* disconnect and destroy the instance of secondary server and restore object */
	/* -------------------------------------------------------------------------- */
	EXEC @hr = sp_OAMethod @object, 'DisConnect'
	EXEC @hr = sp_OADestroy @object
	Exec @hr = sp_OADestroy @objRestore

	/* Destroy the sql server object */
	/* ----------------------------- */
	DestroyObj:
	IF @hr <> 0
	BEGIN
		EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
		exec master..xp_logEvent 60000,@src,Error
		exec master..xp_logEvent 60000,@desc,Error

		EXEC sp_OAGetErrorInfo @objBackup, @src OUT, @desc OUT 
		exec master..xp_logEvent 60000,@src,Error
		exec master..xp_logEvent 60000,@desc,Error

		EXEC sp_OAGetErrorInfo @objRestore, @src OUT, @desc OUT 
		exec master..xp_logEvent 60000,@src,Error
		exec master..xp_logEvent 60000,@desc,Error

		exec master..xp_logevent 60000,@lsPrimarySQLServerName,Error
		exec master..xp_logevent 60000,@lsDBName,Error
		exec master..xp_logevent 60000,@lsBackupFileName,Error
		exec master..xp_logevent 60000,'Error while executing Usp_RestoreForStandBy procedure...',Error

		close lcurDBlist
		deallocate lcurDBlist
	END
	EXEC @hr = sp_OAMethod @object, 'DisConnect'
	EXEC @hr = sp_OADestroy @object
	Exec @hr = sp_OADestroy @objbackup
	Exec @hr = sp_OADestroy @objRestore

end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/* Create the RecoverStandByDatabase stored procedure */
/* -------------------------------------------------- */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_RecoverStandByDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_RecoverStandByDatabase]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create procedure 
		Usp_RecoverStandByDatabase
as
begin

	/* declare local variables */
	/* ----------------------- */
	declare @object 		int
	declare @src 			varchar(255)
	declare @desc 			varchar(255)
	declare @hr			int
	declare @lsDBName		sysname
	declare @lsCommand		varchar(4000)
	declare @liRetry		int
	declare @liRestoreAction	int
	declare @lsSecondarySQLServerName	varchar(50)
	
	/* declare a cursor to get the list of all the user database on the secondary server */
	/* --------------------------------------------------------------------------------- */
	declare 
		lcurDBList
	cursor for
		select 
			name 
		from 
			master..sysdatabases 
		where 
			lower(name) not in ('master','model','msdb','tempdb','pubs','northwind')


	/* set the backup type i.e. full or log */
	/* ------------------------------------ */
	select @lsSecondarySQLServerName = @@SERVERNAME

	/* create an instance of secondary server */
	/* -------------------------------------- */
	EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
	IF @hr <> 0 goto DestroyObj

	/* connect to the secondary server */
	/* ------------------------------- */
	EXEC @hr = sp_OASetProperty @object, 'LoginSecure',True
	IF @hr <> 0 goto DestroyObj

	set @liRetry = 1
	set @hr = 1
	while (@liRetry <= 5 and @hr <> 0)
	begin
		EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @lsSecondarySQLServerName
		if @hr <> 0 waitfor delay '000:00:15'
		set @liRetry = @liRetry + 1
	end
	IF @hr <> 0 goto DestroyObj

	/* for each user database on the primary server perform the full or log backup */
	/* --------------------------------------------------------------------------- */
	open lcurDBlist
	fetch next from
		lcurDBlist
	into
		@lsDBName

	while (@@fetch_status = 0)
	begin
	
		select @lsCommand = 'ExecuteImmediate("Restore Database '+@lsDBName + ' With Recovery")'
		EXEC @hr = sp_OAMethod @object,@lsCommand
		IF @hr <> 0 goto DestroyObj

		fetch next from
			lcurDBlist
		into
			@lsDBName
	end

	/* disconnect and destroy the instance of secondary server and restore object */
	/* -------------------------------------------------------------------------- */
	EXEC @hr = sp_OAMethod @object, 'DisConnect'
	EXEC @hr = sp_OADestroy @object

	close lcurDBlist
	deallocate lcurDBlist

	/* Destroy the sql server object */
	/* ----------------------------- */
	DestroyObj:
	IF @hr <> 0
	BEGIN
		EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
		exec master..xp_logEvent 60000,@src,Error
		exec master..xp_logEvent 60000,@desc,Error

		exec master..xp_logevent 60000,@lsSecondarySQLServerName,Error
		exec master..xp_logevent 60000,@lsDBname,Error
		exec master..xp_logevent 60000,'Error while executing Usp_RecoverStandByDatabase procedure...',Error

		close lcurDBlist
		deallocate lcurDBlist
	END
	EXEC @hr = sp_OAMethod @object, 'DisConnect'
	EXEC @hr = sp_OADestroy @object

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/* Create the RestoreDBForStandBy job */
/* ---------------------------------- */
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'RestoreDBForStandBy')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''RestoreDBForStandBy'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'RestoreDBForStandBy' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'RestoreDBForStandBy', @owner_login_name = N'sa', @description = N'This job takes a full backup of all the user databases on the primary server and restores it on to the secondary server once every day.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'RestoreDBForStandByStep', @command = N'exec master..Usp_RestoreForStandBy 
	''PrimaryServerName'',
	''SecondaryServerName'',
	''DB'',
	''TRUE''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'RestoreDBForStandBySchedule', @enabled = 1, @freq_type = 4, @active_start_date = 20020627, @active_start_time = 213000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 10, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

GO

/* create the RestoreLogForStandByJob */
/* ---------------------------------- */
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'RestoreLOGForStandBy')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''RestoreLOGForStandBy'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'RestoreLOGForStandBy' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'RestoreLOGForStandBy', @owner_login_name = N'sa', @description = N'This jobs takes the backup of the transaction logs of all the user databases on the primary server and restores it on to the secondary server every 15 minutes.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'RestoreLOGForStandByStep', @command = N'exec master..Usp_RestoreForStandBy 
	''PrimaryServerName'',
	''SecondaryServerName'',
	''LOG'',
	''TRUE''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'RestoreLOGForStandBySchedule', @enabled = 1, @freq_type = 4, @active_start_date = 20020627, @active_start_time = 220000, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 203000
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

GO

/* create the FailoverToSecondary job */
/* ---------------------------------- */
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'FailoverToSecondary')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''FailoverToSecondary'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'FailoverToSecondary' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'FailoverToSecondary', @owner_login_name = N'sa', @description = N'This jobs recovers and changes the status of all the user database on the secondary server from StandBy to OnLine. Run this job to fail over the database to the secondary server. It also disables the restore jobs.', @category_name = N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'FailoverToSecondaryStep', @command = N'exec master..Usp_RecoverStandByDatabase', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'DisableTheRestoreDBJobStep', @command = N'EXEC msdb..sp_Update_job 
	@job_name = ''RestoreDBForStandBy'',
	@enabled=0
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'DisableTheRestoreLogJobStep', @command = N'EXEC msdb..sp_Update_job 
	@job_name = ''RestoreLOGForStandBy'',
	@enabled=0
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

GO

/* create the FailoverToPrimary job */ 
/* -------------------------------- */
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'FailoverToPrimary')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''FailoverToPrimary'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'FailoverToPrimary' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'FailoverToPrimary', @owner_login_name = N'sa', @description = N'This job transfers the data from the secondary server to the primary server and setups the secondary server again as the standby server by enabiling the restore jobs.', @category_name = N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'FailoverToPrimaryStep', @command = N'exec master..Usp_RestoreForStandby
		''SecondaryServerName'',
		''PrimaryServerName'',
		''DB'',
		''FALSE''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'StartRestoreDBForStandByJobStep', @command = N'EXEC msdb..sp_start_job @job_name = ''RestoreDBForStandBy''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'EnableTheRestoreDBJobsStep', @command = N'EXEC msdb..sp_Update_job 
	@job_name = ''RestoreDBForStandBy'',
	@enabled=1

', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'EnableTheRestoreLogJobStep', @command = N'EXEC msdb..sp_Update_job 
	@job_name = ''RestoreLOGForStandBy'',
	@enabled=1', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

GO

/* creates a job to recover the database before the daily full database backup maintenance plan */
/* -------------------------------------------------------------------------------------------- */
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'RecoverForDailyBackup')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''RecoverForDailyBackup'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'RecoverForDailyBackup' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'RecoverForDailyBackup', @owner_login_name = N'sa', @description = N'This job recovers the standby database every day before the full backup runs. This is required so that the standby databases are backedup locally on the disk by the daily backup maintenance plan.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'RecoverForDailyBackupStep', @command = N'exec master..Usp_RecoverStandByDatabase', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'RecoverForDailyBackupSchedule', @enabled = 1, @freq_type = 4, @active_start_date = 20020711, @active_start_time = 205000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

GO

/* Start the RestoreDBForStandBy job to start the restore */
/* ------------------------------------------------------ */
--EXEC msdb..sp_start_job @job_name = 'RestoreDBForStandBy'

GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

External Article

Selecting the database recovery model to ensure proper backups

One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.

2008-05-30

4,151 reads