Log Shipping Article

List of Tables & Stored Procedures


Primary server tables



                      Table

                                  Description

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

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

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

Stores one monitor record for this primary database.

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


Primary Server Stored Procedures


Stored Procedure

Description

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

Adds a secondary database name to an existing 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.

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

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_secondaryRetrieves secondary database names for a primary database.

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.

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 Procedures

                     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_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.

Monitor Server Tables

            
                      Table

                   Description
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.

Monitor Server Stored Procedures


                       Stored Procedures

                   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_jobReturns 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.

1 comment:

  1. You've written an excellent post, and you've shared it with us about Full Time Remote Jobs. Your article provided me with some unique and useful knowledge. I appreciate you sharing this text with us.

    ReplyDelete