By: Tim Smith | Comments (3) | Related: > Backup
Problem
We recently discovered that on some days, we don't have the full database backups that we should for each SQL Server instance. Apparently, the application terminates, but never notifies us that it failed. We verify backups as often as we can in our restricted environment, but wondered if there was a way to do a quick automated look at the counts after the backup completed, and run it independently?
Solution
A few thoughts:
- In an idyllic environment, where no legal or architecture issues exist, we would always run CHECKDB before backing up a database and immediately test a backup after it's created on another server. In addition, no drive would ever fail, so once verified, always verified!
- When we know our bottlenecks (and legal department), we may be able to identify what part of the process we can do and how we can re-work it. For instance, in some environments, I restore a backup on a non-production server, run CHECKDB, save the results, and remove the database on the non-production server, acting immediately if the results are negative (except with certain data, which have legal restrictions).
- In addition, the frequency may change. How often do we need to backup a database that has one new record a week versus a database that has one new record per half millisecond?
- On the most basic backup level (as a part of the backup process), I would recommend immediately performing a VERIFYONLY on the BAK file, to ensure that on that point it's valid (it should be a part of the process).
I point this out because a backup file existing is not the same as a restored database, or a restored database which integrity has been verified by CHECKDB. I also understand that verifying a backup exists is the reality for some environments, especially if other software is used. I've spoken with developers who've used third party tools which failed during the backups (i.e.: an unexpected termination) and only later discovered that they didn't have the full amount of backups they should have. In addition, our backup process may run successfully, then a drive disappears and now we don't have backups.
This script will do a quick accountability check, but it is not the same as a verified backup that can be restored; it is simply "This is the count you should have, and this is the count I found" with an alert sent if the counts don't match (you will need to edit the alerts for your environment).
Function Verify-BakExist
{
Param(
[string]$server
,[int]$minus
,[string]$location
,[string]$extension
,[int]$hours
)
Process
{
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$dbcnt = 0
foreach ($db in $srv.Databases)
{
$dbcnt++
}
$res = ($dbcnt - $minus)
$backups = Get-ChildItem $location -Filter $extension | Where-Object {$_.CreationTime -gt (Get-Date).AddHours($hours)}
$bakcnt = 0
foreach ($bak in $backups)
{
$bakcnt++
}
if ($bakcnt -ne $res)
{
$body = "Missing backups for $server. Found $bakcnt, should be $res."
### This script is designed to send an alert, thus the below would have the parameters filled in such as the smtp server, and to and from email address, or if using another function for alerting, replace this with it.
Send-MailMessage -SmtpServer "" -To "" -From "" -Subject "Missing Backups" -Body $body
}
}
}
### If we zip our backups after we back them up:
Verify-BakExist -server "" -minus 2 -location "" -extension "*.ZIP" -hours -24
### If we have direct BAK files
Verify-BakExist -server "" -minus 2 -location "" -extension "*.BAK" -hours -24In those limited environments, and with those limited clients, this does help notify us if the minimum problem occurs (we get notified that we have 6 backups when we have 19 databases). Also, if we can do a full test daily, and back up every six hours, we can run this script three times a day with our fourth time being the full test (CHECKDB, backup, restore, drop, or a derivative). Finally, I can use a derivative of this script to verify that I have transaction log backups for every database that is set to FULL recovery and that has a similar backup time (for instance an hour), by changing the database loop to filter out non-full recovery databases, or I could use the minus variable to subtract the non-full recovery databases.
foreach ($db in $srv.Databases | Where-Object {$_.RecoveryModel -eq "FULL"})
{
$dbcnt++
}One caution I would make to developers: with SMO, it's tempting to just loop through the databases and get the last backup time (in the above script, obtaining $db.LastBackupDate). While that will tell you the last time of the backup, that doesn't mean the backups exist, which this script verifies. Hopefully, you never run into the situation where you verify a backup time, but the files aren't there; unfortunately, this happens.
Next Steps
- If in a limited environment, test the above script.
- Consider what times you could go through the full testing process, from backing up to an actual restore, to ensure that everything is good. The above script helps for the days you don't do the full process.
- Check out these additional resources:
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
