Storing Backups Locally? You have no Backups!

Me, being nice!

You’ve got business critical data stored in your SQL Server, don’t you? And you’re taking backups of those databases, right? But where are you storing those backups? If you’re storing backups locally, on the same media as the database itself, you’re asking for a world of hurt should something happen to that media. For instance, what if one of the disks fails where your storing both the database and its backup file? No more data. This applies even if you’re storing databases and their backups on a SAN; if the SAN has a problem you won’t be able to access either the database, or its backups.

Storing Backups Locally? You have no Backups!

     Austin Powers needs no backup!

It’s a really great idea to store backups offsite to enable recovery from a complete disaster, such as when a hacker miscreant ex-employee destroys your servers, and all the backups. However, before you can run, you must learn to walk.


This post provides a script showing the list of databases with the most recent backup listed. The LocalBackup column shows an X for backups taken to local media; consider modifying these backups to point to physically different media, such as a file server, or external disk. As pointed out above, storing backups locally is a bad idea. The script actually compares the physical filename of the database files to the physical filename of the database backups. The script compares files stored on both drive letters and UNC file shares.

Databases in full recovery model will show two rows, one for the data, and one for the log, providing details about both the most recent database backup, and the most recent log backup. Databases in simple recovery model will only show a single row, for database backups, since you can’t take a log backup for databases in simple recovery mode.

In case you don’t know how to take database backups in SQL Server, check the Microsoft Documentation for an overview.

Paying the rent!

Anyway, onto the raison d’être for this post, the script:

Results include the server name in case you want to run this against multiple SQL Server instances. If the BackupStartDate, and other backup-related columns contain NULL values, you’ve probably never taken a backup of the database, and should consider doing so immediately, if you value the data stored therein!

Exit, post-haste!

Let me know if you have any questions or comments about this post.

This post is part of our series on recovery.