- 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
- 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.
- Backup the log from primary db
- Restore the log to the secondary db
Log shipping will involves 4 jobs
- Backup job: backup the log from primary db
- Copy job: copy the tran dump file to the share
- Restore job: Apply the tran dump to the secondary db
- 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
- 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'AdventureWorks'
- ,@backup_directory = N'C:\ls_backup'
- ,@backup_share = N'\\Sg11\ls_backup'
- ,@backup_job_name = N'LSBackup_AdventureWorks'
- ,@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_sg11\MSSQL2008R21'
- ,@enabled = 1
- ,@freq_type = 4
- ,@freq_interval = 1
- ,@freq_subday_type = 4
- ,@freq_subday_interval = 5
- ,@freq_recurrence_factor = 0
- ,@active_start_date = 20111104
- ,@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
We can use msdb.dbo.sp_help_job to review the job detail
Setup the alert job
- EXEC master.dbo.sp_add_log_shipping_alert_job
Enable the Log shipping on the primary database
- EXEC master.dbo.sp_add_log_shipping_primary_secondary
- @primary_database = N'AdventureWorks'
- ,@secondary_server = N'SG13\SG13MSSQL'
- ,@secondary_database = N'AdventureWorks'
- ,@overwrite = 1
Create the copy and restore job on the secondary database
- 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'sg11\MSSQL2008R2'
- ,@primary_database = N'AdventureWorks'
- ,@backup_source_directory = N'\\Sg11\ls_backup'
- ,@backup_destination_directory = N'C:\LS_COPY'
- ,@copy_job_name = N'LSCopy_sg11\MSSQL2008R2_AdventureWorks'
- ,@restore_job_name = N'LSRestore_sg11\MSSQL2008R2_AdventureWorks'
- ,@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 = 20111104
- ,@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 = 3
- ,@freq_recurrence_factor = 0
- ,@active_start_date = 20111104
- ,@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'AdventureWorks'
- ,@primary_server = N'sg11\MSSQL2008R2'
- ,@primary_database = N'AdventureWorks'
- ,@restore_delay = 0
- ,@restore_mode = 0
- ,@disconnect_users = 0
- ,@restore_threshold = 6
- ,@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
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
- Use msdb;
- select * from log_shipping_primary_databases
- use master
- go
- 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.
- use master;
- go
- sp_help_log_shipping_primary_secondary [AdventureWorks]
- go
- sp_delete_log_shipping_primary_secondary @primary_database= [AdventureWorks] , @secondary_database = [AdventureWorks],@secondary_server = [SG13\SG13MSSQL]
- 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.
- use master;
- go
- sp_help_log_shipping_primary_database [AdventureWorks]
- go
- 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.
- sp_help_log_shipping_secondary_database [AdventureWorks]
- go
- 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
OMEGA buy replica watches has been a world leader in advanced watch design replica omega since 1848. Over the years, the brand has been widely celebrated for their durability and precision. OMEGA has served as the official timekeepers of the Olympic Games for nearly a century. They also created the official watch of the space program and first watch to land on the moon. Buying an OMEGA watch is an excellent way to invest in a piece of history.
ReplyDelete