| Author | Chrissy LeMaire (@cl) , Stuart Moore (@napalmgram) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Get-DbaDbBackupHistory on GitHub.
Want to see the Bill Of Health for this command? Check out Get-DbaDbBackupHistory.
Retrieves backup history records from MSDB for analysis and compliance reporting.
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
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>]
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.
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"
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.
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.
PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last
Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014.
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.
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.
PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -LastFull
Returns information about the most recent full backup for AdventureWorks2014 on sql2014.
PS C:\> Get-DbaDbBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Type Full
Returns information about all Full backups for AdventureWorks2014 on sql2014.
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.
PS C:\> Get-DbaDbBackupHistory -SqlInstance SqlInstance2014a, sql2016 -Force
Returns detailed backup history for all databases on SqlInstance2014a and sql2016.
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.
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.
| Alias | |
| Required | True |
| Pipeline | true (ByValue) |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | ([DateTime]::ParseExact("1970-01-01", "yyyy-MM-dd", [System.Globalization.CultureInfo]::InvariantCulture)) |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Full,Log,Differential,File,Differential File,Partial Full,Partial Differential |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | LastLsn |
| Accepted Values | FirstLsn,DatabaseBackupLsn,LastLsn |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |