Export (0) Print
Expand All
Expand Minimize

sp_help_log_shipping_primary_database (Transact-SQL)

Retrieves primary database settings.

Topic link iconTransact-SQL Syntax Conventions

sp_help_log_shipping_primary_database
[ @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 name

Description

primary_id

The ID of the primary database for the log shipping configuration.

primary_database

The name of the primary database in the log shipping configuration.

backup_directory

The directory where transaction log backup files from the primary server are stored.

backup_share

The network or UNC path to the backup directory.

backup_retention_period

The length of time, in minutes, that a log backup file is retained in the backup directory before being deleted.

backup_compression

Indicates 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 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_id

The Microsoft SQL Server Agent job ID associated with the backup job on the primary server.

monitor_server

The name of the instance of the SQL Server Database Engine being used as a monitor server in the log shipping configuration.

monitor_server_security_mode

The security mode used to connect to the monitor server.

1 = Microsoft Windows Authentication.

0 = SQL Server Authentication.

backup_threshold

The number of minutes allowed to elapse between backup operations before an alert is generated.

threshold_alert

The alert to be raised when the backup threshold is exceeded.

threshold_alert_enabled

Determines if backup threshold alerts are enabled.

1 = Enabled.

0 = Disabled.

last_backup_file

The absolute path of the most recent transaction log backup.

last_backup_date

The time and date of the last log backup operation.

last_backup_date_utc

The time and date of the last transaction log backup operation on the primary database, expressed in Coordinated Universal Time.

history_retention_period

The 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 AdventureWorks.

master.dbo.sp_help_log_shipping_primary_database @database=N'AdventureWorks'
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft