T-SQL Script to Log Shipping Database.
Pre-req. Check List:
Database should be in Full recovery mode.
Take Full database back on the primary server: WIN-Primary\DB04 . Note: I am using Named Instance DB04.
Take a log backup
Manually Copy the both the backup files to Secondary File Share.
Restore Full backup with no recovery. Secondary Server the Database name should be same
Restore log backup with no recovery.
Execute the following script in primary server. It will create Log backup job and log shipping alert job. Server: [WIN-Primary\DB04].[TestDB]
-- Execute the following statements at the Primary to configure Log Shipping
-- for the database [WIN-Primary\DB04].[TestDB],
-- 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: [WIN-Primary\DB04] ******
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'TestDB'
,@backup_directory = N'\\fsdbclus01\backup\DB04\Log\TestDB'
,@backup_share = N'\\fsdbclus01\backup\DB04\Log\TestDB'
,@backup_job_name = N'LSBackup_TestDB'
,@backup_retention_period = 1500
,@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_WIN-Primary\DB041'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20191203
,@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'TestDB'
,@secondary_server = N'WIN-Secondary\DB04'
,@secondary_database = N'TestDB'
,@overwrite = 1
-- ****** End: Script to be run at Primary: [WIN-Primary\DB04] ******
8. Execute the following script in Secondary server: [WIN-Secondary\DB04]. This Script will create a Restore job and Copy job - Copy Job Copies data from Primary File Share to Secondary File Share.
-- Execute the following statements at the Secondary to configure Log Shipping
-- for the database [WIN-Secondary\DB04].[TestDB],
-- 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: [WIN-Secondary\DB04] ******
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'WIN-Primary\DB04'
,@primary_database = N'TestDB'
,@backup_source_directory = N'\\fsdbclus01\backup\DB04\Log\TestDB'
,@backup_destination_directory = N'\\WIN-Secondary\Backup\DB04\Log\TestDB'
,@copy_job_name = N'LSCopy_WIN-Primary\DB04_TestDB'
,@restore_job_name = N'LSRestore_WIN-Primary\DB04_TestDB'
,@file_retention_period = 1500
,@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 = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20191203
,@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 = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20191203
,@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'TestDB'
,@primary_server = N'WIN-Primary\DB04'
,@primary_database = N'TestDB'
,@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: [WIN-Secondary\DB04] ******
--Add a files to primary database
--add log file
USE [master]
GO
ALTER DATABASE [Primary_DB_Name] ADD LOG FILE
( NAME = N'Primary_DB_Name_LOG_2', FILENAME = N'F:\LOGS03\Primary_DB_Name_LOG_2.LDF' ,
SIZE = 102400000KB , FILEGROWTH = 512000KB )
GO
--Script to restore the log backup on the secondary server
RESTORE log [Secondary DB_Name] FROM Disk='\\filesharename\dbname\LogShipping\DB_Name_20200111122001.trn'
with norecovery,
MOVE 'DB_Name_LOG_2' TO 'M:\LOGS03\DB_Name_LOG_2.LDF'
Note: If you remove any data or log from primary db, no steps are required on secondary. It will be automatically removed.