How to solve the challenge in ensure their databases are being backed up for every SQL database administrator? Well, every SQL administrator will check the Last Backup Date on their databases but what if you have multiple instances of SQL installed on a server? It just gets complicated with viewing each database with new connection through SQL Management Studio (SMSS).
What if we can use PowerShell to help SQL Administrator to simplify this task? Let us try exploring the SQLPS PowerShell Module and find out how we can obtain last backup date for every database in every SQL instance installed in a server.
Firstly, we will have to launch a PowerShell console and import the SQLPS PowerShell module on the server like below.
# Import SQLPS PowerShell Module Import-Module SQLPS ;
Next, I’m going to demonstrate on how to list the SQL instances installed on the server below.
# Get SQL Instances Get-ChildItem -Path SQL\$ENV:COMPUTERNAME ;
Since it is possible to list the names of all the SQL instances installed on the server, we will try to loop each SQL instance and list all the databases per SQL instance like below.
# Get SQL Databases on all SQL Instances
Get-ChildItem -Path SQL\$ENV:COMPUTERNAME | `
ForEach-Object {
$_ | `
Select-Object `
-ExpandProperty Databases | `
Format-Table `
Parent, `
Name, `
Status, `
ActiveConnections `
-AutoSize ;
} ;
Awesome. Once we can list all the databases per SQL instance, we can try to select the Last Backup Date, Last Differential Backup Date and Last Log Backup Date properties per database.
To make life a lot easier for SQL administrator, we will create custom property such as Delta in Day, Hour, Minute and Second so that we can easily identify the delta duration since a last successful backup.
# Get SQL Instances and Databases Backup Information
Get-ChildItem -Path SQL\$ENV:COMPUTERNAME |`
ForEach-Object {
$_ | `
Select-Object `
-ExpandProperty Databases | `
Where-Object { $_.Name -CNotIn "msdb","model","master","tempdb"} | `
ForEach-Object {
$_ | `
Select-Object `
Parent, `
Name, `
RecoveryModel, `
LastBackupDate, `
@{ `
N="LastBackupDateDeltaInDay" ; `
E={(New-TimeSpan `
-Start $_.LastBackupDate `
-End (Get-Date)).Days ; } ; `
}, `
@{ `
N="LastBackupDateDeltaInHour" ; `
E={(New-TimeSpan `
-Start $_.LastBackupDate `
-End (Get-Date)).Hours ; } ; `
}, `
@{ `
N="LastBackupDateDeltaInMinute" ; `
E={(New-TimeSpan `
-Start $_.LastBackupDate `
-End (Get-Date)).Minutes ; } ; `
}, `
@{ `
N="LastBackupDateDeltaInSecond" ; `
E={(New-TimeSpan `
-Start $_.LastBackupDate `
-End (Get-Date)).Seconds ; } ; `
}, `
LastDifferentialBackupDate, `
@{ `
N="LastDifferentialBackupDateDeltaInDay" ; `
E={(New-TimeSpan `
-Start $_.LastDifferentialBackupDate `
-End (Get-Date)).Days ; } ; `
}, `
@{ `
N="LastDifferentialBackupDateDeltaInHour" ; `
E={(New-TimeSpan `
-Start $_.LastDifferentialBackupDate `
-End (Get-Date)).Hours ; } ; `
}, `
@{ `
N="LastDifferentialBackupDateDeltaInMinute" ; `
E={(New-TimeSpan `
-Start $_.LastDifferentialBackupDate `
-End (Get-Date)).Minutes ; } ; `
}, `
@{ `
N="LastDifferentialBackupDateDeltaInSecond" ; `
E={(New-TimeSpan `
-Start $_.LastDifferentialBackupDate `
-End (Get-Date)).Seconds ; } ; `
}, `
LastLogBackupDate, `
@{ `
N="LastLogBackupDateDeltaInDay" ; `
E={(New-TimeSpan `
-Start $_.LastLogBackupDate `
-End (Get-Date)).Days ; } ; `
}, `
@{ `
N="LastLogBackupDateDeltaInHour" ; `
E={(New-TimeSpan `
-Start $_.LastLogBackupDate `
-End (Get-Date)).Hours ; } ; `
}, `
@{ `
N="LastLogBackupDateDeltaInMinute" ; `
E={(New-TimeSpan `
-Start $_.LastLogBackupDate `
-End (Get-Date)).Minutes ; } ; `
}, `
@{ `
N="LastLogBackupDateDeltaInSecond" ; `
E={(New-TimeSpan `
-Start $_.LastLogBackupDate `
-End (Get-Date)).Seconds ; } ; `
} ;
} ;
} ;
What can we use those custom delta properties for? We can use those delta properties for the followings:
- To check if the database is overdue for a backup
- To check if the database is recently backed up successfully
Last but not least, with the understanding of how to obtain all the databases per instance, we can easily initiate a full SQL backup per database using the Backup-SQLDatabase cmdlet to kick off the backup of all those databases in that server that has multiple SQL instances.
# Get SQL Databases on all SQL Instances and perform a Full Backup of Database
Get-ChildItem -Path SQL\$ENV:COMPUTERNAME |`
Select -Expand Databases | `
Where-Object { $_.Name -CNotIn "msdb","model","master","tempdb"} | `
ForEach-Object {
Backup-SqlDatabase `
-ServerInstance $_.Parent `
-Database $_.Name ;
} ;
Isn’t it easy? That’s it.
Geek Notebook 