Test-DbaDbLogShipStatus

Synopsis

Retrieves log shipping status and health information from the monitoring instance

Description

Queries the log shipping monitoring system to check the health of your log shipping configuration across primary and secondary instances.
This function connects to your log shipping monitoring instance and examines backup, copy, and restore operations to identify any issues or delays.

Make sure you’re connecting to the monitoring instance of your log shipping infrastructure, as this is where SQL Server stores the consolidated monitoring data.

The function analyzes timing thresholds for each operation and reports specific problems like missed backups, copy delays, or restore failures.
When everything is functioning normally, you’ll see “All OK” in the status output.
Problem databases will show detailed messages about which operations are behind schedule or failing entirely.

Syntax

Test-DbaDbLogShipStatus
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [-Simple]
    [-Primary]
    [-Secondary]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaDbLogShipStatus -SqlInstance sql1

Retrieves the log ship information from sql1 and displays all the information present including the status.

Example: 2

PS C:\> Test-DbaDbLogShipStatus -SqlInstance sql1 -Database AdventureWorks2014

Retrieves the log ship information for just the database AdventureWorks.

Example: 3

PS C:\> Test-DbaDbLogShipStatus -SqlInstance sql1 -Primary

Retrieves the log ship information and only returns the information for the databases on the primary instance.

Example: 4

PS C:\> Test-DbaDbLogShipStatus -SqlInstance sql1 -Secondary

Retrieves the log ship information and only returns the information for the databases on the secondary instance.

Example: 5

PS C:\> Test-DbaDbLogShipStatus -SqlInstance sql1 -Simple

Retrieves the log ship information and only returns the columns SQL Instance, Database, Instance Type and Status

Required Parameters

-SqlInstance

The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL Server version 2000 or greater.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which log shipped databases to check by exact name. Accepts multiple database names as a comma-separated list.
Use this when you want to focus on specific databases instead of checking all log shipped databases on the monitoring instance.

-ExcludeDatabase

Excludes specific log shipped databases from the status check by exact name. Accepts multiple database names as a comma-separated list.
Use this when you want to check most databases but skip certain ones, such as test or development log shipping configurations.

-Simple

Returns only essential columns: SqlInstance, Database, InstanceType, and Status instead of all detailed timing information.
Use this for quick health overviews when you don’t need the full backup/copy/restore timing details.

-Primary

Returns only status information for databases acting as primary instances in log shipping configurations.
Use this when you want to focus specifically on backup operations and primary-side health monitoring.

-Secondary

Returns only status information for databases acting as secondary instances in log shipping configurations.
Use this when you want to focus specifically on copy and restore operations and secondary-side health monitoring.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

Outputs

PSCustomObject

Returns one object per log-shipped database configured on the monitoring instance. Output structure varies based on the -Simple parameter.

Default output (without -Simple) includes all properties:

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance)
• Database: The name of the log-shipped database
• InstanceType: The role of the instance - either “Primary Instance” (backup/copy operations) or “Secondary Instance” (restore operations)
• TimeSinceLastBackup: DateTime of the most recent backup operation (or “N/A” if never executed)
• LastBackupFile: The name of the file involved in the last backup operation
• BackupThreshold: The configured threshold in minutes for maximum acceptable time between backups
• IsBackupAlertEnabled: Boolean indicating whether alerting is enabled for backup operation failures
• TimeSinceLastCopy: DateTime of the most recent copy operation (or “N/A” if never executed); only relevant on secondary instances
• LastCopiedFile: The name of the file involved in the last copy operation
• TimeSinceLastRestore: DateTime of the most recent restore operation (or “N/A” if never executed); only relevant on secondary instances
• LastRestoredFile: The name of the file involved in the last restore operation
• LastRestoredLatency: The delay in seconds between the backup and restore operations (latency between primary and secondary)
• RestoreThreshold: The configured threshold in minutes for maximum acceptable time between restore operations
• IsRestoreAlertEnabled: Boolean indicating whether alerting is enabled for restore operation failures
• Status: String containing operational status - “All OK” for healthy operations or detailed error message(s) describing any issues (e.g., “The backup has not been executed in the last 60 minutes”)

When -Simple is specified:

• Only four properties are returned: SqlInstance, Database, InstanceType, and Status
• This provides a quick health overview without detailed timing information

When -Primary is specified:

• Only databases acting as primary instances are returned; backup-related properties are populated
• Copy and restore properties may be empty or N/A since those operations occur on secondaries

When -Secondary is specified:

• Only databases acting as secondary instances are returned; copy and restore properties are populated
• Backup properties may be empty or N/A since backup operations occur on primaries