< Back
Get-DbaDbBackupHistory
Post
NAME Get-DbaDbBackupHistory
SYNOPSIS
Returns backup history details for databases on a SQL Server.
SYNTAX
Get-DbaDbBackupHistory -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-IncludeCopyOnly <Switch>]
[-Since <Datetime>] [-RecoveryFork <String>] [-DeviceType <System.String[]>] [-Raw <Switch>] [-LastLsn <Bigint>]
[-IncludeMirror <Switch>] [-Type <System.String[]>] [-EnableException <Switch>] [<CommonParameters>]
Get-DbaDbBackupHistory -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-IncludeCopyOnly <Switch>]
[-Force <Switch>] [-Since <Datetime>] [-RecoveryFork <String>] [-DeviceType <System.String[]>] [-Raw <Switch>]
[-LastLsn <Bigint>] [-IncludeMirror <Switch>] [-Type <System.String[]>] [-EnableException <Switch>]
[<CommonParameters>]
Get-DbaDbBackupHistory -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-IncludeCopyOnly <Switch>]
[-Since <Datetime>] [-RecoveryFork <String>] [-Last <Switch>] [-LastFull <Switch>] [-LastDiff <Switch>] [-LastLog
<Switch>] [-DeviceType <System.String[]>] [-Raw <Switch>] [-LastLsn <Bigint>] [-IncludeMirror <Switch>] [-Type
<System.String[]>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Returns backup history details for some or all databases on a SQL Server.
You can even get detailed information (including file path) for latest full, differential and log files.
Backups taken with the CopyOnly option will NOT be returned, unless the IncludeCopyOnly switch is present
Reference: http://www.sqlhub.com/2011/07/find-your ... erver.html
PARAMETERS
-Database [<System.Object[]>]
Specifies one or more database(s) to process. If unspecified, all databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DeviceType [<System.String[]>]
Specifies a filter for backup sets based on DeviceTypes. Valid options are 'Disk','Permanent Disk Device',
'Tape', 'Permanent Tape Device','Pipe','Permanent Pipe Device','Virtual Device', in addition to custom
integers for your own DeviceTypes.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Specifies one or more database(s) to exclude from processing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Force [<Switch>]
If this switch is enabled, a large amount of information is returned, similar to what SQL Server itself
returns.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeCopyOnly [<Switch>]
By default Get-DbaDbBackupHistory will ignore backups taken with the CopyOnly option. This switch will include
them
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeMirror [<Switch>]
By default mirrors of backups are not returned, this switch will cause them to be returned
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Last [<Switch>]
If this switch is enabled, the most recent full chain of full, diff and log backup sets is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastDiff [<Switch>]
If this switch is enabled, the most recent differential backup set is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastFull [<Switch>]
If this switch is enabled, the most recent full backup set is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastLog [<Switch>]
If this switch is enabled, the most recent log backup is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastLsn [<Bigint>]
Specifies a minimum LSN to use in filtering backup history. Only backups with an LSN greater than this value
will be returned, which helps speed the retrieval process.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Raw [<Switch>]
If this switch is enabled, one object per backup file is returned. Otherwise, media sets (striped backups
across multiple files) will be grouped into a single return object.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RecoveryFork [<String>]
Specifies the Recovery Fork you want backup history for
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Since [<Datetime>]
Specifies a DateTime object to use as the starting point for the search for backups.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<System.String[]>]
Specifies one or more types of backups to return. Valid options are 'Full', 'Log', 'Differential', 'File',
'Differential File', 'Partial Full', and 'Partial Differential'. Otherwise, all types of backups will be
returned unless one of the -Last* switches is enabled.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: DisasterRecovery, Backup
Author: Chrissy LeMaire (@cl) | Stuart Moore (@napalmgram)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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 '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.
RELATED LINKS
https://dbatools.io/ Get-DbaDbBackupHistory
SYNOPSIS
Returns backup history details for databases on a SQL Server.
SYNTAX
Get-DbaDbBackupHistory -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-IncludeCopyOnly <Switch>]
[-Since <Datetime>] [-RecoveryFork <String>] [-DeviceType <System.String[]>] [-Raw <Switch>] [-LastLsn <Bigint>]
[-IncludeMirror <Switch>] [-Type <System.String[]>] [-EnableException <Switch>] [<CommonParameters>]
Get-DbaDbBackupHistory -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-IncludeCopyOnly <Switch>]
[-Force <Switch>] [-Since <Datetime>] [-RecoveryFork <String>] [-DeviceType <System.String[]>] [-Raw <Switch>]
[-LastLsn <Bigint>] [-IncludeMirror <Switch>] [-Type <System.String[]>] [-EnableException <Switch>]
[<CommonParameters>]
Get-DbaDbBackupHistory -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-IncludeCopyOnly <Switch>]
[-Since <Datetime>] [-RecoveryFork <String>] [-Last <Switch>] [-LastFull <Switch>] [-LastDiff <Switch>] [-LastLog
<Switch>] [-DeviceType <System.String[]>] [-Raw <Switch>] [-LastLsn <Bigint>] [-IncludeMirror <Switch>] [-Type
<System.String[]>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Returns backup history details for some or all databases on a SQL Server.
You can even get detailed information (including file path) for latest full, differential and log files.
Backups taken with the CopyOnly option will NOT be returned, unless the IncludeCopyOnly switch is present
Reference: http://www.sqlhub.com/2011/07/find-your ... erver.html
PARAMETERS
-Database [<System.Object[]>]
Specifies one or more database(s) to process. If unspecified, all databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DeviceType [<System.String[]>]
Specifies a filter for backup sets based on DeviceTypes. Valid options are 'Disk','Permanent Disk Device',
'Tape', 'Permanent Tape Device','Pipe','Permanent Pipe Device','Virtual Device', in addition to custom
integers for your own DeviceTypes.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Specifies one or more database(s) to exclude from processing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Force [<Switch>]
If this switch is enabled, a large amount of information is returned, similar to what SQL Server itself
returns.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeCopyOnly [<Switch>]
By default Get-DbaDbBackupHistory will ignore backups taken with the CopyOnly option. This switch will include
them
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeMirror [<Switch>]
By default mirrors of backups are not returned, this switch will cause them to be returned
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Last [<Switch>]
If this switch is enabled, the most recent full chain of full, diff and log backup sets is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastDiff [<Switch>]
If this switch is enabled, the most recent differential backup set is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastFull [<Switch>]
If this switch is enabled, the most recent full backup set is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastLog [<Switch>]
If this switch is enabled, the most recent log backup is returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LastLsn [<Bigint>]
Specifies a minimum LSN to use in filtering backup history. Only backups with an LSN greater than this value
will be returned, which helps speed the retrieval process.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Raw [<Switch>]
If this switch is enabled, one object per backup file is returned. Otherwise, media sets (striped backups
across multiple files) will be grouped into a single return object.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RecoveryFork [<String>]
Specifies the Recovery Fork you want backup history for
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Since [<Datetime>]
Specifies a DateTime object to use as the starting point for the search for backups.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<System.String[]>]
Specifies one or more types of backups to return. Valid options are 'Full', 'Log', 'Differential', 'File',
'Differential File', 'Partial Full', and 'Partial Differential'. Otherwise, all types of backups will be
returned unless one of the -Last* switches is enabled.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: DisasterRecovery, Backup
Author: Chrissy LeMaire (@cl) | Stuart Moore (@napalmgram)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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 '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.
RELATED LINKS
https://dbatools.io/ Get-DbaDbBackupHistory