TechNet
Export (0) Print
Expand All

Log Shipping Tables and Stored Procedures

 

Applies To: SQL Server 2016

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.

TableDescription
log_shipping_monitor_alertStores 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_detailStores error detail for log shipping jobs associated with this primary server.
log_shipping_monitor_history_detailStores history detail for log shipping jobs associated with this primary server.
log_shipping_monitor_primaryStores one monitor record for this primary database.
log_shipping_primary_databasesContains configuration information for primary databases on a given server. Stores one row per primary database.
log_shipping_primary_secondariesMaps primary databases to secondary databases.
Stored ProcedureDescription
sp_add_log_shipping_primary_databaseSets 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_secondaryAdds a secondary database name to an existing primary database.
sp_change_log_shipping_primary_databaseChanges primary database settings including local and remote monitor record.
sp_cleanup_log_shipping_historyCleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_primary_databaseRemoves log shipping of primary database including backup job as well as local and remote history.
sp_delete_log_shipping_primary_secondaryRemoves a secondary database name from a primary database.
sp_help_log_shipping_primary_databaseRetrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.
sp_help_log_shipping_primary_secondaryRetrieves secondary database names for a primary database.
sp_refresh_log_shipping_monitorRefreshes the monitor with the latest information for the specified log shipping agent.
TableDescription
log_shipping_monitor_alertStores 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_detailStores error detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_history_detailStores history detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_secondaryStores one monitor record per secondary database associated with this secondary server.
log_shipping_secondaryContains configuration information for the secondary databases on a given server. Stores one row per secondary ID.
log_shipping_secondary_databasesStores configuration information for a given secondary database. Stores one row per secondary database.
System_CAPS_ICON_note.jpg 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.

Stored ProcedureDescription
sp_add_log_shipping_secondary_databaseSets up a secondary database for log shipping.
sp_add_log_shipping_secondary_primarySets 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_databaseChanges secondary database settings including local and remote monitor records.
sp_change_log_shipping_secondary_primaryChanges secondary database settings such as source and destination directory, and file retention period.
sp_cleanup_log_shipping_historyCleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_secondary_databaseRemoves a secondary database and the local history and remote history.
sp_delete_log_shipping_secondary_primaryRemoves the information about the specified primary server from the secondary server.
sp_help_log_shipping_secondary_databaseRetrieves secondary database settings from the log_shipping_secondary, log_shipping_secondary_databases, and log_shipping_monitor_secondary tables.
sp_help_log_shipping_secondary_primaryThis stored procedure retrieves the settings for a given primary database on the secondary server.
sp_refresh_log_shipping_monitorRefreshes the monitor with the latest information for the specified log shipping agent.
TableDescription
log_shipping_monitor_alertStores alert job ID.
log_shipping_monitor_error_detailStores error detail for log shipping jobs.
log_shipping_monitor_history_detailStores history detail for log shipping jobs.
log_shipping_monitor_primaryStores one monitor record per primary database associated with this monitor server.
log_shipping_monitor_secondaryStores one monitor record per secondary database associated with this monitor server.
Stored ProcedureDescription
sp_add_log_shipping_alert_jobCreates a log shipping alert job if one has not already been created.
sp_delete_log_shipping_alert_jobRemoves a log shipping alert job if there are no associated primary databases.
sp_help_log_shipping_alert_jobReturns the job ID of the alert job.
sp_help_log_shipping_monitor_primaryReturns monitor records for the specified primary database from the log_shipping_monitor_primary table.
sp_help_log_shipping_monitor_secondaryReturns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.

Community Additions

ADD
Show:
© 2016 Microsoft