Configuring Log Shipping

You can configure log shipping using SQL Server Management Studio or by manually running a series of stored procedures. Configuring 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. 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 place the backup share on a separate host computer.

3. Choose a backup schedule for the primary database.

4. 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.

5. Configure one or more secondary databases.

6. Optionally configure a monitor server.

When configuring 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.

When you configure 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:

• SQL Server 2005 Standard Edition, SQL Server 2005 Workgroup Edition, or SQL Server 2005 Enterprise Edition must be installed on all server instances involved in log shipping.
• 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 2005:

• Database mirroring
  If log shipping is used in conjunction 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.
• Notification Services
  For information on configuring log shipping with Notification Services, see Using Log Shipping or Database Mirroring with Notification Services.
• Vardecimal storage format
  Log shipping works correctly with the vardecimal storage format. But each Database Engine must be upgraded to at least SQL Server 2005, service pack 2, and all related databases must be enabled for vardecimal storage format. For more information about vardecimal storage format, see Storing Decimal Data As Variable Length.

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 configure log shipping.

The backup and restore directories in your log shipping configuration must adhere to these requirements:

• For the backup job to be successful, the SQL Server service account on the primary server instance and the proxy account of the backup job (by default, this is the SQL Server Agent account on the primary server instance) must have read/write permissions to the backup directory.
• For the copy job to be successful, the proxy account of the copy job (by default, this is the SQL Server Agent account on the secondary server instance) must have read permissions to the backup directory and write access to the copy directory.
• For the restore job to be successful, the SQL Server service account on the secondary server instance and the proxy account of the restore job (by default, this is the SQL Server Agent account on the secondary server instance) must have read/write access to the copy directory.

See Also

Tasks

How to: Enable Log Shipping (SQL Server Management Studio)
How to: Enable Log Shipping (Transact-SQL)

Concepts

Log Shipping Tables and Stored Procedures
Managing Metadata When Making a Database Available on Another Server Instance
Monitoring Log Shipping

Other Resources

Log Shipping

Help and Information

Getting SQL Server 2005 Assistance

Change History