How to Configure Database Log Shipping in SQL Server - SQL Server DBA Tutorial

In this video you will learn step by step process of how to configure Database Log Shipping using SQL Server Management studio as well as using T-SQL Script. It shows process of how to enable Log Shipping configuration, how to configure Backup settings on Primary, how to add Secondary Server for Log Shipping, how to configure Log Shipping Monitoring Server, what is Primary, Secondary and Monitoring servers in Log Shipping and how to configure copy/restore Operation in SQL Server Log Shipping.

Script to Create Log Shipping in SQL Server

-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [TBSSql\SQLPROD].[SalesOrders],
-- 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: [TBSSql\SQLPROD] ******


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'SalesOrders' 
        ,@backup_directory = N'\\tBSclient\LogShippingBackup' 
        ,@backup_share = N'\\tBSclient\LogShippingBackup' 
        ,@backup_job_name = N'LSBackup_SalesOrders' 
        ,@backup_retention_period = 4320
        ,@backup_compression = 2
        ,@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_TBSSql\SQLPROD1' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20150315 
        ,@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'SalesOrders' 
        ,@secondary_server = N'TBSCLIENT\SQLMIRROR' 
        ,@secondary_database = N'SalesOrders' 
        ,@overwrite = 1 

-- ****** End: Script to be run at Primary: [TBSSql\SQLPROD]  ******


-- Execute the following statements at the Secondary to configure Log Shipping 
-- for the database [TBSCLIENT\SQLMIRROR].[SalesOrders],
-- 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: [TBSCLIENT\SQLMIRROR] ******


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'TBSSql\SQLPROD' 
        ,@primary_database = N'SalesOrders' 
        ,@backup_source_directory = N'\\tBSclient\LogShippingBackup' 
        ,@backup_destination_directory = N'C:\Data' 
        ,@copy_job_name = N'LSCopy_TBSSql\SQLPROD_SalesOrders2' 
        ,@restore_job_name = N'LSRestore_TBSSql\SQLPROD_SalesOrders2' 
        ,@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 = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20150315 
        ,@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 = 20150315 
        ,@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'SalesOrders' 
        ,@primary_server = N'TBSSql\SQLPROD' 
        ,@primary_database = N'SalesOrders' 
        ,@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 


-- ****** End: Script to be run at Secondary: [TBSCLIENT\SQLMIRROR] ******



Configure Database Log Shipping in SQL Server

8 comments:

  1. Notwithstanding having the option to send different vehicles inside huge enough holders you are additionally allowed to transport individual things inside the vehicles that you are dispatching henceforth sparing yourself further charges once more. track china post to us

    ReplyDelete
  2. The things that meet all requirements for obligation free transportation to Brazil must be utilized and can't be for resale purposes.nemzetközi raklapos szállítás Europa-Road Kft

    ReplyDelete
  3. Wow i can say that this is another great article as expected of this blog.Bookmarked this site.. EuropaRoad

    ReplyDelete
  4. Hello, this weekend is good for me, since this time i am reading this enormous informative article here at my home. parcels to Pakistan

    ReplyDelete
  5. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful. shipping quote

    ReplyDelete
  6. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this... https://jungleparktoys.com/

    ReplyDelete
  7. An interesting dialogue is price comment. I feel that it is best to write more on this matter, it may not be a taboo topic however usually individuals are not enough to talk on such topics. To the next. Cheers. Europa-Road

    ReplyDelete
  8. Official data shows Hungary recorded a budget deficit of around budapest rental

    ReplyDelete