Script to create a standby database server(V 3.0)

/* ---------------------------------------------------------------------------------------------------- *//* Instructions:Read the instructions below before executing this scripts. *//* ---------------------------------------------------------------------------------------------------- *//* Script Name:StandBySecondaryServer.SQL*//* Owner:Bodhayan K. Sharma*//* Created On:July 10, 2002*//* Version:3.0*//* ---------------------------------------------------------------------------------------------------- *//* 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.*//* ---------------------------------------------------------------------------------------------------- *//* Modified On:October 31,2002*//* Modification Details :Modified the script to leave the database read-only and able to restore *//*addition logs. This will give read only access to the standby database. *//* Warning:The database/log restores require an exclusive access to the database.  *//*Therefore the restores will terminate if any user connections is found. *//*If the restore terminates you will have to perform a full restore again.*//* This script requires modification if the collation is case sensitive.   *//* Bug Fixed:The script fails if there are space in your SQL program file location   *//*or data / backup file location.*//* Acknowledgements:Thanks to Adrian Kennedy from Zimbabwe for reporting the above issues.  *//* ---------------------------------------------------------------------------------------------------- */
/* 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 @lsSQLDataRootvarchar(255)
declare @lsUndoFileNamesysname

/* declare the cursor to get the list of all the user database on the primary server *//* --------------------------------------------------------------------------------- */declare 
lcurDBList
cursor for
select distinct DBName 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


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
set @lsBackupFileName = '['+@lsBackupFileName+']'

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


/* get the Data directory of the Secondary Server *//* ---------------------------------------------- */select @lsCommand = 'registry.SQLDataRoot'
EXEC @hr = sp_OAGetProperty @Object,@lsCommand, @lsSQLDataRoot OUT
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
set @lsBackupFileName = '['+@lsBackupFileName+']'

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

/* set the standby database property *//* --------------------------------- */set @lsUndoFileName = @lsSQLDataRoot + '\Log\' + @lsDBName + 'Undo.ldf'
EXEC @hr = sp_OASetProperty @objRestore, 'StandByFiles',@lsUndoFileName
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_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')


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