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),
@lsSecondarySQLServerNamevarchar(50),
@lsBackupTypevarchar(3) = 'DB',
@lsStandBy varchar(5) = 'FALSE'
)
as
begin

/* declare local variables *//* ----------------------- */declare @object int
declare @objBackupint
declare @objRestoreint
declare @src varchar(255)
declare @desc varchar(255)
declare @hrint
declare @lsDBNamesysname
declare @liDBCountint
declare @liDBincrint
declare @lsCommandvarchar(4000)
declare @tblDBList table (DBName sysname)
declare @liRetryint
declare @lsBackupDirectoryvarchar(255)
declare @lsBackupFileNamesysname
declare @liBackupActionint
declare @liRestoreActionint
declare @lsMachineNamesysname
--declare @lsSecondarySQLServerNamevarchar(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 @hrint
declare @lsDBNamesysname
declare @lsCommandvarchar(4000)
declare @liRetryint
declare @liRestoreActionint
declare @lsSecondarySQLServerNamevarchar(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

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