functions/Get-DbaLastBackup.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
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 }
}
        }
    }
}