Share via

Get-SqlBackupHistory

  • Reference
Module:
SQLServer

Gets backup information about databases and returns SMO BackupSet objects for each Backup record found based on the parameters specified to this cmdlet.

Syntax

Get-SqlBackupHistory
   [-Since <SinceType>]
   [-StartTime <DateTime>]
   [-EndTime <DateTime>]
   [-BackupType <BackupSetType>]
   [-IncludeSnapshotBackups]
   [-TimeSpan <TimeSpan>]
   [-IgnoreProviderContext]
   [-SuppressProviderContextWarning]
   [[-Path] <String[]>]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [-DatabaseName <System.Collections.Generic.List`1[System.String]>]
   [<CommonParameters>]
Get-SqlBackupHistory
   [-Since <SinceType>]
   [-StartTime <DateTime>]
   [-EndTime <DateTime>]
   [-BackupType <BackupSetType>]
   [-IncludeSnapshotBackups]
   [-TimeSpan <TimeSpan>]
   [-IgnoreProviderContext]
   [-SuppressProviderContextWarning]
   [[-ServerInstance] <String[]>]
   [-Credential <PSCredential>]
   [-ConnectionTimeout <Int32>]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [-DatabaseName <System.Collections.Generic.List`1[System.String]>]
   [<CommonParameters>]
Get-SqlBackupHistory
   [-Since <SinceType>]
   [-StartTime <DateTime>]
   [-EndTime <DateTime>]
   [-BackupType <BackupSetType>]
   [-IncludeSnapshotBackups]
   [-TimeSpan <TimeSpan>]
   [-IgnoreProviderContext]
   [-SuppressProviderContextWarning]
   [-InputObject] <Server[]>
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [-DatabaseName <System.Collections.Generic.List`1[System.String]>]
   [<CommonParameters>]

Description

This cmdlet searches the server instance and gets the backup records as SMO BackupSet objects for all the records found matching the specified parameters.

Examples

Example 1

PS C:\> Get-SqlBackupHistory -ServerInstance test-server -DatabaseName AdventureWorks2014

Name                                     Type               Backup Start Date         Backup Finish Date
----                                     ----               -----------------         ------------------
AdventureWorks2014-Full Database Backup  Database           2/21/2017 7:52:39 PM      2/21/2017 7:52:40 PM
AdventureWorks2014-Full Database Backup  Database           3/20/2017 2:41:39 PM      3/20/2017 2:41:39 PM
AdventureWorks2014-Full-2017-07-24T22:22 Database           7/24/2017 3:23:11 PM      7/24/2017 3:23:11 PM

This example gets records for all backup types for AdventureWorks2014 database on test-server SQL instance.

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:PSObject
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-BackupType

The type of backup to filter on. If not specified then gets all backup types. Accepted values are defined below.

Type:BackupSetType
Accepted values:Database, Differential, Incremental, Log, FileOrFileGroup, FileOrFileGroupDifferential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ConnectionTimeout

The time to wait in seconds for a connection to be established and the dynamically generated -DatabaseName parameter to be populated.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Credential

The PSCredential object whose username and password fields are used to connect to the SQL instance.

Type:PSCredential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-DatabaseName

The names of the databases whose backup records are to be retrieved. This is a dynamically populated field and so provides auto-complete suggestions on database names.

Type:System.Collections.Generic.List`1[System.String]
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-EndTime

The time before which all backup records to be retrieved should have completed.

Type:DateTime
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-IgnoreProviderContext

Indicates that this cmdlet does not use the current context to override the values of the ServerInstance, DatabaseName parameters. If you do not specify this parameter, the cmdlet ignores the values of these parameters, if possible, in favor of the context in which you run the cmdlet.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-IncludeSnapshotBackups

This switch will make the cmdlet obtain records for snapshot backups as well. By default such backups are not retrieved.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies SMO Server objects to get the backup records for.

Type:Server[]
Position:1
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-Path

Specifies the SQL provider path to either a server instance or a database for this cmdlet to use to obtain BackupSets for. If not specified uses the current working location.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:ActionPreference
Aliases:proga
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ServerInstance

The name of the server instances which this cmdlet will target.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-Since

Specifies an abbreviation that you can instead of the StartTime parameter.

It can be specified with the EndTime parameter.

You cannot use the StartTime parameter, if you use this parameter.

The acceptable values for this parameter are:

  • Midnight (gets all the job history information generated after midnight)
  • Yesterday (gets all the job history information generated in the last 24 hours)
  • LastWeek (gets all the job history information generated in the last week)
  • LastMonth (gets all the job history information generated in the last month)
Type:SinceType
Accepted values:Midnight, Yesterday, LastWeek, LastMonth
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-StartTime

Gets the backup records which started after this specified time.

Type:DateTime
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-SuppressProviderContextWarning

Suppresses the warning when the cmdlet is using the provider context.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-TimeSpan

If specified, it causes the cmdlet to filter records generated more than 'Timespan' ago.

Type:TimeSpan
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

System.String[]

Microsoft.SqlServer.Management.Smo.Server[]

Outputs

System.Object