PowerShell Backup Check
Backup Check is a script I've written in PowerShell to get overdue backups for all your SQL Servers.
Previous scripts I've posted on this site have been HTA applications written in VBScript. I have decided that from now on all scripts will be written in PowerShell. It is obvious that Microsoft want PowerShell to be the standard scripting environment across all of their server products. Resistance is futile!
I have dozens of scripts that I use in my everyday job, and I've chosen a simple one that checks for overdue backups as the first to post on this site.
Before listing the script, a quick word about running PowerShell for SQL Server. I don't run it from the menu on SQL Server 2008 Management Studio; all this does is call SQLPS. SQLPS is a minishell with the SQL Server provider added, and doesn't have as much flexibility as the standard PowerShell console.
I have set up my PowerShell profile to automatically perform the preconfiguration so I can run the fully featured PowerShell and still use the SQL Server provider. I added the script from this MSDN link to my profile.
This script uses the SQL Server provider, allowing databases to be referenced hierarchically, in the same way as the file system or registry. This requires SQL Server 2008 client tools. Given that the majority of sites are still using SQL Server 2005, I tend to use SMO for most of my scripts. This one is an exception.
OK, on to the script. Create a text file in your working directory called Instances.txt. This lists all instances you want to monitor, one per line. The PowerShell provider requires an instance name for all instances, using DEFAULT for default instances. Here is an example:
EWAS1\DEFAULT
EWAS1\NAMEDINSTANCE
FRED2\DEFAULT
WILMA\RUBBLE
EWAS1\NAMEDINSTANCE
FRED2\DEFAULT
WILMA\RUBBLE
Two servers (EWAS1 and FRED2) with default instances, and 2 (EWAS1 and WILMA) with named instances.
I have created a function that will perform an auto-discovery on your subnet and populate this file for you. Any additional instances should be added manually.
function Enumerate-Instances {
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() |
Foreach-Object {
if ([Convert]::IsDbNull($_.InstanceName))
{
$_.ServerName + "\DEFAULT"
}
else
{
$_.ServerName + "\" + $_.InstanceName
}
} |
Set-Content -Path .\Instances.txt
}
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() |
Foreach-Object {
if ([Convert]::IsDbNull($_.InstanceName))
{
$_.ServerName + "\DEFAULT"
}
else
{
$_.ServerName + "\" + $_.InstanceName
}
} |
Set-Content -Path .\Instances.txt
}
Note that I have not used any aliases. I suspect most people reading this will be new to PowerShell, so it makes sense to keep it readable.
Run this as follows:
Enumerate-Instances
Now for the script to check your backups.
The script reads the instance names from Instances.txt and queries each one individually. You can change the number of hours to suit your backup regime. You can also replace LastBackupDate with LastDifferentialBackupDate or LastLogBackupDate if you wish (or create a new function for these).
function Check-Backups {
Get-Content .\Instances.txt |
Foreach-Object {
Get-ChildItem SQLSERVER:\SQL\$_\databases |
Where-Object {
$_.LastBackupDate -le [System.DateTime]::Now.AddHours(-24) -and $_.Name -ne "tempdb"
}
} |
Format-Table Parent, Name, LastBackupDate -Auto
}
Get-Content .\Instances.txt |
Foreach-Object {
Get-ChildItem SQLSERVER:\SQL\$_\databases |
Where-Object {
$_.LastBackupDate -le [System.DateTime]::Now.AddHours(-24) -and $_.Name -ne "tempdb"
}
} |
Format-Table Parent, Name, LastBackupDate -Auto
}
Add these functions to your PowerShell profile. Now all you have to do is run:
Check-Backups
The output should look something like this:
Parent Name LastBackupDate
------ ---- --------------
[EWAS1] TestDatabase 04/09/2009 23:04:44
[EWAS1] AnotherDatabase 01/01/0001 00:00:00
[WILMA\RUBBLE] SalesDataMart 17/09/2009 18:34:01
------ ---- --------------
[EWAS1] TestDatabase 04/09/2009 23:04:44
[EWAS1] AnotherDatabase 01/01/0001 00:00:00
[WILMA\RUBBLE] SalesDataMart 17/09/2009 18:34:01
If you find that system databases are not being checked, edit the script from the MSDN link at the top of this article, as follows:
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $true
I hope this article has given you a sense of what can be done with PowerShell. I haven't explained here how these scripts work, as the explanation would require an understanding of some of the building blocks of the language. I may begin a series of tutorial articles if there is sufficient demand.
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!Do you have a question about SQL Server? Would you like to answer a question?
Go to the SQL FAQ to get started.
