Data Loss Risks Detected

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.

CREATE TABLE #backupset (backup_set_id INT, database_name NVARCHAR(128), backup_finish_date DATETIME, type CHAR(1), next_backup_finish_date DATETIME);
INSERT INTO #backupset (backup_set_id, database_name, backup_finish_date, type)
  SELECT backup_set_id, database_name, backup_finish_date, type
  FROM msdb.dbo.backupset WITH (NOLOCK)
  WHERE backup_finish_date >= DATEADD(dd, -14, GETDATE())
  AND database_name NOT IN ('master', 'model', 'msdb');
CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date);

UPDATE #backupset
SET next_backup_finish_date = (SELECT TOP 1 backup_finish_date FROM #backupset bsNext WHERE bs.database_name = bsNext.database_name AND bs.backup_finish_date < bsNext.backup_finish_date ORDER BY bsNext.backup_finish_date)
FROM #backupset bs;

SELECT bs1.database_name, MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss,
  'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query
  FROM #backupset bs1
  GROUP BY bs1.database_name
  ORDER BY bs1.database_name

SELECT TOP 50 bs1.database_name, bs1.backup_finish_date, bs1.next_backup_finish_date, DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date) AS minutes_of_data_loss,
  'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query
  FROM #backupset bs1
  ORDER BY DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date) DESC;

--DROP TABLE #backupset;
GO

CREATE TABLE #backupset (backup_set_id INT, database_name NVARCHAR(128), backup_finish_date DATETIME, type CHAR(1), next_backup_finish_date DATETIME);

INSERT INTO #backupset (backup_set_id, database_name, backup_finish_date, type)

SELECT backup_set_id, database_name, backup_finish_date, type

FROM msdb.dbo.backupset WITH (NOLOCK)

WHERE backup_finish_date >= DATEADD(dd, -14, GETDATE())

AND database_name NOT IN ('master', 'model', 'msdb');

CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date);

UPDATE #backupset

SET next_backup_finish_date = (SELECT TOP 1 backup_finish_date FROM #backupset bsNext WHERE bs.database_name = bsNext.database_name AND bs.backup_finish_date < bsNext.backup_finish_date ORDER BY bsNext.backup_finish_date)

FROM #backupset bs;

SELECT bs1.database_name, MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss,

'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query

FROM #backupset bs1

GROUP BY bs1.database_name

ORDER BY bs1.database_name

SELECT TOP 50 bs1.database_name, bs1.backup_finish_date, bs1.next_backup_finish_date, DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date) AS minutes_of_data_loss,

FROM #backupset bs1

ORDER BY DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date) DESC;

--DROP TABLE #backupset;

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?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT @@SERVERNAME AS ServerName ,
        YEAR(backup_finish_date) AS backup_year ,
        MONTH(backup_finish_date) AS backup_month ,
        CAST(AVG(( backup_size / ( DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) ) / 1048576 )) AS INT) AS throughput_mb_sec_avg ,
        CAST(MIN(( backup_size / ( DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) ) / 1048576 )) AS INT) AS throughput_mb_sec_min ,
        CAST(MAX(( backup_size / ( DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) ) / 1048576 )) AS INT) AS throughput_mb_sec_max
    FROM msdb.dbo.backupset bset
    WHERE bset.type = 'D' /* full backups only */
        AND bset.backup_size > 5368709120 /* 5GB or larger */
        AND DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) > 1 /* backups lasting over a second */
    GROUP BY YEAR(backup_finish_date) ,
        MONTH(backup_finish_date)
    ORDER BY @@SERVERNAME ,
        YEAR(backup_finish_date) DESC ,
        MONTH(backup_finish_date) DESC;
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT @@SERVERNAME AS ServerName ,

YEAR(backup_finish_date) AS backup_year ,

MONTH(backup_finish_date) AS backup_month ,

CAST(AVG(( backup_size / ( DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) ) / 1048576 )) AS INT) AS throughput_mb_sec_avg ,

CAST(MIN(( backup_size / ( DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) ) / 1048576 )) AS INT) AS throughput_mb_sec_min ,

CAST(MAX(( backup_size / ( DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) ) / 1048576 )) AS INT) AS throughput_mb_sec_max

FROM msdb.dbo.backupset bset

WHERE bset.type = 'D' /* full backups only */

AND bset.backup_size > 5368709120 /* 5GB or larger */

AND DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) > 1 /* backups lasting over a second */

GROUP BY YEAR(backup_finish_date) ,

MONTH(backup_finish_date)

ORDER BY @@SERVERNAME ,

YEAR(backup_finish_date) DESC ,

MONTH(backup_finish_date) DESC;

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.