- 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
We can use msdb.dbo.sp_help_job to review the job detail
Setup the alert job
Enable the Log shipping on the primary database
Create the copy and restore job on the secondary database
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
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 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.
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.
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
Log Shipping Overview http://msdn.microsoft.com/en-us/library/ms187103.aspx