functions/Get-DbaLastBackup.ps1
|
Function Get-DbaLastBackup { <# .SYNOPSIS Get date/time for last known backups .DESCRIPTION Retrieves and compares the date/time for the last known backups, as well as the creation date/time for the database. Default output includes columns Server, Database, RecoveryModel, LastFullBackup, LastDiffBackup, LastLogBackup, SinceFull, SinceDiff, SinceLog, Status, DatabaseCreated, DaysSinceDbCreated. .PARAMETER SqlServer The SQL Server that you're connecting to. .PARAMETER Credential Credential object used to connect to the SQL Server as a different user .PARAMETER Databases Return information for only specific databases .PARAMETER Exclude Return information for all but these specific databases .PARAMETER Simple Shows concise information including Server name, Database name, and the date the last time backups were performed .NOTES Tags: DisasterRecovery, Backup Author: Klaas Vandenberghe ( @PowerDBAKlaas ) dbatools PowerShell module (https://dbatools.io) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/. .LINK https://dbatools.io/Get-DbaLastBackup .EXAMPLE Get-DbaLastBackup -SqlServer ServerA\sql987 Returns a custom object displaying Server, Database, RecoveryModel, LastFullBackup, LastDiffBackup, LastLogBackup, SinceFull, SinceDiff, SinceLog, Status, DatabaseCreated, DaysSinceDbCreated .EXAMPLE Get-DbaLastBackup -SqlServer ServerA\sql987 -Simple Returns a custom object with Server name, Database name, and the date the last time backups were performed .EXAMPLE Get-DbaLastBackup -SqlServer ServerA\sql987 | Out-Gridview Returns a gridview displaying Server, Database, RecoveryModel, LastFullBackup, LastDiffBackup, LastLogBackup, SinceFull, SinceDiff, SinceLog, Status, DatabaseCreated, DaysSinceDbCreated #> [CmdletBinding()] Param ( [parameter(Mandatory = $true, ValueFromPipeline = $true)] [Alias("ServerInstance", "SqlInstance")] [string[]]$SqlServer, [PsCredential]$Credential, [switch]$Simple ) DynamicParam { if ($SqlServer) { return Get-ParamSqlDatabases -SqlServer $SqlServer[0] -SqlCredential $Credential } } BEGIN { # Convert from RuntimeDefinedParameter object to regular array $databases = $psboundparameters.Databases $exclude = $psboundparameters.Exclude } PROCESS { foreach ($servername in $SqlServer) { Write-Verbose "Connecting to $servername" try { $server = Connect-SqlServer -SqlServer $servername -SqlCredential $Credential } catch { if ($SqlServer.count -eq 1) { throw $_ } else { Write-Warning "Can't connect to $servername. Moving on." Continue } } $dbs = $server.Databases | Where-Object { $_.name -ne 'TempDb' } if ($databases.count -gt 0) { $dbs = $dbs | Where-Object { $databases -contains $_.Name } } if ($exclude.count -gt 0) { $dbs = $dbs | Where-Object { $exclude -notcontains $_.Name } } foreach ($db in $dbs) { $result = $null Write-Verbose "Processing $($db.name) on $servername" if ($db.IsAccessible -eq $false) { Write-Warning "The database $($db.name) on server $servername is not accessible. Skipping database." Continue } # To avoid complicated manipulations on datetimes depending on locale settings and culture, # dates are compared to 0, which represents 1/01/0001 0:00:00 $SinceFull = if ($db.LastBackupdate -eq 0) {""} else {(New-TimeSpan -Start $db.LastBackupdate).Tostring()} $SinceFull = if ($db.LastBackupdate -eq 0) {""} else {$SinceFull.split('.')[0..($SinceFull.split('.').count - 2)] -join ' days ' } $SinceDiff = if ($db.LastDifferentialBackupDate -eq 0) {""} else {(New-TimeSpan -Start $db.LastDifferentialBackupDate).Tostring()} $SinceDiff = if ($db.LastDifferentialBackupDate -eq 0) {""} else {$SinceDiff.split('.')[0..($SinceDiff.split('.').count - 2)] -join ' days ' } $SinceLog = if ($db.LastLogBackupDate -eq 0) {""} else {(New-TimeSpan -Start $db.LastLogBackupDate).Tostring()} $SinceLog = if ($db.LastLogBackupDate -eq 0) {""} else {$SinceLog.split('.')[0..($SinceLog.split('.').count - 2)] -join ' days ' } $daysSinceDbCreated = (New-TimeSpan -Start $db.createDate).Days If ($daysSinceDbCreated -lt 1 -and $db.LastBackupDate -eq 0) { $Status = 'New database, not backed up yet' } elseif ((New-TimeSpan -Start $db.LastBackupDate).Days -gt 0 -and (New-TimeSpan -Start $db.LastDifferentialBackupDate).Days -gt 0){$Status = 'No Full or Diff Back Up in the last day'} elseif ($db.RecoveryModel -eq "Full" -and (New-TimeSpan -Start $db.LastLogBackupDate).Hours -gt 0){$Status = 'No Log Back Up in the last hour'} else { $Status = 'OK' } $result = [PSCustomObject]@{ Server = $server.name Database = $db.name RecoveryModel = $db.recoverymodel LastFullBackup = if ( $db.LastBackupdate -eq 0 ) { $null } else { $db.LastBackupdate.tostring() } LastDiffBackup = if ( $db.LastDifferentialBackupDate -eq 0 ) { $null } else { $db.LastDifferentialBackupDate.tostring() } LastLogBackup = if ( $db.LastLogBackupDate -eq 0 ) { $null } else { $db.LastLogBackupDate.tostring() } SinceFull = $SinceFull SinceDiff = $SinceDiff SinceLog = $SinceLog DatabaseCreated = $db.createDate DaysSinceDbCreated = $daysSinceDbCreated Status = $status } if ($Simple) { $result | Select-Object Server, Database, LastFullBackup, LastDiffBackup, LastLogBackup } else { $result } } } } } |