Log Shipping Internals

Log shipping is the oldest Disaster Recovery feature provided by SQL Server. The best part is that it is very easy to implement. Check here for the steps to be followed.

It creates three jobs for: Backup the log at primary, Copy the backup file to secondary and the last one to Restore the backup.

If you think that the SQL Server Agent job step for the same must be including the very basic BACKUP LOG script than you are wrong. As it basically calls sqllogship.exe file.

In all the three jobs the same sqllogship.exe file is called.

Out of curiosity to know what it does, I configured the log shipping and stopped every thing else on the server started a trace to capture RPC SP and SQLStmt completed and ran the job for backup to see what it does, these are the steps it follows :

Backup job

1. It adds the information about the initiation of backup into the history table of the log shipping.

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'b4d51ba8-0789-4050-9733-2b8c809f8700', 
@agent_type = 0, 
@session_id = 5, 
@database = null, 
@session_status = 0, 
@last_processed_file_name = null, 
@message =  N'Starting transaction log backup. 
Primary ID: ''b4d51ba8-0789-4050-9733-2b8c809f8700'''

You can view this history in [dbo].[log_shipping_monitor_history_detail]

2. Then it adds history stating “Retrieving backup history”

3. To recover the backup history and more information the below command is used:

 exec master.sys.sp_help_log_shipping_primary_database 
@primary_id = N'b4d51ba8-0789-4050-9733-2b8c809f8700'

4. Then it pushes all the information captured into history table again.

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'b4d51ba8-0789-4050-9733-2b8c809f8700', 
@agent_type = 0, 
@session_id = 5, 
@database = N'AdventureWorks2012Logshipped', 
@session_status = 1, 
@last_processed_file_name = null, 
@message =  N'Retrieved backup settings. 
Primary Database: ''AdventureWorks2012Logshipped'', 
Backup Directory: ''\\ICEN057\Data\Backup_LS'', 
Backup Retention Period: 4320 minute(s), 
Backup Compression: Server Default'

5. Once it has all the information it begin the backup pre-checks. Which includes checking the status of the database, standby status, checks for if the current database is preferred backup replica (in case its in AG). Post the checks it again add the information into history details as below

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'b4d51ba8-0789-4050-9733-2b8c809f8700', 
@agent_type = 0, 
@session_id = 5, 
@database = N'AdventureWorks2012Logshipped', 
@session_status = 1,
@last_processed_file_name = null, 
@message =  N'Backing up transaction log. 
Primary Database: ''AdventureWorks2012Logshipped'', 
Log Backup File:''//ICEN057/Data/Backup_LS\AdventureWorks2012Logshipped_20200216150542.trn'''

6. Finally initiates the backup command.

BACKUP LOG [AdventureWorks2012Logshipped] TO  DISK = N'//ICEN057/Data/Backup_LS\AdventureWorks2012Logshipped_20200216150542.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD,  STATS = 10

7. Once this backup is completed the old backups based on the retention are deleted and again the history table is updated with the information for the both deleting old backups and successful completion of the backup.

Copy job:

1. Starts the process by writing into the history table.

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a',
@agent_type = 1, 
@session_id = 12, 
@database = null, 
@session_status = 0, 
@last_processed_file_name = null, 
@message =  N'Starting transaction log copy. 
Secondary ID: ''181510d2-5ea5-4d7d-baa4-6c0aaee3101a'''

2. Again writes to the history stating “Retrieving copy settings”

3. To reterive the copy setting uses the below command.

exec master.sys.sp_help_log_shipping_secondary_database 
@secondary_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a'

The information it is able to get is handful and is as below

4. Writes to history tables with the information retrieved above.

5. Now as it has all the information it need to proceed with the copy it again writes all the detail into the history table.

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a', 
@agent_type = 1, 
@session_id = 12,
@database = null, 
@session_status = 1, 
@last_processed_file_name = null, 
@message =  N'Copying log backup files. 
Primary Server: ''ICEN057\SSCENTRICSERVER'', 
Primary Database: ''AdventureWorks2012Logshipped'', 
Backup Source Directory: ''\\ICEN057\Data\Backup_LS'', 
Backup Destination Directory: ''\\ICEN057\Data\Copy_LS'''

6. Creates a temporary “wrk” file and writes the information into the history table.

7. Once copy is successful the “wrk” file extension is changed back to .trn

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a', 
@agent_type = 1, 
@session_id = 12, 
@database = null, 
@session_status = 1, 
@last_processed_file_name = N'//ICEN057/Data/Copy_LS\AdventureWorks2012Logshipped_20200216163001.trn', @message =  N'Renamed temporary work file. 
Source: ''//ICEN057/Data/Copy_LS\AdventureWorks2012Logshipped_20200216163001.wrk'', Destination: ''//ICEN057/Data/Copy_LS\AdventureWorks2012Logshipped_20200216163001.trn'''

8. Successful completion of the copy is written to history.

Restore Job:

1. Till now you must have got the idea that it writes each every step that it does into the history detail table in the msdb database.

2. It starts with writing to the history table with the message ‘Starting transaction log restore’.

3. Retrieves the restore information using the below query, the same it used to for copy information.

exec master.sys.sp_help_log_shipping_secondary_database 
@secondary_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a'

4. Once it has got all the information required it writes that into the history table as below.

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a', 
@agent_type = 2, 
@session_id = 11, 
@database = N'AdventureWorks2012Logshipped', 
@session_status = 1, 
@last_processed_file_name = null, 
@message =  N'Retrieved database restore settings. 
Secondary Database: ''AdventureWorks2012Logshipped'', 
Restore Delay: 0, Restore All: True, 
Restore Mode: No Recovery, Disconnect Users: False, 
Last Restored File: \\ICEN057\Data\Copy_LS\AdventureWorks2012Logshipped_20200216161501.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified'

5. Based upon the above information it initiates the restore.

RESTORE LOG [AdventureWorks2012Logshipped] FROM DISK = N'\\ICEN057\Data\Copy_LS\AdventureWorks2012Logshipped_20200216163001.trn' WITH FILE = 1, NORECOVERY

6. Once restore completes it writes to history stating the successful completion of the restore.

 exec master.sys.sp_MSadd_log_shipping_history_detail 
@agent_id = N'181510d2-5ea5-4d7d-baa4-6c0aaee3101a', 
@agent_type = 2, 
@session_id = 11, 
@database = N'AdventureWorks2012Logshipped', 
@session_status = 1, 
@last_processed_file_name = N'\\ICEN057\Data\Copy_LS\AdventureWorks2012Logshipped_20200216163001.trn', @message =  N'Restored log backup file. 
Secondary DB: ''AdventureWorks2012Logshipped'', 
File: ''\\ICEN057\Data\Copy_LS\AdventureWorks2012Logshipped_20200216163001.trn'''

7. Once all the log restores are completed it deletes the old backup as the last step.

As we can see that it does use the sqllogship.exe but still for backup and restore uses T-SQL only.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s