Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Scripting Log Shipping Automation

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.

like image 275
Gabriel Guimarães Avatar asked Nov 08 '10 20:11

Gabriel Guimarães


People also ask

How do you build log shipping?

To configure log shipping. Right click the database you want to use as your primary database in the log shipping configuration, and then click Properties. Under Select a page, click Transaction Log Shipping. Select the Enable this as a primary database in a log shipping configuration check box.

What is SQL in shipping?

The Safe Working Load (SWL) of an item of ship's lifting gear is the safe maximum weight that can be lifted with the load suspended directly from the hook in the standard configuration or in other specified rigging variation(s).

What is the difference between mirroring and log shipping?

Log shipping supports an unlimited number of secondary's for each primary database. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.


1 Answers

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] ******
like image 80
Jason Cumberland Avatar answered Oct 18 '22 18:10

Jason Cumberland