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:
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.
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.
Choose a backup schedule for the primary database.
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.
Configure one or more secondary databases.
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.
Important
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.
Note
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.
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
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|