Log shipping refers to the process of taking transaction log backups of a database on one server, shipping them to a secondary server and restoring them.

In the log shipping process:

 Transaction log backups are performed on the source database on the primary SQL Server (for example the production database).

  The backup files are copied to a network share that can be accessed by both SQL Servers. 

  The backup files are restored to the destination database on the secondary SQL Server.

Log shipping is useful if you are maintaining a "standby" server as a backup to your primary server, but do not require automatic failover. For information on manually failing over to a log-shipped standby server, see Failing over to a standby server.

Alternatives for maintaining a standby server include replication, a failover clustering solution, AlwaysOn Availability Groups, and database mirroring. For more information, see Configuring High Availability in SQL Server Books Online. For a detailed comparison of log shipping and replication, see Log Shipping vs Replication on SQLServerCentral.com.

Why use SQL Backup for log shipping?

SQL Backup simplifies the configuration process for log shipping and protects against network outages. It also makes the backup and restore process up to ten times faster than native backup and restore, and supports compression and encryption.

The SQL Backup graphical user interface provides a Log Shipping wizard for the configuration of log shipping. If you have not already set up the destination database for log shipping, the wizard can do this for you by taking a full backup of the source database and restoring it to a new or existing database on the secondary SQL Server. This ensures the destination database is consistent with the primary database and in the correct state for receiving the transaction log backups.

The wizard then sets up SQL Server Agent jobs that use SQL Backup to perform the backup and restore operations. A SQL Server Agent job on the primary server periodically backs up the transaction logs and copies the backup files to the shared folder. A job on the standby server periodically retrieves the backup files from the shared folder and restores the transaction logs.

If the copying process fails for a backup file (due to an extended network outage, for example), SQL Backup automatically attempts to copy the file to the network share at regular intervals. Any future transaction log backups that are created are queued behind the file that could not be copied. Once the copying process is working again (because the network outage has been fixed, for example), SQL Backup copies each transaction log backup to the network share, in the correct order.

For more information about using the wizard to set up log shipping, see Configuring log shipping.

Prerequisites

• You must have at least two SQL Server database engine servers or two database engine instances in your log shipping implementation.

  You cannot ship transaction logs from a later version of SQL Server to an earlier version (for example, from SQL Server 2005 to SQL Server 2000) because of the differences in their structures. You can ship transaction logs from earlier versions of SQL Server to later versions (for example, from SQL Server 2005 to SQL Server 2008), but you must select the Non-operational recovery completion state (RESTORE WITH NORECOVERY). This is a SQL Server restriction.

• SQL Backup server components must be installed on both of the SQL Servers.
  However, you cannot ship transaction log backups created on a SQL Server instance that has SQL Backup 6 server components or later installed, to an instance that has an earlier version of the server components installed. This is because of differences in the SQL Backup (.sqb) file format introduced in SQL Backup 6. In this scenario, you should update the destination SQL Server instance with the latest SQL Backup server components before configuring log shipping.

• The source database must use the full or bulk-logged recovery model. Databases using the simple recovery model do not implement the transaction log in a manner compatible with log shipping and will not be displayed. Note that a number of issues may arise when changing a database recovery model; refer to your SQL Server documentation for more information.
• You must have a shared folder to copy the transaction log backups to. The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder. The SQL Server Agent account of the standby server must have read and delete access to the shared folder.
• The SQL Server Agent services must be running and configured with network credentials (such as a domain account) if you plan to use a network share as the shared folder. You can configure log shipping with SQL Server Agent services stopped, but the process does not run until the Agent is started.
• You must have execute permissions on the following extended stored procedures: sqbdata, sqbdir, sqbmemory, sqbstatus, sqbutility, sqlbackup.
• You must be a member of the sysadmin fixed server role on the participating servers.

Customizing log shipping

The Log Shipping wizard may not provide all the options you need. If required, you can use the wizard to set up the jobs, and then use SQL Server Management Studio to modify the job steps manually.

For more information about SQL Backup syntax, see Scripting SQL Backup.

Monitoring the log shipping

You can use the free Log Shipping Monitor tool for collating and summarizing SQL Server log shipping activity. The Log Shipping Monitor provides customizable alerts for key log shipping variables, allowing you to take steps to avoid log shipping failures. For example, you can set up alerts to notify you if the free space on a drive falls below a specified size, or if no transaction log backups have been created or restored on the standby server for a specified period of time.

In the event of a log shipping failure, you can use the information provided by the Log Shipping Monitor to identify possible causes so that you can avoid future failures.

You can download the Log Shipping Monitor from Redgate Labs.