0 Log Shipping

• Log shipping will be deprecate in the future MSSQL Server release. User should consider to use Database Mirror to replace Log shipping.
• Log shipping does not support in the SQL Express.
• The database set up for log shipping must be FULL or BUIL INSERT mode.

Log shipping high level over view

1. Restore the full db from primary to the secondary db. 2 modes can be choosing on the secondary db. Stand by or  non recovery. When use stand by mode, the secondary db can be serve as reporting purpose however, stand by mode can not be used for high available solution.
2. Backup the log from primary db
3. Restore the log to the secondary db

Log shipping will involves 4 jobs

1. Backup job: backup the log from primary db
2. Copy job: copy the tran dump file to the share
3. Restore job: Apply the tran dump to the secondary db
4. Alert job: only will be created when monitor server is configured.

SSMS can be used to generate the Script.

Setup the log backup jobs

Below example is setting up the log backup job for every 5mins

1. DECLARE @LS_BackupJobId    AS uniqueidentifier
2. DECLARE @LS_PrimaryId    AS uniqueidentifier
3. DECLARE @SP_Add_RetCode    As int
4.  
5.  
6. EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
7.         @database = N'AdventureWorks'
8.         ,@backup_directory = N'C:\ls_backup'
9.         ,@backup_share = N'\\Sg11\ls_backup'
10.         ,@backup_job_name = N'LSBackup_AdventureWorks'
11.         ,@backup_retention_period = 4320
12.         ,@backup_compression = 2
13.         ,@backup_threshold = 60
14.         ,@threshold_alert_enabled = 1
15.         ,@history_retention_period = 5760
16.         ,@backup_job_id = @LS_BackupJobId OUTPUT
17.         ,@primary_id = @LS_PrimaryId OUTPUT
18.         ,@overwrite = 1
19.  
20.  
21. IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
22. BEGIN
23.  
24. DECLARE @LS_BackUpScheduleUID    As uniqueidentifier
25. DECLARE @LS_BackUpScheduleID    AS int
26.  
27.  
28. EXEC msdb.dbo.sp_add_schedule
29.         @schedule_name =N'LSBackupSchedule_sg11\MSSQL2008R21'
30.         ,@enabled = 1
31.         ,@freq_type = 4
32.         ,@freq_interval = 1
33.         ,@freq_subday_type = 4
34.         ,@freq_subday_interval = 5
35.         ,@freq_recurrence_factor = 0
36.         ,@active_start_date = 20111104
37.         ,@active_end_date = 99991231
38.         ,@active_start_time = 0
39.         ,@active_end_time = 235900
40.         ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
41.         ,@schedule_id = @LS_BackUpScheduleID OUTPUT
42.  
43. EXEC msdb.dbo.sp_attach_schedule
44.         @job_id = @LS_BackupJobId
45.         ,@schedule_id = @LS_BackUpScheduleID  
46.  
47. EXEC msdb.dbo.sp_update_job
48.         @job_id = @LS_BackupJobId
49.         ,@enabled = 1
50.  
51.  
52. END

We can use msdb.dbo.sp_help_job  to review the job detail

Setup the alert job

1. EXEC master.dbo.sp_add_log_shipping_alert_job

Enable the Log shipping on the primary database

1. EXEC master.dbo.sp_add_log_shipping_primary_secondary
2.         @primary_database = N'AdventureWorks'
3.         ,@secondary_server = N'SG13\SG13MSSQL'
4.         ,@secondary_database = N'AdventureWorks'
5.         ,@overwrite = 1

Create the copy and restore job on the secondary database

