Log Shipping Tables and Stored Procedures

This topic describes all of the tables and stored procedures associated with a log shipping configuration. All log shipping tables are stored in msdb on each server. The tables below describe which tables and stored procedures are used on which servers in a log shipping configuration.

Primary Server Tables

Table Description

log_shipping_monitor_alert

Stores alert job ID. This table is only used on the primary server if a remote monitor server has not been configured.

log_shipping_monitor_error_detail

Stores error detail for log shipping jobs associated with this primary server.

log_shipping_monitor_history_detail

Stores history detail for log shipping jobs associated with this primary server.

log_shipping_monitor_primary

Stores one monitor record for this primary database.

log_shipping_primary_databases

Contains configuration information for primary databases on a given server. Stores one row per primary database.

log_shipping_primary_secondaries

Maps primary databases to secondary databases.

Primary Server Stored Procedures

Stored Procedure Description

sp_add_log_shipping_primary_database

Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.

sp_add_log_shipping_primary_secondary

Adds a secondary database name to an existing primary database.

sp_change_log_shipping_primary_database

Changes primary database settings including local and remote monitor record.

sp_cleanup_log_shipping_history

Cleans up history locally and on the monitor based on retention period.

sp_delete_log_shipping_primary_database

Removes log shipping of primary database including backup job as well as local and remote history.

sp_delete_log_shipping_primary_secondary

Removes a secondary database name from a primary database.

sp_help_log_shipping_primary_database

Retrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.

sp_help_log_shipping_primary_secondary

Retrieves secondary database names for a primary database.

sp_refresh_log_shipping_monitor

Refreshes the monitor with the latest information for the specified log shipping agent.

Secondary Server Tables

Table Description

log_shipping_monitor_alert

Stores alert job ID. This table is only used on the secondary server if a remote monitor server has not been configured.

log_shipping_monitor_error_detail

Stores error detail for log shipping jobs associated with this secondary server.

log_shipping_monitor_history_detail

Stores history detail for log shipping jobs associated with this secondary server.

log_shipping_monitor_secondary

Stores one monitor record per secondary database associated with this secondary server.

log_shipping_secondary

Contains configuration information for the secondary databases on a given server. Stores one row per secondary ID.

log_shipping_secondary_databases

Stores configuration information for a given secondary database. Stores one row per secondary database.

Note

Secondary databases on the same secondary server for a given primary database share the settings in the log_shipping_secondary table. If a shared setting is altered for one secondary database, the setting is altered for all of them.

Secondary Server Stored Procedures

Stored Procedure Description

sp_add_log_shipping_secondary_database

Sets up a secondary database for log shipping.

sp_add_log_shipping_secondary_primary

Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.

sp_change_log_shipping_secondary_database

Changes secondary database settings including local and remote monitor records.

sp_change_log_shipping_secondary_primary

Changes secondary database settings such as source and destination directory, and file retention period.

sp_cleanup_log_shipping_history

Cleans up history locally and on the monitor based on retention period.

sp_delete_log_shipping_secondary_database

Removes a secondary database and the local history and remote history.

sp_delete_log_shipping_secondary_primary

Removes the information about the specified primary server from the secondary server.

sp_help_log_shipping_secondary_database

Retrieves secondary database settings from the log_shipping_secondary, log_shipping_secondary_databases, and log_shipping_monitor_secondary tables.

sp_help_log_shipping_secondary_primary

This stored procedure retrieves the settings for a given primary database on the secondary server.

sp_refresh_log_shipping_monitor

Refreshes the monitor with the latest information for the specified log shipping agent.

Monitor Server Tables

Table Description

log_shipping_monitor_alert

Stores alert job ID.

log_shipping_monitor_error_detail

Stores error detail for log shipping jobs.

log_shipping_monitor_history_detail

Stores history detail for log shipping jobs.

log_shipping_monitor_primary

Stores one monitor record per primary database associated with this monitor server.

log_shipping_monitor_secondary

Stores one monitor record per secondary database associated with this monitor server.

Monitor Server Stored Procedures

Stored Procedure Description

sp_add_log_shipping_alert_job

Creates a log shipping alert job if one has not already been created.

sp_delete_log_shipping_alert_job

Removes a log shipping alert job if there are no associated primary databases.

sp_help_log_shipping_alert_job

Returns the job ID of the alert job.

sp_help_log_shipping_monitor_primary

Returns monitor records for the specified primary database from the log_shipping_monitor_primary table.

sp_help_log_shipping_monitor_secondary

Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.

See Also

Other Resources

Log Shipping

Help and Information

Getting SQL Server 2005 Assistance