sp_Blitz® Result: Backups Not Performed Recently

Before we start working on anything, we need to know all our user databases are getting backed up.  This part of our SQL Server sp_Blitz script checks to see if there’s been a full backup in the last 7 days.

Often, someone set up maintenance plans to back up specific databases by checking their names.  They checked all of the database names that were present at the time, but then down the road, other people added more databases – without knowing they needed to check more boxes to get ‘em backed up.

In rare cases, like for multi-terabyte databases, sometimes there’s a full backup happening just once per month, and differential backups are done more frequently.  Our Blitz only checks the last 7 days, so you would see alerts here after the 7th of the month.

To Learn More About Your Backups (or Lack Thereof)

To see how much data you could lose per database over the last couple of weeks, run this query:

	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

	DROP TABLE #backupset;

For each of your databases, it lists the maximum amount of time you went between backups over the last two weeks. To see the backup history for each database, copy/paste the query from the “more_info_query” column and run that – it’ll list the full backup history.

Time to ask some tough questions, eh?

To Fix the Problem

For servers using maintenance plans, you can edit maintenance plans in SQL Server Management Studio under Management, Maintenance Plans.  Your server may have multiple maintenance plans, like one for system databases and one for user databases.

If you’re implementing backups for the first time, consider using Ola Hallengren’s free maintenance scripts.  They’re not quite as easy as maintenance plans, but they’re much more flexible.  Ola’s scripts even work with third party backup compression products.

To learn more about the importance of backups, check out our Backup & Recovery Step by Step training.

Return to sp_Blitz®