sp_help_log_shipping_primary_database (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Retrieves primary database settings.

Topic link icon Transact-SQL Syntax Conventions

[ @database = ] 'database' OR  
[ @primary_id = ] 'primary_id'  

[ @database = ] 'database'
Is the name of the log shipping primary database. database is sysname, with no default, and cannot be NULL.

[ @primary_id = ] 'primary_id'
The ID of the primary database for the log shipping configuration. primary_id is uniqueidentifier and cannot be NULL.

0 (success) or 1 (failure)

Column nameDescription
primary_idThe ID of the primary database for the log shipping configuration.
primary_databaseThe name of the primary database in the log shipping configuration.
backup_directoryThe directory where transaction log backup files from the primary server are stored.
backup_shareThe network or UNC path to the backup directory.
backup_retention_periodThe length of time, in minutes, that a log backup file is retained in the backup directory before being deleted.
backup_compressionIndicates whether the log shipping configuration uses backup compression.

 0 = Disabled. Never compress log backups.

 1 = Enabled. Always compress log backups.

 2 = Use the setting of the View or Configure the backup compression default Server Configuration Option. This is the default value.

Backup compression is supported only in SQL Server 2008 Enterprise (or a later version). In other editions, the value is always 2.
backup_job_idThe Microsoft SQL Server Agent job ID associated with the backup job on the primary server.
monitor_serverThe name of the instance of the SQL Server Database Engine being used as a monitor server in the log shipping configuration.
monitor_server_security_modeThe security mode used to connect to the monitor server.

1 = Microsoft Windows Authentication.

0 = SQL Server Authentication.
backup_thresholdThe number of minutes allowed to elapse between backup operations before an alert is generated.
threshold_alertThe alert to be raised when the backup threshold is exceeded.
threshold_alert_enabledDetermines if backup threshold alerts are enabled.

 1 = Enabled.

 0 = Disabled.
last_backup_fileThe absolute path of the most recent transaction log backup.
last_backup_dateThe time and date of the last log backup operation.
last_backup_date_utcThe time and date of the last transaction log backup operation on the primary database, expressed in Coordinated Universal Time.
history_retention_periodThe amount of time, in minutes, that log shipping history records are retained for a given primary database before being deleted.

sp_help_log_shipping_primary_database must be run from the master database on the primary server.

Only members of the sysadmin fixed server role can run this procedure.

This example illustrates using sp_help_log_shipping_primary_database to retrieve primary database settings for the database AdventureWorks2012.

EXEC master.dbo.sp_help_log_shipping_primary_database @database=N'AdventureWorks2012';  

About Log Shipping (SQL Server)
System Stored Procedures (Transact-SQL)

Community Additions