Scripting Log Shipping Automation

Question

Is it possible to script All of the Log Shipping configuration?

For example: I need to write a job that initiates Log Shipping from two databases from server A to server B.

The job will run on Server A.

By the way both are SQL 2008 R2 Enterprise.

There's a share on server A. \serverA\Log

I Believe this is all that is needed. Is it possible to do it just by script? Do I have to initialize the databases or is there a option like SSMS to auto initialize?

Will I have to create the jobs manually? or is it configured in a way that creates the jobs and manages them?

Is it scriptable? Has anyone done anything like this? Does any one have a script to do this?

Thanks in advance.

Agadir

You can also use command from dbatools.io/commands to accomplish this – Kiyohara

Answer 1

This is totally possible, and it can be scripted out through the log shipping setup GUI. The only obstacle to overcome in the script that it generates will need to connect to both the primary and the secondary in the same script. This can be accomplished by used the SQLCMD mode commands of SQLCMD using the CONNECT command, making sure to put GOs between the connections. As a bonus you can also execute OS commands (copy, del, etc.) so you can script out the backup and restore as well. See example below.

-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [primary\instance1].[mydb],
-- The script needs to be run at the Primary in the context of the [msdb] database.  
------------------------------------------------------------------------------------- 
-- Adding the Log Shipping configuration 

-- ****** Begin: Script to be run at Primary: [primary\instance1] ******

:setvar PRIMARY primary\instance1
:setvar SECONDARY secondary\instance2
:setvar DATABASE mydb
:setvar PBKDIR "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar PBKSHARE "\\primary\d$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar SBKSHARE "\\secondary\d$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar SDATADIR "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"


:connect $(PRIMARY)

alter database [$(DATABASE)] set recovery full

DECLARE @LS_BackupJobId AS uniqueidentifier 
DECLARE @LS_PrimaryId   AS uniqueidentifier 
DECLARE @SP_Add_RetCode As int 


EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
        @database = N'$(DATABASE)' 
        ,@backup_directory = N'$(PBKDIR)' 
        ,@backup_share = N'$(PBKSHARE)' 
        ,@backup_job_name = N'LSBackup_$(DATABASE)' 
        ,@backup_retention_period = 1440
        ,@backup_threshold = 180 
        ,@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_$(PRIMARY)' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 60 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20100101 
        ,@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'$(DATABASE)' 
        ,@secondary_server = N'$(SECONDARY)' 
        ,@secondary_database = N'$(DATABASE)' 
        ,@overwrite = 1 

backup database [$(DATABASE)] to disk = '$(PBKDIR)$(DATABASE)_dr_init.bak' with init
go


print '$(PBKSHARE)$(DATABASE)_dr_init.bak'
print '$(SBKSHARE)'
print 'copy $(PBKSHARE)$(DATABASE)_dr_init.bak $(SBKSHARE) /y'

!!copy $(PBKSHARE)$(DATABASE)_dr_init.bak $(SBKSHARE) /y
go

!!del $(PBKSHARE)$(DATABASE)_dr_init.bak /Q
go

-- ****** End: Script to be run at Primary: [primary\instance1]  ******


-- Execute the following statements at the Secondary to configure Log Shipping 
-- for the database [secondary\instance2].[mydb],
-- the script needs to be run at the Secondary in the context of the [msdb] database. 
------------------------------------------------------------------------------------- 
-- Adding the Log Shipping configuration 

-- ****** Begin: Script to be run at Secondary: [secondary\instance2] ******

:connect $(SECONDARY)

!!sqlcmd /E /S$(SECONDARY) -i DRRestoreDatabase.sql -v BKDIR="$(SBKSHARE)" -v DATADIR="$(SDATADIR)" -v LOGDIR="$(SLOGDIR)"

!!del $(SBKSHARE)$(DATABASE)_dr_init.bak /Q
go


DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId  AS uniqueidentifier 
DECLARE @LS_Add_RetCode As int 


EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
        @primary_server = N'$(PRIMARY)' 
        ,@primary_database = N'$(DATABASE)' 
        ,@backup_source_directory = N'$(PBKSHARE)' 
        ,@backup_destination_directory = N'$(SBKSHARE)' 
        ,@copy_job_name = N'LSCopy_$(PRIMARY)_$(DATABASE)' 
        ,@restore_job_name = N'LSRestore_$(PRIMARY)_$(DATABASE)' 
        ,@file_retention_period = 1440 
        ,@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 


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 = 60 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@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 = 60 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@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'$(DATABASE)' 
        ,@primary_server = N'$(PRIMARY)' 
        ,@primary_database = N'$(DATABASE)' 
        ,@restore_delay = 0 
        ,@restore_mode = 1 
        ,@disconnect_users = 1 
        ,@restore_threshold = 180   
        ,@threshold_alert_enabled = 1 
        ,@history_retention_period = 2880
        ,@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 
go


-- ****** End: Script to be run at Secondary: [secondary\instance2] ******

Monorail

to use this I should create a job and set it CMDEXEC? Will this work? By the way is there any way to loop this for a select like a cursor? – Permissible

Save the script as DRLogShippingSetup.sql and then you could set it up in a CMDEXEC job that runs SQLCMD like below (will need full path to script), but the way the script is constructed it will need to be run from the secondary instance. The way I call this is from a batch script, with one call like the one below for each database. You could put a wrapper batch (or powershell) script around the call to do a for each database. sqlcmd /E /S secondary\instance1 -i DRLogShippingSetup.sql – Monorail

Should you delete the backup file? Is that ruining your backup chain on the primary? – Janel