• Article

Log Shipping Deployment

You can enable log shipping using SQL Server Management Studio or by manually running a series of stored procedures.

Basic Steps

Enabling log shipping involves the following basic steps:

  1. Choose servers for your primary server, secondary server, and optional monitor server.

    Note

    The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a log shipping configuration can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

  2. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by Standard and all higher editions. When creating a log shipping configuration, you can control the backup compression behavior of log backups. For more information, see Backup Compression (SQL Server).

  3. Create a file share for the transaction log backups, preferably on a fault-tolerant server that is not part of the log shipping configuration. To maximize availability of the primary server, Microsoft recommends as a best practice that you put the backup share on a separate host computer.

  4. Choose a backup schedule for the primary database.

  5. Create a folder for each secondary server into which the transaction log backup files will be copied. These folders are usually located on the secondary servers.

  6. Configure one or more secondary databases.

  7. Optionally configure a monitor server.

When enabling the secondary server for log shipping, you can choose the following options on the Management Studio log shipping Secondary Database Settings dialog box to set up the secondary database:

  • Automatically create a backup of the primary database and restore it to the secondary server, creating the secondary database if necessary.

  • Restore a pre-existing backup of the primary database to the secondary server, creating the secondary database if necessary.

You can also initialize the secondary database by restoring a database backup manually.

Important

The Management Studio log shipping tool is intended to handle only simple cases of backup and restore. For complex cases, such as a database that uses many files or nondefault options, you must back up and restore the full database manually. In general, use manual backup and restore for any case that requires a complex BACKUP or RESTORE command. After the secondary database has been restored, use the Management Studio log shipping tool to finish setting up log shipping.

For more information about these Transact-SQL statements, see BACKUP (Transact-SQL) and RESTORE (Transact-SQL).

When you enable the primary server for log shipping, you can specify how often transaction log backups are created on the primary server. If the volume of transactions is high, it may be useful to back up the transaction log frequently to minimize the potential loss of data.

Requirements

Log shipping has the following requirements:

  • For information about which editions of SQL Server support log shipping, see Features Supported by the Editions of SQL Server 2008 R2.  

  • The servers involved in log shipping should have the same case-sensitivity settings.

  • The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

Using Log Shipping with Other Features or Components

Log shipping can be used with the following features or components of SQL Server:

  • Database mirroring

    If log shipping is used together with database mirroring, the current primary database of the log shipping configuration must be the same database as the current principal database of database mirroring. For more information, see Database Mirroring and Log Shipping.

  • Replication

    For information about the effects of log shipping on replication behavior and the requirements and procedures for replicating from the secondary if the primary is lost, see Replication and Log Shipping.

Viewing Transaction Log Shipping Status (SQL Server Management Studio)

For any server instance involved in log shipping (whether a monitor, primary, or secondary server), SQL Server Management Studio offers a report about the log shipping activity of the current server instance. For more information, see How to: View the Log Shipping Report (SQL Server Management Studio).

Permissions

You must be a sysadmin on each server instance to enable log shipping. The backup and restore directories in your log shipping configuration must follow these requirements.

  • For the backup job, read/write permissions to the backup directory are required on the following:

    • The SQL Server service account on the primary server instance. 

    • The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance. 

  • For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.

  • For the restore job, read/write permission to the copy directory are required by the following:

    • The SQL Server service account on the secondary server instance. 

    • The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.