SQL Server 2014 Log Shipping Deployment (5): Deploy log shipping with T-SQL command

13.2.5 Deploying log shipping with T-SQL commands

SQL Server also provides the means to deploy log shipping as a T-SQL command. In the previous section, after you complete step (6) or (8), you can save the generated command script. The results of this script command and the results of the operation in Management Studio are equivalent, and this command script can also be used to redeploy log shipping more quickly in the disaster recovery scenario for the user.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m02/57/fc/wkiom1slpqys6umdaahai7avalk980.jpg "height=" 510 "/>

The resulting command script (with Monitor server):

--Execute the following statement on the primary server for the database [SQLSVR1]. [DB01]

--Configure log shipping.

--You need to run the script in the context of the [msdb] database on the primary server.

-------------------------------------------------------------------------------------

--  Add a log shipping configuration-- ******  start:  scripts to run on master server  [SQLSVR1]   ******DECLARE  @LS_ backupjobid as uniqueidentifierdeclare  @LS_PrimaryId  AS uniqueidentifierDECLARE  @SP_Add_RetCode  As intEXEC  @SP_Add_RetCode  = master.dbo.sp_add_log_shipping_primary_ Database@database = n ' DB01 ', @backup_directory  = n ' \\192.168.1.20\backuplog ', @backup_share  = n ' \\192.168.1.20\backuplog ', @backup_job_name  = n ' lsbackup_db01 ', @backup_retention_ period = 4320, @backup_compression  = 2, @monitor_server  = n ' SQLSVR3 ', @monitor_ Server_security_mode = 1, @backup_threshold  = 60, @threshold_alert_enabled  = 1,@ history_retention_period = 5760, @backup_job_id  =  @LS_BackupJobId  output, @primary_id  =  @LS_PrimaryId  output, @overwrite  = 1IF  (@ @ERROR  = 0 and @ SP_ADD_RETCODE = 0)begindeclare  @LS_BackUpScheduleUID  As uniqueidentifierDECLARE  @LS_BackUpScheduleID  as  intexec msdb.dbo.sp_add_schedule@schedule_name =n ' Lsbackupschedule_sqlsvr11 ', @enabled  =  1, @freq_type  = 4, @freq_interval  = 1, @freq_subday_type  = 4, @freq_subday_ Interval = 15, @freq_recurrence_factor  = 0, @active_start_date  = 20140708, @active_ end_date = 99991231, @active_start_time  = 0, @active_end_time  = 235900, @schedule_ uid =  @LS_BackUpScheduleUID  output, @schedule_id  =  @LS_BackUpScheduleID   outputexec msdb.dbo.sp_attach_schedule@job_id =  @LS_BackupJobId, @schedule_id  =  @LS_ backupscheduleidexec msdb.dbo.sp_update_job@job_id =  @LS_BackupJobId, @enabled  =  1endexec master.dbo.sp_add_log_shipping_primary_secondary@primary_database = n ' DB01 ', @secondary _server = n ' SQLSVR2 ', @Secondary_database = n ' DB01 ', @overwrite  = 1-- ******  end:  to be on the primary server  [ Scripts running on the sqlsvr1]   ******

--Execute the following statement on the secondary server for the database [SQLSVR2]. [DB01]

--Configure log shipping.

--You need to run the script in the context of the [msdb] database on the secondary server.

-------------------------------------------------------------------------------------

