Speedy Log Shipping Setup

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

image

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.

image

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:BackupDBTrxLogs"

:setvar PrimaryBackupShare "ServerAtrxlogs$"

:setvar SecondaryServer "ServerB"

:setvar SecondaryDatabase "MyDB"

:setvar SecondaryBackupDirectory "ServerBTrxlogs$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

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s