Verify SQL Server Backups Existence in the File System with PowerShell

By:   |   Comments (3)   |   Related: > Backup


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?


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
        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)
        $res = ($dbcnt - $minus)
  $backups = Get-ChildItem $location -Filter $extension | Where-Object {$_.CreationTime -gt (Get-Date).AddHours($hours)}
        $bakcnt = 0
        foreach ($bak in $backups)
        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 -24

In 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"})

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Smith 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

Comments For This Article

Thursday, March 26, 2015 - 9:25:47 AM - Tim Back To Top (36710)

@Fredrick Not sure what you're requesting on the versions, but I have an idea on the other part.

Keep in mind that we want to avoid using this script if we're in an environment where we can fully test our backups; this script helps in those limited environments.

@Ray That was an oversight; it should be like over equals, especially if differences are not partials ("dog", "cat", "rabbit").  However, if someone wants to use the equals, you can convert the property to a string.

$dbrecoverystring = $db.RecoveryModel.ToString()
if ($dbrecoverystring -eq "Full")
     Write-Host $db.Name " and this is a full recovery model database."

The RecoveryModel object doesn't appear to be a string, at least according to what I can find in the documentation and when I convert it to a string, it responds as it should.  And yes, those are other ways to obtain the count.


Wednesday, March 25, 2015 - 7:09:38 PM - Ray Giacalone Back To Top (36697)

Hi Tim, thx for taking the time to write up this article.

Just a couple of points...I could not get the database object to filter down with the EQUAL operator against the RecoveryModel property as you show here:
$srv.Databases | Where-Object {$_.RecoveryModel -eq "FULL"}

This always came up empty...however I was able to use the LIKE operator and - hey it started working.
$srv.Databases | Where-Object {$_.RecoveryModel -like "Full"});

What could account for the difference between our environments to explain that?

The other point is getting counts on collections without going through the foreach loop...these will also return the counts:
($srv.Databases |Where-Object {$_.RecoveryModel -like "Full"}).Count;

Wednesday, March 25, 2015 - 4:51:41 PM - Frederick Back To Top (36691)

Can you do a dynamic script for all SQL servers versions and for all their databases backup check and email it in an excel format?

get free sql tips
agree to terms