Log Shipping Tables and Stored Procedures
Applies to: 
SQL Server
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. |