Log Shipping

Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.

Log Shipping Operations

• Back up the transaction log at the primary server instance
• Copy the transaction log file to the secondary server instance.
• Restore the log backup on the secondary server instance.

Important: A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

Configuration consists of

• Primary server and database – must use the full or bulk-logged recovery model;
• Secondary server and databases (can have more than one)
• Optional monitor server

The optional monitor server tracks all of the details of log shipping, including:

• When the transaction log on the primary database was last backed up
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.

Log Shipping Jobs – handled by dedicated SQL Server Agent jobs

• Backup job – created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information
• Copy job – created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server
• Restore Job – created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information
• Alert job – created on the monitor server instance (if used). This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance. Any change to the alert job (such as rescheduling, disabling, or enabling the job) affects all databases using that monitor server. If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.

Enabling and configuring Log Shipping

Source http://msdn.microsoft.com/en-us/library/ms190640.aspx