Export (0) Print
Expand All

Configuring Log Shipping

Updated: 12 December 2006

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.
    ms188698.note(en-US,SQL.90).gifNote:
    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.

ms188698.note(en-US,SQL.90).gifImportant:
The Management Studio log shipping tool is intended to handle only simple cases of backup and restore. For complicated cases, such as a database with numerous files or nondefault options, you must backup and restore the full database manually. In general, use manual backup and restore for any case that requires a complex BACKUP or RESTORE command. Once the secondary database has been restored, use the Management Studio log shipping tool to finish setting up log shipping.

ms188698.note(en-US,SQL.90).gifNote:
For more information about these Transact-SQL statements, see BACKUP (Transact-SQL) and RESTORE (Transact-SQL).

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.

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.

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.

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

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.

Release History

12 December 2006

New content:
  • Added a note about the SQL Server on-disk storage format being the same in 64-bit and 32-bit environments.
  • Added bullet regarding vardecimal storage format.

Community Additions

ADD
Show:
© 2014 Microsoft