--  Add log shipping configuration-- ******  start:  scripts to run on secondary server  [SQLSVR2]   ******DECLARE  @LS_ secondary__copyjobid as uniqueidentifierdeclare  @LS_Secondary__RestoreJobId  AS  uniqueidentifierdeclare  @LS_Secondary__SecondaryId  AS uniqueidentifierDECLARE  @LS_Add_ retcode as intexec  @LS_Add_RetCode  = master.dbo.sp_add_log_shipping_secondary_ Primary@primary_server = n ' SQLSVR1 ', @primary_database  = n ' DB01 ', @backup_source_directory  = n ' \\192.168.1.20\backuplog ', @backup_destination_directory  = n ' D:\copylog ', @copy_job_ Name = n ' Lscopy_sqlsvr1_db01 ', @restore_job_name  = n ' lsrestore_sqlsvr1_db01 ', @file_ retention_period = 4320, @monitor_server  = n ' SQLSVR3 ', @monitor_server_security_mode  =  1, @overwrite  = 1, @copy_job_id  =  @LS_Secondary__CopyJobId  output, @restore_job_id  =  @LS_Secondary__RestoreJobId &NBSp;output, @secondary_id  =  @LS_Secondary__SecondaryId  OUTPUTIF  (@ @ERROR  = 0  and  @LS_Add_RetCode  = 0) begindeclare  @LS_SecondaryCopyJobScheduleUID  As  uniqueidentifierdeclare  @LS_SecondaryCopyJobScheduleID  as intexec 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  = 20140708, @active_end_date  = 99991231, @active_ Start_time = 0, @active_end_time  = 235900, @schedule_uid  =  @LS_ Secondarycopyjobscheduleuid output, @schedule_id  =  @LS_SecondaryCopyJobScheduleID   outputexec msdb.dbo.sp_attach_schedule@job_id =  @LS_Secondary__CopyJobId, @schedule_id  =   @LS_SecondaryCopyJobScheduleIDDECLARE   @LS_Secondaryrestorejobscheduleuid as uniqueidentifierdeclare  @LS_SecondaryRestoreJobScheduleID  as  intexec 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  = 20140708, @active_ end_date = 99991231, @active_start_time  = 0, @active_end_time  = 235900, @schedule_ uid =  @LS_SecondaryRestoreJobScheduleUID  output, @schedule_id  =  @LS_ secondaryrestorejobscheduleid outputexec msdb.dbo.sp_attach_schedule@job_id =  @LS_ Secondary__restorejobid, @schedule_id  =  @LS_SecondaryRestoreJobScheduleIDENDDECLARE   @LS_Add_ retcode2 as intif  (@ @ERROR  = 0 AND  @LS_Add_RetCode  = 0) beginexec   @LS_Add_RetCode2  = master.dbo.sp_add_log_shipping_secondary_database@secondary_database = n ' DB01 ', @primary_server  = n ' SQLSVR1 ' , @primary_database  = n ' DB01 ', @restore_delay  = 0, @restore_mode  = 0, @disconnect_ Users = 0, @restore_threshold  = 45, @threshold_alert_enabled  = 1, @history_ retention_period = 5760, @overwrite  = 1, @ignoreremotemonitor  = 1ENDIF  (@@ error = 0 and  @LS_Add_RetCode  = 0) beginexec msdb.dbo.sp_update_job@job_id  =  @LS_Secondary__CopyJobId, @enabled  = 1exec msdb.dbo.sp_update_job@job_id =   @LS_Secondary__RestoreJobId, @enabled  = 1END-- ******  end:  to the secondary server  [SQLSVR2] Scripts running on    ******

--****** start: script to run on Monitor [SQLSVR3] ******

EXEC Msdb.dbo.sp_processlogshippingmonitorsecondary@mode = 1, @secondary_server = N ' SQLSVR2 ', @secondary_database = N ' DB01 ', @secondary_id = N ', @primary_server = N ' SQLSVR1 ', @primary_database = N ' DB01 ', @restore_threshold = $, @threshold_ Alert = 14420, @threshold_alert_enabled = 1, @history_retention_period = 5760, @monitor_server = N ' SQLSVR3 ', @monitor_ Server_security_mode = ****** end: script to run on Monitor [SQLSVR3] ******

The predefined stored procedures contained in this command script are used to set the various parts of log shipping;

master.dbo.sp_add_log_shipping_primary_database Sets the log shipping configuration for the primary database and sets the log shipping backup job.

Msdb.dbo.sp_add_schedule Set a schedule for log shipping, or set a schedule for the copy job, or set a schedule for the restore job.

msdb.dbo.sp_attach_schedule Link a log shipping job to a timesheet, or link a copy job to a timesheet, or link a restore job to a timesheet.

msdb.dbo.sp_update_job Backup job, copy job, or restore job with transaction log enabled

master.dbo.sp_add_log_shipping_primary_secondary Sets relevant information on the primary database for the specified secondary database.

master.dbo.sp_add_log_shipping_secondary_primary Sets relevant information on the secondary database for the specified primary database, adds local and remote monitoring links, and creates replication and restore jobs.

Master.dbo.sp_add_log_shipping_secondary_database Set up a secondary database for log shipping.

msdb.dbo.sp_processlogshippingmonitorsecondary Monitoring alerts on the primary and secondary databases are enabled on the monitoring server.

This article is from the "Margin with Wish" blog, please be sure to keep this source http://281816327.blog.51cto.com/907015/1598315

SQL Server 2014 Log Shipping Deployment (5): Deploy log shipping with T-SQL command