Log Shipping Configuration with SQL Server 2012 Step by Step

by Manzoor Siddiqui · Published June 22, 2015 · Updated September 24, 2015

Log shipping is a low cost technique for disaster recovery solution which you can setup on primary server database and one or more secondary server databases. This is basically a three step process backup, copy and restore involved in to configure log shipping. Once this technique is implemented it will automatically backup the transaction log on primary server as per job schedule then it will copy the transaction log on secondary server and finally it will restore log backups. You should have sysadmin rights on the server to implement log shipping and read write permission on folder involved to hold transaction log.

It is better to have same version of SQL Server on both side of primary and secondary server so that you can do manual failover. You can do failover from lower version of SQL Server to higher version of SQL Server but reverse is not possible. Also your database must be in full recovery model or bulk-logged recovery model to configure and implement log shipping.

Now we will see step by step process how to configure log shipping in SQL Server 20012. For demonstration purpose I am going to use ‘Inventory’ database.

First of all verify if your database recovery model is full or bulk-logged with below query.

--Check Recovery Model of Database
USE [master]
GO
SELECT name, compatibility_level, recovery_model, recovery_model_desc, state_desc
FROM sys.databases WHERE name = 'Inventory'
GO

If it is simple recovery model then alter the same with below query.

USE [master]
GO
ALTER DATABASE [Inventory] SET RECOVERY FULL WITH NO_WAIT
GO

Now take the full backup of Inventory database as we are going to use this database as log shipping on secondary database server after restore. [Click here to know how to backup a database.]

Now go to secondary server and restore database ‘Inventory’ with set recovery state as RESTORE WITH STANDBY mode. [Click Here to know how to restore database.]

Figure 2: Restore Database on Secondary Server with Standby mode

On primary server, right click on ‘Inventory’ database and click on Properties.

Select Transaction Log Shipping page and check mark ‘Enable this as a primary database in a log shipping configuration’. [refer Figure-3]

Figure 3: Database Properties

Click on Backup Settings… New window of Transaction Log Backup Settings will pop up where you have to specify network path to backup folder or if the backup folder is located on the primary server then put local folder path, then click OK. [refer Figure-4]

Figure 4: Transaction Log Backup Settings

Make sure that you have assigned read and write permission on this folder to SQL Server service account then click OK.

In next step you have to map secondary server instance and database so click on Add button in secondary databases option and you will get below screen.

Figure 5: Secondary Database Settings – Initialize Secondary Database

Now click Connect… button to map Secondary Server Instance and Database.

On this page, you have to configure three tabs which are enabled as given below.

– Initialize Secondary Database
– Copy Files
– Restore Transaction Log

In Initialize Secondary Database tab, select option ‘No, the secondary database is initialised’, because we have already restored database on secondary server in our previous step. [refer Figure-5]

Now in Copy Files tab, specify destination folder for copied files i.e. you can mention network shared folder path then select Restore Transaction Log tab.

Figure 6: Secondary Database Settings – Copy Files

Choose Standby mode in database state when restoring backups and select ‘Disconnect users in the database when restoring backups’. Now click on OK button. It will create a job to restore transaction log.

Figure 7: Secondary Database Settings – Restore Transaction Log

So, finally we have configured Log Shipping, here I am not setting up monitor server instance as this is optional and also I do not have requirement but if you require you can select ‘Use a monitor server instance’ and do the necessary settings. [refer Figure-8]

Figure 8: Database Properties Configuration Completed

Once you will click OK our configured settings will be saved and scheduled jobs will be created.

You can verify the log shipping configuration details by executing below query.

Figure 10: Log Shipping Status

USE [master]
GO
EXEC sp_help_log_shipping_primary_database @database = 'Inventory'
GO

After successful log shipping configuration multiple jobs are created on primary and secondary server instance. You can verify the jobs from SQL Server Agents node.

Figure 11: Jobs Created on Primary and Secondary Servers

Finally as per job schedule all the transaction log will be copied and restored to our secondary server database. You can insert some records in Inventory database tables and verify the same.

Configure Log Shipping by T-SQL

If you want to configure log shipping by T-SQL then you can do the same by following below steps.

– Take Full Backup of database from primary server database.

– Restore database on secondary server in standby mode.

– Execute below query on primary server under master database so that it will setup and create required jobs on primary server.

-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [Manzoor].[Inventory],
-- 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: [Manzoor] ******
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'Inventory' 
 ,@backup_directory = N'C:\sidd\LogShip' 
 ,@backup_share = N'\\MANZOOR\LogShip' 
 ,@backup_job_name = N'LSBackup_Inventory' 
 ,@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_Manzoor1' 
 ,@enabled = 1 
 ,@freq_type = 4 
 ,@freq_interval = 1 
 ,@freq_subday_type = 4 
 ,@freq_subday_interval = 15 
 ,@freq_recurrence_factor = 0 
 ,@active_start_date = 20150621 
 ,@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'Inventory' 
 ,@secondary_server = N'MANZOOR\SQL2' 
 ,@secondary_database = N'Inventory' 
 ,@overwrite = 1
-- ****** End: Script to be run at Primary: [Manzoor] ******

Now execute the below query on secondary server under master database so that it will setup and create required jobs on secondary server.

-- Execute the following statements at the Secondary to configure Log Shipping 
-- for the database [MANZOOR\SQL2].[Inventory],
-- 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: [MANZOOR\SQL2] ******
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'Manzoor' 
 ,@primary_database = N'Inventory' 
 ,@backup_source_directory = N'\\MANZOOR\LogShip' 
 ,@backup_destination_directory = N'\\MANZOOR\LogShip' 
 ,@copy_job_name = N'LSCopy_Manzoor_Inventory' 
 ,@restore_job_name = N'LSRestore_Manzoor_Inventory' 
 ,@file_retention_period = 4320 
 ,@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 = 20150621 
 ,@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 = 20150621 
 ,@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'Inventory' 
 ,@primary_server = N'Manzoor' 
 ,@primary_database = N'Inventory' 
 ,@restore_delay = 0 
 ,@restore_mode = 1 
 ,@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: [MANZOOR\SQL2] ******