Get-DbaDbBackupHistory

Synopsis

Retrieves backup history records from MSDB for analysis and compliance reporting.

Description

Queries the MSDB database backup tables to extract detailed backup history information including file paths, sizes, compression ratios, and LSN sequences. Essential for compliance auditing, disaster recovery planning, and troubleshooting backup issues without having to manually query system tables. The function automatically groups striped backup sets into single objects and excludes copy-only backups by default, making the output more practical for restoration scenarios. You can filter results by database name, backup type, date range, or retrieve only the most recent backup chains needed for point-in-time recovery.

Reference: http://www.sqlhub.com/2011/07/find-your-backup-history-in-sql-server.html

Syntax

Get-DbaDbBackupHistory -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-IncludeCopyOnly]
    [-Since <PSObject>]
    [-RecoveryFork <String>]
    [-Last]
    [-LastFull]
    [-LastDiff]
    [-LastLog]
    [-DeviceType <String[]>]
    [-Raw]
    [-LastLsn <BigInteger>]
    [-IncludeMirror]
    [-Type <String[]>]
    [-AgCheck]
    [-IgnoreDiffBackup]
    [-LsnSort <String>]
    [-EnableException]
    [<CommonParameters>]

Get-DbaDbBackupHistory -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-IncludeCopyOnly]
    [-Force]
    [-Since <PSObject>]
    [-RecoveryFork <String>]
    [-Last]
    [-LastFull]
    [-LastDiff]
    [-LastLog]
    [-DeviceType <String[]>]
    [-Raw]
    [-LastLsn <BigInteger>]
    [-IncludeMirror]
    [-Type <String[]>]
    [-AgCheck]
    [-IgnoreDiffBackup]
    [-LsnSort <String>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbBackupHistory -SqlInstance SqlInstance2014a

Returns server name, database, username, backup type, date for all database backups still in msdb history on SqlInstance2014a. This may return many rows; consider using filters that are included in
other examples.

Example: 2

PS C:\> $cred = Get-Credential sqladmin

Get-DbaDbBackupHistory -SqlInstance SqlInstance2014a -SqlCredential $cred
Does the same as above but connect to SqlInstance2014a as SQL user “sqladmin”

Example: 3

PS C:\> Get-DbaDbBackupHistory -SqlInstance SqlInstance2014a -Database db1, db2 -Since ([DateTime]'2016-07-01 10:47:00')

Returns backup information only for databases db1 and db2 on SqlInstance2014a since July 1, 2016 at 10:47 AM.

Example: 4

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014, pubs -Force | Format-Table

Returns information only for AdventureWorks2014 and pubs and formats the results as a table.

Example: 5

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last

Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014.

Example: 6

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last -DeviceType Disk

Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014, but only for backups to disk.

Example: 7

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last -DeviceType 148,107

Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014, but only for backups with device_type 148 and 107.

Example: 8

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -LastFull

Returns information about the most recent full backup for AdventureWorks2014 on sql2014.

Example: 9

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Type Full

Returns information about all Full backups for AdventureWorks2014 on sql2014.

Example: 10

PS C:\> Get-DbaRegServer -SqlInstance sql2016 | Get-DbaDbBackupHistory

Returns database backup information for every database on every server listed in the Central Management Server on sql2016.

Example: 11

PS C:\> Get-DbaDbBackupHistory -SqlInstance SqlInstance2014a, sql2016 -Force

Returns detailed backup history for all databases on SqlInstance2014a and sql2016.

Example: 12

PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2016 -Database db1 -RecoveryFork 38e5e84a-3557-4643-a5d5-eed607bef9c6 -Last

If db1 has multiple recovery forks, specifying the RecoveryFork GUID will restrict the search to that fork.

Required Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.

Optional Parameters

-SqlCredential

Credential object used to connect to the SQL Server instance as a different user. This can be a Windows or SQL Server account. Windows users are determined by the existence of a backslash, so if you
are intending to use an alternative Windows connection instead of a SQL login, ensure it contains a backslash.

-Database

Specifies one or more databases to include in the backup history search. Accepts wildcards for pattern matching.
Use this when you need backup history for specific databases rather than all databases on the instance.

-ExcludeDatabase

Specifies one or more databases to exclude from the backup history search.
Useful when you want history for most databases but need to skip system databases or specific user databases.

-IncludeCopyOnly

Includes copy-only backups in the results, which are normally excluded by default.
Copy-only backups don’t break the backup chain and are commonly used for ad-hoc backups or moving databases to other environments.

-Force

Returns all columns from the MSDB backup tables instead of the filtered standard output.
Use this when you need access to additional backup metadata fields for detailed analysis or troubleshooting.

-Since

Filters backup history to only include backups taken after the specified date and time.
Accepts DateTime objects or TimeSpan objects (which get added to the current time). Times are compared using the SQL Server instance’s timezone.
Essential for limiting results when dealing with databases that have extensive backup history.

-RecoveryFork

Filters results to a specific recovery fork GUID when a database has multiple recovery paths.
Use this when a database has been restored from different backup chains or has experienced recovery fork scenarios, ensuring you get the correct backup sequence.

-Last

Returns the most recent complete backup chain (full, differential, and log backups) needed for point-in-time recovery.
This provides the exact backup sequence you’d need to restore a database to its most current state.

-LastFull

Returns only the most recent full backup for each database.
Use this to quickly identify the base backup needed for restore operations or to verify when the last full backup was taken.

-LastDiff

Returns only the most recent differential backup for each database.
Useful for verifying differential backup schedules or identifying the latest differential backup in a restore scenario.

-LastLog

Returns only the most recent transaction log backup for each database.
Critical for monitoring log backup frequency and identifying the latest point-in-time recovery option available.

-DeviceType

Filters backups by device type such as ‘Disk’, ‘Tape’, ‘URL’, or ‘Virtual Device’.
Use this to find backups stored on specific media types, particularly useful when backups go to different destinations like local disk vs cloud storage.

-Raw

Returns one object per backup file instead of grouping striped backup sets into single objects.
Use this when you need to see individual backup file details for striped backups or need to analyze backup file distribution.

-LastLsn

Filters to only include backups with LSNs greater than the specified value, improving query performance on large backup histories.
Use this when you know the LSN range you’re interested in, typically when building restore sequences or analyzing backup chains.

-IncludeMirror

Includes mirror backup copies in the results, which are excluded by default.
Use this when you need to see all backup copies created through backup mirroring, useful for verifying mirror backup configurations.

-Type

Filters results to specific backup types: ‘Full’, ‘Log’, ‘Differential’, ‘File’, ‘Differential File’, ‘Partial Full’, or ‘Partial Differential’.
Use this to focus on particular backup types when analyzing backup strategies or troubleshooting specific backup issues.

-AgCheck

Deprecated parameter. Use Get-DbaAgBackupHistory instead to retrieve backup history from all replicas in an Availability Group.
This parameter is maintained for backward compatibility but no longer functions.

-IgnoreDiffBackup

Excludes differential backups from the results, showing only full and log backups.
Useful when analyzing backup chains that don’t use differential backups or when you want to focus on full and log backup patterns.

-LsnSort

Determines which LSN column to use for sorting results: ‘FirstLsn’, ‘DatabaseBackupLsn’, or ‘LastLsn’ (default).
Use this to control backup ordering when working with complex backup scenarios or when you need results sorted by specific LSN checkpoints.

-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

System.Data.DataRow (when -Raw is specified)

Returns one DataRow per backup file from the MSDB backup tables, preserving the raw SQL Server structure. This is useful when you need to analyze individual backup files in a striped backup set or require access to all MSDB backup columns without object wrapping.

Additional property added to raw output:

• FullName: Copy of the Path property for consistency with grouped output Dataplat.Dbatools.Database.BackupHistory (default) Returns one BackupHistory object per backup set, with striped backups automatically grouped into a single object. The standard output is more practical for restore planning and backup analysis.

Default display properties (via table format):

• SqlInstance: The SQL Server instance name
• Database: Database name
• Type: Backup type (Full, Log, Differential, File, Differential File, Partial Full, or Partial Differential)
• TotalSize: Total uncompressed backup size in bytes
• DeviceType: Storage device type (Disk, Tape, URL, Virtual Device, etc.)
• Start: Backup start date and time
• Duration: Duration of the backup operation
• End: Backup completion date and time

All available properties on BackupHistory objects:

• ComputerName: Computer name where SQL Server instance resides
• InstanceName: SQL Server instance name
• SqlInstance: Full SQL Server instance name (ComputerName\InstanceName)
• AvailabilityGroupName: Availability group name (if applicable)
• Database: Database name
• DatabaseId: SQL Server database ID
• UserName: Windows or SQL login that performed the backup
• Start: Backup start DateTime
• End: Backup completion DateTime
• Duration: TimeSpan duration of the backup
• Path: Array of file paths for the backup files
• TotalSize: Total uncompressed backup size in bytes
• CompressedBackupSize: Compressed backup size in bytes (NULL for SQL 2005)
• CompressionRatio: Ratio of total size to compressed size (1.0 for uncompressed)
• Type: Backup type string
• BackupSetId: Unique backup set identifier from MSDB
• DeviceType: Backup device type (Disk, Tape, Pipe, Virtual Device, URL, etc.)
• Software: Software that created the backup (e.g., “Microsoft SQL Server”)
• FullName: Array of backup file paths (same as Path)
• FileList: Array of file objects with FileType, LogicalName, and PhysicalName properties
• Position: Position of this backup in the media set
• FirstLsn: First Log Sequence Number in the backup (string representation of binary(10))
• DatabaseBackupLsn: LSN of the last database backup referenced by this backup
• CheckpointLsn: LSN of the checkpoint at the time the backup was created
• LastLsn: Last Log Sequence Number in the backup
• SoftwareVersionMajor: Major version number of SQL Server that created the backup
• IsCopyOnly: Boolean indicating if this is a copy-only backup
• LastRecoveryForkGUID: Unique identifier of the last recovery fork
• RecoveryModel: Database recovery model (Simple, Full, or Bulk-logged)
• EncryptorThumbprint: Thumbprint of the certificate used for backup encryption (SQL 2014+)
• EncryptorType: Encryption algorithm used (SQL 2014+)
• KeyAlgorithm: Key algorithm used for encryption (SQL 2014+)