Is Your SQL Server a Loser?
Detecting Data Loss Risks

MSDB: Where SQL Server Stores Backup History

When SQL Server does backups or restores, it records the history in these MSDB tables:

  • BackupSet – includes server_name, database_name, backup_start_date, backup_finish_date. Note that even failed backups will show up here, so you’ll need to filter those out.
  • BackupFilegroup and BackupFile – describes the database being backed up, not the database backup files.
  • backupMediaFamily – describes the backup location, like disk, tape, virtual device.
  • RestoreHistory – includes some of the restore parameters like RECOVERY or RESTART.

By default, this data never goes away. sp_Blitz® checks to make sure that you’re only keeping 60 days or less of backup history because in edge case scenarios, a big MSDB can be a backup bottleneck. Before you clean up your MSDB history, though, check out these queries to analyze your backup performance and data loss risks.

What Are My Biggest Data Loss Risks?

The below query asks two questions about the last two weeks of backups:

For each database, what was my biggest data loss window? For example, if the database had a full backup that finished at 1:30 AM, and then the next log backup finished at 2:05 AM, it could have lost up to ~35 minutes of data if the server crashed at 2:04 AM.

What were the top 50 biggest data loss risks overall? Maybe we’ve got a handful of databases that consistently get less-frequent backups.

To get the answers, we start by putting the last 2 weeks of backup data in TempDB. MSDB’s backup history data isn’t indexed in a way that supports the queries we’re going to run, and we don’t want to lock up MSDB and slow down backup/restore operations. That’s why we put it into TempDB first, index it to make the queries run fast, and then return the result sets.

We don’t drop the temp table afterwards so you can go spelunking through there looking for backup problems as well.

If you get errors or unexpected results from these scripts, let us know. Include as much detail as possible including the exact build number of SQL Server, the number of databases involved, the script’s results, and your expected results.

These results usually lead to scary conversations. Ask your business users how much data you’re allowed to lose, and how long you’re allowed to be down for. Our HA/DR planning PDF worksheet helps.

How Fast Are My SQL Server Backups?

For reference, network throughput is around 100 MB/sec. If you’re not backing up over a network, you can check your storage bandwidth’s speed with Kendra’s Bandwidth Reference Poster.

Have your average backup speeds gone up or down in the last few months? Often we see a moment in time where backup performance suddenly got worse across the entire server. Note that this doesn’t have anything to do with data growth – this is about throughput, not total backup runtime. If your server’s backup throughput suddenly takes a nosedive, that’s your clue that something changed about the backup configuration, the database storage, or the backup storage.

Armed with the data in these tables, we can start to make estimates about how long a restore will take. If we know that yesterday’s full backup, plus all of its transaction log backups, took 47 minutes in total, then a restore might take 47 minutes as well. The exact time will vary based on things like Instant File Initialization, the read speed from the backup target, and the write speed on the database and log file drives. It’s just a starting point. However, if we know that the business wants us to be able to restore to any point in time in under 15 minutes, but our full plus log backups take 45 minutes, we probably will not meet our goals.

Are My Backups (and My Databases) Growing?

Wouldn’t it be nice to see an instant snapshot over time about how fast your SQL Server has grown?

Get this script, which pivots backup size over time. It returns a row for each database, plus columns for 0 (this month), -1 (last month), -2 (two months ago), etc.

Copy/paste this into Excel, and run sums on each column to build a fancy graph for management. This helps you prove that your databases have grown by X% over the last Y months. This is a great way to help demonstrate why you need additional hardware budgeting, too.

More Reading for Recovery

Here’s our favorite SQL Server backup resources.