As a follow up to last weeks post on quickly restoring multiple databases to default locations I’ve another script which might help you with quick deployments.
Last week I covered restoring the databases, I also needed to get log shipping setup for multiple databases. If you’ve ever had to configure log shipping you know that there’s lots of clicking and typing of things and entering information. It’s a bit of a pain for a single database, when you have to set it up for multiple databases it’s a long, slow, time consuming nightmare.
I’d had enough of dealing with this so I put together a script that will do the complete configuration of the primary and secondary servers with a single execution. The key to doing this is using SQLCMD mode from within SSMS.
Never used SQLCMD mode before? I’m not surprised, it’s not that well known. To enter SQLCMD mode you either choose the option from the Query menu item or add the button to one of your tool bars
This allows you to do some special things, like parameter substitution and, key to what I’m doing, change connections as a part of the script.
Try the following, replacing PrimaryServer and SecondaryServer with something relevant to your environment.
As you can see from the script it connected to each server in turn and grabbed the @@SERVERNAME value. That’s pretty special.
My log shipping script does very much the same thing. You just provide the primary and secondary servers, the file locations for the transaction log backups and the name of the database. The script takes care of the rest.
It will perform a very basic check to ensure that the local backup directory exists and error if not. The script also does not encompass a log shipping monitor server and has a default of 15 minutes for the backup/copy/restore processes. All of that could be quickly adjusted and made additional parameters.
View the script below or download SetupLogShipping.sql
Of course this doesn’t answer the question of when or why you would use log shipping, that’s for another post.
:ON ERROR exit
:setvar PrimaryServer "ServerA"
:setvar PrimaryDatabase "MyDB"
:setvar PrimaryBackupDirectory "E:\Backup\DB\TrxLogs\"
:setvar PrimaryBackupShare "\\ServerA\trxlogs$\"
:setvar SecondaryServer "ServerB"
:setvar SecondaryDatabase "MyDB"
:setvar SecondaryBackupDirectory "\\ServerB\Trxlogs$\TrxForRestore\"
SET NOCOUNT ON
/* Begin: Script to be run at Primary */
:CONNECT $(PrimaryServer)
BEGIN
USE msdb;
/* Check to see if the backup folder exists */
RAISERROR('Checking to see if $(PrimaryBackupDirectory) exists...',0,1) WITH NOWAIT
DECLARE @CheckDir NVARCHAR(2000)
DECLARE @ErrorMsg NVARCHAR(200)
DECLARE @DirInfo TABLE
(
FileExists BIT ,
FileIsADirectory BIT ,
ParentDirectoryExists BIT
)
SET @CheckDir = '$(PrimaryBackupDirectory)'
INSERT INTO @DirInfo
( FileExists ,
FileIsADirectory ,
ParentDirectoryExists
)
EXECUTE [master].dbo.xp_fileexist @CheckDir
IF NOT EXISTS ( SELECT *
FROM @DirInfo
WHERE FileExists = 0
AND FileIsADirectory = 1
AND ParentDirectoryExists = 1 )
BEGIN
SET @ErrorMsg = 'The primary backup directory $(PrimaryBackupDirectory) does not exist...quitting'
RAISERROR(@ErrorMsg,16,1) WITH NOWAIT
RETURN
END
RAISERROR('$(PrimaryBackupDirectory) exists, continuing...',0,1) WITH NOWAIT
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
RAISERROR('Enabling log shipping for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'$(PrimaryDatabase)'
,@backup_directory = N'$(PrimaryBackupDirectory)'
,@backup_share = N'$(PrimaryBackupShare)'
,@backup_job_name = N'LSBackup_$(PrimaryDatabase)'
,@backup_retention_period = 4320
,@backup_compression = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_$(PrimaryServer)'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20120607
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_alert_job
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'$(PrimaryDatabase)'
,@secondary_server = N'$(PrimaryServer)'
,@secondary_database = N'$(SecondaryDatabase)'
,@overwrite = 1
RAISERROR('Log shipping enabled for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT
END
GO
/* ENDS THE PRIMARY SCRIPT */
/* Script to be run on secondary */
:CONNECT $(SecondaryServer)
USE msdb;
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As int
RAISERROR('Adding log shipping secondary to $(SecondaryServer) with database name $(SecondaryDatabase)...',0,1) WITH NOWAIT
EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'$(PrimaryServer)'
,@primary_database = N'$(PrimaryDatabase)'
,@backup_source_directory = N'$(PrimaryBackupShare)'
,@backup_destination_directory = N'$(SecondaryBackupDirectory)'
,@copy_job_name = N'LSCopy_$(PrimaryServer)_$(PrimaryDatabase)'
,@restore_job_name = N'LSRestore_$(PrimaryServer)_$(PrimaryDatabase)'
,@file_retention_period = 4320
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID AS int
RAISERROR('Creating log shipping secondary jobs...',0,1) WITH NOWAIT
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20120607
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20120607
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2 As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'$(SecondaryDatabase)'
,@primary_server = N'$(PrimaryServer)'
,@primary_database = N'$(PrimaryDatabase)'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@overwrite = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
RAISERROR('Setup of log shipping from database $(PrimaryDatabase) on server $(PrimaryServer) to database $(SecondaryDatabase) on $(SecondaryServer) complete!',0,1) WITH NOWAIT