1.  
2. DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier
3. DECLARE @LS_Secondary__RestoreJobId    AS uniqueidentifier
4. DECLARE @LS_Secondary__SecondaryId    AS uniqueidentifier
5. DECLARE @LS_Add_RetCode    As int
6.  
7.  
8. EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
9.         @primary_server = N'sg11\MSSQL2008R2'
10.         ,@primary_database = N'AdventureWorks'
11.         ,@backup_source_directory = N'\\Sg11\ls_backup'
12.         ,@backup_destination_directory = N'C:\LS_COPY'
13.         ,@copy_job_name = N'LSCopy_sg11\MSSQL2008R2_AdventureWorks'
14.         ,@restore_job_name = N'LSRestore_sg11\MSSQL2008R2_AdventureWorks'
15.         ,@file_retention_period = 4320
16.         ,@overwrite = 1
17.         ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
18.         ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
19.         ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
20.  
21. IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
22. BEGIN
23.  
24. DECLARE @LS_SecondaryCopyJobScheduleUID    As uniqueidentifier
25. DECLARE @LS_SecondaryCopyJobScheduleID    AS int
26.  
27.  
28. EXEC msdb.dbo.sp_add_schedule
29.         @schedule_name =N'DefaultCopyJobSchedule'
30.         ,@enabled = 1
31.         ,@freq_type = 4
32.         ,@freq_interval = 1
33.         ,@freq_subday_type = 4
34.         ,@freq_subday_interval = 15
35.         ,@freq_recurrence_factor = 0
36.         ,@active_start_date = 20111104
37.         ,@active_end_date = 99991231
38.         ,@active_start_time = 0
39.         ,@active_end_time = 235900
40.         ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
41.         ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
42.  
43. EXEC msdb.dbo.sp_attach_schedule
44.         @job_id = @LS_Secondary__CopyJobId
45.         ,@schedule_id = @LS_SecondaryCopyJobScheduleID  
46.  
47. DECLARE @LS_SecondaryRestoreJobScheduleUID    As uniqueidentifier
48. DECLARE @LS_SecondaryRestoreJobScheduleID    AS int
49.  
50.  
51. EXEC msdb.dbo.sp_add_schedule
52.         @schedule_name =N'DefaultRestoreJobSchedule'
53.         ,@enabled = 1
54.         ,@freq_type = 4
55.         ,@freq_interval = 1
56.         ,@freq_subday_type = 4
57.         ,@freq_subday_interval = 3
58.         ,@freq_recurrence_factor = 0
59.         ,@active_start_date = 20111104
60.         ,@active_end_date = 99991231
61.         ,@active_start_time = 0
62.         ,@active_end_time = 235900
63.         ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
64.         ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
65.  
66. EXEC msdb.dbo.sp_attach_schedule
67.         @job_id = @LS_Secondary__RestoreJobId
68.         ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  
69.  
70.  
71. END
72.  
73.  
74. DECLARE @LS_Add_RetCode2    As int
75.  
76.  
77. IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
78. BEGIN
79.  
80. EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
81.         @secondary_database = N'AdventureWorks'
82.         ,@primary_server = N'sg11\MSSQL2008R2'
83.         ,@primary_database = N'AdventureWorks'
84.         ,@restore_delay = 0
85.         ,@restore_mode = 0
86.         ,@disconnect_users    = 0
87.         ,@restore_threshold = 6   
88.         ,@threshold_alert_enabled = 1
89.         ,@history_retention_period    = 5760
90.         ,@overwrite = 1
91.  
92. END
93.  
94.  
95. IF (@@error = 0 AND @LS_Add_RetCode = 0)
96. BEGIN
97.  
98. EXEC msdb.dbo.sp_update_job
99.         @job_id = @LS_Secondary__CopyJobId
100.         ,@enabled = 1
101.  
102. EXEC msdb.dbo.sp_update_job
103.         @job_id = @LS_Secondary__RestoreJobId
104.         ,@enabled = 1
105.  
106. END

Before the log shipping job actually start working, we will need to restore the database from primary to secondary with NONRECOVERY option. 

How to check which database has the log shipping set up?

msdb.log_shipping_primary_databases or  sp_help_log_shipping_primary_database

1. Use msdb;
2. select * from log_shipping_primary_databases

1. use master
2. go
3. sp_help_log_shipping_primary_database  @database=N'AdventureWorks'

More store procedure or system table can be found here http://msdn.microsoft.com/en-us/library/ms175106.aspx

 

Remove Log shipping

On the primary server

Use On the primary server, sp_help_log_shipping_primary_secondary to exam the log shipping secondary db setup and execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.

1. use master;
2. go
3. sp_help_log_shipping_primary_secondary [AdventureWorks]
4. go
5. sp_delete_log_shipping_primary_secondary @primary_database= [AdventureWorks] , @secondary_database = [AdventureWorks],@secondary_server = [SG13\SG13MSSQL]
6. go

Use sp_help_log_shipping_primary_database  to exame the primary database setup and use sp_delete_log_shipping_primary_database to remove the primary database being as log shipping database. this will remove the backup log job too.

1. use master;
2. go
3. sp_help_log_shipping_primary_database [AdventureWorks]
4. go
5. sp_delete_log_shipping_primary_database [AdventureWorks]

On the secondary server, use sp_help_log_shipping_secondary_database to exam the secondary information and use sp_help_log_shipping_secondary_database  to remove it. this will remove the restore job and copy job too.

1. sp_help_log_shipping_secondary_database [AdventureWorks]
2. go
3. sp_delete_log_shipping_secondary_database @secondary_database = [AdventureWorks]

Notes

Why I can not find the log shipping option in the database property within the SSMS?

The log shipping and database mirror is only support in the non express version. You wont be able to find this option in the SQL Express SSMS.

From SQL Express SSMS

From SQL Server Enterprise SSMS

 

Reference

Log Shipping Overview http://msdn.microsoft.com/en-us/library/ms187103.aspx