Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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.

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.

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.

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.

NoteNote

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.

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.

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.

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.

Community Additions

ADD
Show:
© 2015 Microsoft