Five Undocumented SQL Server 2014 log shipping stored procedures
In this article, I want to describe the following five undocumented log shipping stored
procedures shipped with SQL Server 2014:
- sp_check_log_shipping_monitor_alert
- sp_create_log_shipping_monitor_account
- sp_delete_log_shipping_monitor_info
- sp_delete_log_shipping_monitor_jobs
- sp_log_shipping_get_date_from_file
sp_check_log_shipping_monitor_alert
The sp_check_log_shipping_monitor_alert log shipping stored procedure is used to check all monitor
alerts in the primary database and in the secondary database in log shipping configuration.
Syntax
sp_check_log_shipping_monitor_alert
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
This stored procedure exists in the master database and must be invoked from the master database.
Permissions
Only members of the sysadmin fixed server role can run this procedure.
This stored procedure is supported in SQL Server 2014 Enterprise, Developer ? Standard Edition only.
Example
This is the example to check all monitor alerts:
USE master
EXEC sp_check_log_shipping_monitor_alert
sp_create_log_shipping_monitor_account
The sp_create_log_shipping_monitor_account log shipping stored procedure is used to create
the log_shipping_monitor_probe login on the monitor server, and assigns update permissions
to msdb.dbo.log_shipping_primaries and msdb.dbo.log_shipping_secondaries tables.
Syntax
sp_create_log_shipping_monitor_account [ @password = ] ‘password’
Arguments
[ @password = ] ‘password’
Is the password for the log_shipping_monitor_probe account. password is sysname,
with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None.
Remarks
The log_shipping_monitor_probe account is used by the primary and secondary servers when a
transaction log has been backed up, copied, or restored. This stored procedure exists in the
msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This is the example to create a log shipping monitor account with the password "a4d8ght5":
USE msdb
EXEC sp_create_log_shipping_monitor_account @password = ‘a4d8ght5’
sp_delete_log_shipping_monitor_info
The sp_delete_log_shipping_monitor_info log shipping stored procedure is used to remove a log
shipping pair from a log shipping monitor.
Syntax
sp_delete_log_shipping_monitor_info [ @primary_server_name = ] ‘primary_server_name’,
[ @primary_database_name = ] ‘primary_database_name’,
[ @secondary_server_name = ] ‘secondary_server_name’,
[ @secondary_database_name = ] ‘secondary_database_name’
Arguments
[ @primary_server_name = ] ‘primary_server_name’
Is the name of the primary server. primary_server_name is sysname, with no default.
[ @primary_database_name = ] ‘primary_database_name’
Is the name of the primary database. primary_database_name is sysname, with no default.
[ @secondary_server_name = ] ‘secondary_server_name’
Is the name of the secondary server. secondary_server_name is sysname, with no default.
[ @secondary_database_name = ] ‘secondary_database_name’
Is the name of the secondary database. secondary_database_name is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None.
Remarks
The sp_delete_log_shipping_monitor_info stored procedure does not delete the actual log shipping
pair, it only removes a log shipping pair from a log shipping monitor. This stored procedure
exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This example removes a log shipping pair from a log shipping monitor:
USE msdb
EXEC sp_delete_log_shipping_monitor_info
@primary_server_name = ‘PrSalesSrv’,
@primary_database_name = ‘PrSalesDb’,
@secondary_server_name = ‘SdSalesSrv’,
@secondary_database_name = ‘SdSalesDb’
sp_delete_log_shipping_monitor_jobs
The sp_delete_log_shipping_monitor_jobs log shipping stored procedure drops the
‘Log Shipping Alert Job – Backup’ and ‘Log Shipping Alert Job – Restore’ jobs.
Syntax
sp_delete_log_shipping_monitor_jobs
Return Code Values
None.
Result Sets
None.
Remarks
The sp_delete_log_shipping_monitor_jobs stored procedure requires the SQL Server Agent be running.
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This example drops the ‘backup’ and ‘restore’ log shipping alert jobs:
USE msdb
EXEC sp_delete_log_shipping_monitor_jobs
sp_log_shipping_get_date_from_file
The sp_log_shipping_get_date_from_file log shipping stored procedure is user to return the date
from the specified file name in the ISO format (yymmdd).
Syntax
sp_log_shipping_get_date_from_file [ @db_name = ] ‘db_name’,
[ @filename = ] ‘filename’,
[ @file_date = ] ‘file_date’
Arguments
[ @db_name = ] ‘db_name’
Is the database name. db_name is sysname, with no default.
[ @filename = ] ‘filename’
Is the file name. filename is NVARCHAR (500), with no default.
[ @file_date = ] ‘file_date’
Is the date of the file. file_date is DATETIME, with no default. file_date is an output parameter.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Example
This example returns ‘20060504’ from the ‘Sales_tlog_20060504’ file name:
USE msdb
DECLARE @filedate DATETIME
EXEC sp_log_shipping_get_date_from_file
@db_name = ‘Sales’,
@filename = ‘Sales_tlog_20060504’,
@file_date = @filedate OUTPUT
SELECT @filedate