commands

Clear

Get-DbaAgBackupHistory


Author	Chrissy LeMaire (@cl) , Stuart Moore (@napalmgram), Andreas Jordan
Availability	Windows, Linux, macOS

Want to see the source code for this command? Check out Get-DbaAgBackupHistory on GitHub.
Want to see the Bill Of Health for this command? Check out Get-DbaAgBackupHistory.

Synopsis

Returns backup history details for databases on a SQL Server Availability Group.

Description

Returns backup history details for some or all databases on a SQL Server Availability Group.

You can even get detailed information (including file path) for latest full, differential and log files. For detailed examples of the various parameters see the documentation of Get-DbaDbBackupHistory.

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

Syntax

Get-DbaAgBackupHistory -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>] -AvailabilityGroup <String>
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-IncludeCopyOnly]
    [-Since <DateTime>]
    [-RecoveryFork <String>]
    [-Last]
    [-LastFull]
    [-LastDiff]
    [-LastLog]
    [-DeviceType <String[]>]
    [-Raw]
    [-LastLsn <BigInteger>]
    [-IncludeMirror]
    [-Type <String[]>]
    [-LsnSort <String>]
    [-EnableException]
    [<CommonParameters>]

Get-DbaAgBackupHistory -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>] -AvailabilityGroup <String>
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-IncludeCopyOnly]
    [-Force]
    [-Since <DateTime>]
    [-RecoveryFork <String>]
    [-Last]
    [-LastFull]
    [-LastDiff]
    [-LastLog]
    [-DeviceType <String[]>]
    [-Raw]
    [-LastLsn <BigInteger>]
    [-IncludeMirror]
    [-Type <String[]>]
    [-LsnSort <String>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaAgBackupHistory -SqlInstance AgListener -AvailabilityGroup AgTest1

Returns information for all database backups still in msdb history on all replicas of availability group AgTest1 using the listener AgListener to determine all replicas.

Example: 2

PS C:\> Get-DbaAgBackupHistory -SqlInstance Replica1, Replica2, Replica3 -AvailabilityGroup AgTest1

Returns information for all database backups still in msdb history on the given replicas of availability group AgTest1.

Example: 3

PS C:\> Get-DbaAgBackupHistory -SqlInstance 'Replica1:14331', 'Replica2:14332', 'Replica3:14333' -AvailabilityGroup AgTest1

Returns information for all database backups still in msdb history on the given replicas of availability group AgTest1 using custom ports.

Example: 4

PS C:\> $ListOfReplicas | Get-DbaAgBackupHistory -AvailabilityGroup AgTest1

Returns information for all database backups still in msdb history on the replicas in $ListOfReplicas of availability group AgTest1.

Example: 5

PS C:\> $serverWithAllAgs = Connect-DbaInstance -SqlInstance MyServer
PS C:\> $allAgResults = foreach ( $ag in $serverWithAllAgs.AvailabilityGroups ) {
>>     Get-DbaAgBackupHistory -SqlInstance $ag.AvailabilityReplicas.Name -AvailabilityGroup $ag.Name
>> }
>>
PS C:\> $allAgResults | Format-Table

Returns information for all database backups on all replicas for all availability groups on SQL instance MyServer.

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. If you pass in one availability group listener, all replicas are automatically determined and queried. If you pass in a list of individual replicas, they will be queried. This enables you to use custom ports for the replicas.


Alias
Required	True
Pipeline	true (ByValue)
Default Value

-AvailabilityGroup

Specify the availability group to process.


Alias
Required	True
Pipeline	false
Default Value

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.


Alias
Required	False
Pipeline	false
Default Value

-Database

Specifies one or more database(s) to process. If unspecified, all databases of the availability group will be processed.


Alias
Required	False
Pipeline	false
Default Value

-ExcludeDatabase

Specifies one or more database(s) to exclude from processing.


Alias
Required	False
Pipeline	false
Default Value

-IncludeCopyOnly

By default Get-DbaAgBackupHistory will ignore backups taken with the CopyOnly option. This switch will include them.


Alias
Required	False
Pipeline	false
Default Value	False

-Force

If this switch is enabled, a large amount of information is returned, similar to what SQL Server itself returns.


Alias
Required	False
Pipeline	false
Default Value	False

-Since

Specifies a DateTime object to use as the starting point for the search for backups.


Alias
Required	False
Pipeline	false
Default Value	(Get-Date '01/01/1970')

-RecoveryFork

Specifies the Recovery Fork you want backup history for


Alias
Required	False
Pipeline	false
Default Value

-Last

If this switch is enabled, the most recent full chain of full, diff and log backup sets is returned.


Alias
Required	False
Pipeline	false
Default Value	False

-LastFull

If this switch is enabled, the most recent full backup set is returned.


Alias
Required	False
Pipeline	false
Default Value	False

-LastDiff

If this switch is enabled, the most recent differential backup set is returned.


Alias
Required	False
Pipeline	false
Default Value	False

-LastLog

If this switch is enabled, the most recent log backup is returned.


Alias
Required	False
Pipeline	false
Default Value	False

-DeviceType

Specifies a filter for backup sets based on DeviceType. Valid options are 'Disk','Permanent Disk Device', 'Tape', 'Permanent Tape Device','Pipe','Permanent Pipe Device','Virtual Device','URL', in addition to custom integers for your own DeviceType.


Alias
Required	False
Pipeline	false
Default Value

-Raw

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.


Alias
Required	False
Pipeline	false
Default Value	False

-LastLsn

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.


Alias
Required	False
Pipeline	false
Default Value

-IncludeMirror

By default mirrors of backups are not returned, this switch will cause them to be returned


Alias
Required	False
Pipeline	false
Default Value	False

-Type

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.


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Full,Log,Differential,File,Differential File,Partial Full,Partial Differential

-LsnSort

Specifies which of the returned LSN values you would like to use for sorting when using the LastFull, LastDiff and LastLog parameters.


Alias
Required	False
Pipeline	false
Default Value	FirstLsn
Accepted Values	FirstLsn,DatabaseBackupLsn,LastLsn

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


Alias
Required	False
Pipeline	false
Default Value	False