Get-BackupHistory

DBSystems: Gets backup history information for databases

Script

#Requires -Version 5.0
#Requires -Modules SQLServer

[CmdLetBinding()]
Param(
    [Parameter(Mandatory = $true)]   
    [string]$ServerInstance,    
    [pscredential]$ServerCredential,
    [ValidateSet('Database', 'Differential', 'Incremental', 'Log', 'FileOrFileGroup', 'FileOrFileGroupDifferential')]
    [string]$BackupType,
    [string]$DBName,
    [datetime]$EndTime,
    [datetime]$StartTime,
    [switch]$IgnoreProviderContext,
    [switch]$IncludeSnapshotBackups,
    [switch]$SuppressProviderContextWarning,
    [ValidateSet('Midnight', 'Yesterday', 'LastWeek', 'LastMonth')]
    [string]$Since,
    [int]$ConnectionTimeout = 30,
    [ValidateSet('*','DatabaseName','BackupSetId','BackupStartDate','BackupFinishDate','BackupSize','Type','UserName','ServerName','Description')]
    [string[]]$Properties = @('DatabaseName','BackupStartDate','BackupFinishDate','BackupSize','Type','UserName')
)

function Get-SqlServerInstanceInternal {
    [CmdLetBinding()]
    Param(
        [Parameter(Mandatory = $true)]   
        [string]$ServerInstance,    
        [pscredential]$ServerCredential,
        [int]$ConnectionTimeout = 30
    )
    try {
        [hashtable]$cmdArgs = @{
            'ErrorAction' = 'Stop'
            'Confirm' = $false
            'ServerInstance' = $ServerInstance
            'ConnectionTimeout' = $ConnectionTimeout
        }
        if ($null -ne $ServerCredential) {
            $cmdArgs.Add('Credential', $ServerCredential)
        }
        return Get-SqlInstance @cmdArgs
    } catch {
        throw
    }
}

Import-Module SQLServer

try {
    if ($Properties -contains '*') {
        $Properties = @('*')
    }
    $instance = Get-SqlServerInstanceInternal -ServerInstance $ServerInstance -ServerCredential $ServerCredential -ConnectionTimeout $ConnectionTimeout

    [hashtable]$cmdArgs = @{
        'ErrorAction' = 'Stop'
        'InputObject' = $instance
        'IgnoreProviderContext' = $IgnoreProviderContext
        'IncludeSnapshotBackups' = $IncludeSnapshotBackups
        'SuppressProviderContextWarning' = $SuppressProviderContextWarning
    }
    
    if (-not [string]::IsNullOrWhiteSpace($DBName)) {
        $cmdArgs.Add("DatabaseName", $DBName)
    }
    if (-not [string]::IsNullOrWhiteSpace($BackupType)) {
        $cmdArgs.Add("BackupType", $BackupType)
    }
    if ($null -ne $EndTime) {
        $cmdArgs.Add('EndTime', $EndTime)
    }
    if ($null -ne $StartTime) {
        $cmdArgs.Add('StartTime', $StartTime)
    }
    if ($null -eq $StartTime -and -not [string]::IsNullOrWhiteSpace($Since)) {
        $cmdArgs.Add("Since", $Since)
    }
    
    $result = Get-SqlBackupHistory @cmdArgs | Select-Object $Properties
    Write-Output $result
} catch {
    throw
}

Customize

ServerInstance string

Specifies the name of the target computer including the instance name, e.g. MyServer\Instance

ServerCredential pscredential

Specifies a PSCredential object for the connection to the SQL Server. ServerCredential is ONLY used for SQL Logins. When you are using Windows Authentication you don't specify -Credential. It is picked up from your current login.

BackupType string

The type of backup to filter on. If not specified then gets all backup types

DBName string

The names of the databases whose backup records are to be retrieved, comma separated

EndTime datetime

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

StartTime datetime

Gets the backup records which started after this specified time

IgnoreProviderContext switch

Off

Indicates that this cmdlet does not use the current context to override the values of the ServerInstance, DatabaseName parameters

IncludeSnapshotBackups switch

Off

This switch will make the cmdlet obtain records for snapshot backups as well

SuppressProviderContextWarning switch

Off

Since string

Specifies an abbreviation for the StartTime parameter

ConnectionTimeout int

Specifies the time period to retry the command on the target server

Properties string

List of properties to expand, comma separated e.g. Name,BackupFinishDate. Use * for all properties