Transaction Log Shipping

Transaction Log Shipping involves copying the logs from a Primary Server to any number of Secondary servers. A Monitor server can also be involved, and alert you of any problems.

TLS is usually made up of 3 parts:

1. Backup job - Performed on primary server. Logs history to local server and monitor server and deletes old backup files and history info.
2. Copy job - Performed on secondary server. Copies backup files to secondary server, logs history on secondary server and monitor server.
3. Restore job - Performed on secondary server. Restores the backup files to the database, logs history on secondary server and monitor server.

How to setup Transaction Log Shipping

• Open database properties and select Transaction Log Shipping
• Select checkbox to ‘Enable this as a primary database in a log shipping configuration’ (if the database is in SIMPLE recovery mode, you’ll get an error)
• Click Backup Settings…
• Add network and local path to a shared folder where backups can be made and click Schedule to define how often backups are made. Click Ok.
• Click Add… to add a secondary server then Connect and provide the login details
• Check ‘Yes, generate a full backup…’ (unless your database is already initialised on secondary server)
• Click the Copy Files tab and enter the Destination folder for copied files (on secondary server) and again click the button to define a Schedule
• Click Restore Transaction Log tab and select Standby mode and Disconnect users in the database when restoring backups, again defining a Schedule.
• Click OK
• Click OK again

NB: In order to bring the database on the secondary server back online:

RESTORE DATABASE <db_name> WITH RECOVER