Technical Article

Script to create a standby database server(new).

,

New feature to relocate files on the secondary server has been added to this script. If you don't need this feature please use my other script that is posted on this website.
Run this script on a secondary SQL Server to replicate the data of all the user databases from the primary server every 15 minutes. Full sync is done at 9pm every night. Please try this script in the development environment first to make sure that you are completely satisfied and it suits your requirement.

/* ---------------------------------------------------------------------------------------------------- *//* 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*//*3. SecondaryServerDataDrive e.g. with D if the data dir is on D Drive*//*4. PrimaryServerDataDrive e.g. with E if the data dir is on E Drive     *//* 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:Bodhayan K. Sharma*//* Modified On:August 09,2002*//* Modification Details :Removed Usp_RestoreForStandBy and Added Usp_RestoreForStandByWithMove*//*stored procedure. This new stored procedure allows to relocate files on *//*the standby server. Two new parameters have been added. One to know if  *//*if relocation has to be done and the other to know the drive where files*//*have to be relocated.*//* ---------------------------------------------------------------------------------------------------- */
/* Create the Usp_RestoreForStandByWithMove Stored Procedure *//* --------------------------------------------------------- */if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_RestoreForStandByWithMove]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_RestoreForStandByWithMove]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create procedure 
Usp_RestoreForStandByWithMove
(
@lsPrimarySQLServerName varchar(50),
@lsSecondarySQLServerNamevarchar(50),
@lsBackupTypevarchar(3) = 'DB',
@lsStandBy varchar(5) = 'FALSE',
@lsRelocateFilesYesNochar(1) = 'N',
@lsRelocateFilesToChar(1) = Null
)
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,
LogicalName Varchar(256), 
Physicalname Varchar(520))
declare @LogicalNamevarchar(256)
declare @PhysicalNamevarchar(520)
declare @liRetryint
declare @lsBackupDirectoryvarchar(255)
declare @lsBackupFileNamesysname
declare @liBackupActionint
declare @liRestoreActionint
declare @lsMachineNamesysname
--declare @lsSecondarySQLServerNamevarchar(50)
declare @liFGCount int
declare @liFGIncr int
declare @liDBFCount int
declare @liDBFIncr int
declare @liLFCount int
declare @liLFIncr int
declare @lsRelocateFilesvarchar(4000)

/* 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' and lower(@lsDBName) <> 'sqlhelpdesk'
begin
If Upper(@lsRelocateFilesYesNo) = 'N'
begin
insert @tblDBList values(@lsDBName,'','')
end
else
begin
select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').filegroups.count'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand,@liFGCount OUT
IF @hr <> 0 goto DestroyObj
select @liFGincr = 1
while @liFGincr <= @liFGCount
begin

select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').filegroups('+convert(varchar(2),@liFGincr)+').dbfiles.count'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand, @liDBFCount OUT
IF @hr <> 0 goto DestroyObj
select @liDBFincr = 1
while @liDBFincr <= @liDBFCount
begin
select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').filegroups('+convert(varchar(2),@liFGincr)+').dbfiles('+convert(varchar(2),@liDBFincr)+').Name'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand,@LogicalName OUT
IF @hr <> 0 goto DestroyObj
select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').filegroups('+convert(varchar(2),@liFGincr)+').dbfiles('+convert(varchar(2),@liDBFincr)+').PhysicalName'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand,@PhysicalName OUT
IF @hr <> 0 goto DestroyObj
select @liDBFincr = @liDBFincr + 1
insert @tblDBList values(@lsDBName,@LogicalName,@PhysicalName)
end
select @liFGincr = @liFGincr + 1
end
/* get the transaction log properties *//* ---------------------------------- */select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').TransactionLog.LogFiles.count'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand,@liLFCount OUT
IF @hr <> 0 goto DestroyObj
select @liLFincr = 1
while @liLFincr <= @liLFCount
begin
select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').TransactionLog.LogFiles('+convert(varchar(2),@liLFincr)+').Name'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand,@LogicalName OUT
IF @hr <> 0 goto DestroyObj
select @lsCommand = 'databases(' + convert(varchar(2),@liDBincr) + ').TransactionLog.LogFiles('+convert(varchar(2),@liLFincr)+').PhysicalName'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand,@PhysicalName OUT
IF @hr <> 0 goto DestroyObj
select @liLFincr = @liLFincr + 1
insert @tblDBList values(@lsDBName,@LogicalName,@PhysicalName)
end
end
end
set @liDBincr = @liDBincr + 1
end

/* Update the temp table with the new drive location *//* ------------------------------------------------- */If Upper(@lsRelocateFilesYesNo) = 'Y' and Upper(@lsRelocateFilesTo) is Not Null
begin
Update
@tblDBList
set
PhysicalName = @lsRelocateFilesTo + substring(PhysicalName,2,len(PhysicalName))
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,
@LogicalName,
@Physicalname


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,
@LogicalName,
@PhysicalName
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,
@LogicalName,
@PhysicalName

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 relocatefiles property of the restore object *//* ---------------------------------------------------- */if Upper(@lsRelocateFilesYesNo) = 'Y' and Upper(@lsRelocateFilesTo) is Not Null
begin
set @lsRelocateFiles = ""
Update
@tblDBList
set
@lsRelocateFiles = @lsRelocateFiles + rtrim(a.LogicalName) + ',' + rtrim(a.PhysicalName) + ','
from
@tblDBList a
where
a.DBName = @lsDBName

set @lsRelocateFiles = substring(@lsRelocateFiles,1,len(@lsRelocateFiles)-1)

EXEC @hr = sp_OASetProperty @objRestore, 'RelocateFiles',@lsRelocateFiles
if @hr <> 0 goto DestroyObj
end

/* 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,
@LogicalName,
@PhysicalName

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_RestoreForStandByWithMove 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
-- usp_RestoreForStandByWithMove 'PrimaryServerName','SecondaryServerName','DB','TRUE','Y','SecondaryServerDataDrive'
-- usp_RestoreForStandByWithMove 'PrimaryServerName','SecondaryServerName','LOG','TRUE','Y','SecondaryServerDataDrive'
-- master..xp_readerrorlog
-- sp_cycle_errorlog



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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','sqlhelpdesk')


/* get the secondary server name *//* ----------------------------- */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

/* Recover all the user databases on the secondary server *//* ------------------------------------------------------ */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 *//* ------------------------------------------------------- */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
-- usp_RecoverStandByDatabase
-- master..xp_readerrorlog
-- sp_cycle_errorlog

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_RestoreForStandByWithMove 
''PrimaryServerName'',
''SecondaryServerName'',
''DB'',
''TRUE'',
''Y'',
''SecondaryServerDataDrive''
', @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 = 210000, @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_RestoreForStandByWithMove 
''PrimaryServerName'',
''SecondaryServerName'',
''LOG'',
''TRUE'',
''Y'',
''SecondaryServerDataDrive''', @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_RestoreForStandbyWithMove
''SecondaryServerName'',
''PrimaryServerName'',
''DB'',
''FALSE'',
''Y'',
''PrimaryServerDataDrive''', @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

/* 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