An Exceptions-Based Report for SQL Backups   Leave a comment

UPDATE 3/26/2011: If you used an earlier version of the stored procedure, I have made a couple of minor corrections to the procedure below, including adding logic for including, in the all_exceptions implementation, dbs in the BULK_LOGGED recovery model, which were omitted before.

Here is a stored procedure that reports on SQL backups that are missing, based on the number of days since the last backups of each type, for each SQL database on an instance. This stored procedure works with all versions of SQL Server, from SQL 2000 and above.

In particular, calling the procedure with the “all_exceptions” parameter will show records for any databases in the SQL instance that have not had a Transaction Log backup in over 1 day (if in the FULL or BULK_LOGGED recovery models), or have not had a Full backup in over 7 days, or have not had a Differential backup in over 1 day. If there has never been a backup, this is indicated in the results by returning “999” in the  “Days_Since_Last_Full,” “Days_Since_Last_TLog,” or “Days_Since_Last_Diff” columns. Calling with the “all_exceptions” parameter returns results sorted by recovery model, then database name.

In a follow-up post (click HERE) I will include a SQL Server Reporting Services report example which integrates nicely with this stored procedure. The “999” result is used in some of the formatting expressions in the SSRS report, so if you intend to use this report, you should not modify those parts of the procedure.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

USE [master]
GO

CREATE PROC [dbo].[rpt_SQL_Backups_Report]

	@type VARCHAR(50) = 'all_exceptions'

AS

DECLARE @srvrName VARCHAR(500)
SELECT @srvrName = @@SERVERNAME

/*

	Created by Norm Enger 2/28/2011, based in part on a script at:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=715

	Shows status, recovery models, last full backup dates, last tlog backup dates,
	last diff backup dates, and days lapsed since each of those, for all dbs on a server.
	Works for SQL 2000 and above.

	The "all_alpha" sorts by db name.

	The "all_recovery_alpha" sorts by recovery model, then db name.

	The "all_exceptions" shows only ones that have TLog backups aged over 1 day,
	Full backups aged over 7 days, or Diff backups aged over 1 day, or any type
	that has never have had a backup (indicated by returning "999" in the
	"Days_Since_Last_Full, "Days_Since_Last_TLog", or "Days_Since_Last_Diff"
	results; and sorts by recovery model, then db name.

	Sample executions:
	EXEC rpt_SQL_Backups_Report 'all_exceptions'
	EXEC rpt_SQL_Backups_Report 'all_recovery_alpha'
	EXEC rpt_SQL_Backups_Report 'all_alpha'

*/

SET NOCOUNT ON

CREATE TABLE #TEMP
(
	DBName VARCHAR(500),
	[Recovery] VARCHAR(500),
	[DBStatus] VARCHAR(500)
)

DECLARE
	@dbname VARCHAR(500),
	@STR VARCHAR (8000)

DECLARE DBRECOVERYMODELGENERATOR_CURSOR CURSOR FOR
	SELECT name FROM sysdatabases
	WHERE category IN ('0', '1','16')
	ORDER BY name

OPEN DBRECOVERYMODELGENERATOR_CURSOR

FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname WHILE (@@FETCH_STATUS <> -1)

BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		SELECT @STR = 'SELECT ''' + @dbname + ''' AS DBName, CAST(DATABASEPROPERTYEX ('''+ @dbname + ''', ''recovery'') AS VARCHAR(500)) AS Recovery, CAST(DATABASEPROPERTYEX (''' + @dbname + ''', ''STATUS'') AS VARCHAR(500)) + CAST(CASE WHEN DATABASEPROPERTYEX(''' + @dbname + ''', ''IsInStandBy'') = 0 THEN '''' ELSE '' (Standby / Read-Only)'' END AS VARCHAR(500)) AS DBStatus'
		INSERT INTO #TEMP
		EXEC (@STR)
	END 

	FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname
END

CLOSE DBRECOVERYMODELGENERATOR_CURSOR

DEALLOCATE DBRECOVERYMODELGENERATOR_CURSOR

IF @type = 'all_alpha'
BEGIN
	SELECT
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	T.[Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	U.[Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	V.[Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	ORDER BY DBName
END

IF @type = 'all_recovery_alpha'
BEGIN
	SELECT
	ServerName = @srvrName,
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	ORDER BY [Recovery], DBName
END

IF @type = 'all_exceptions'
BEGIN
	SELECT
	ServerName = @srvrName,
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	WHERE
		(
			(
			T.[Days_Since_Last_Full] > 7
			)
			OR
			(
			U.[Days_Since_Last_TLog] > 1
			AND a.[Recovery] IN ('FULL','BULK_LOGGED')
			)
			OR
			(
			T.[Days_Since_Last_Full] IS NULL
			)
			OR
			(
			U.[Days_Since_Last_TLog] IS NULL
			AND a.[Recovery] IN ('FULL','BULK_LOGGED')
			)
			OR
			(
			a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind')
			AND
			a.[DBName] NOT LIKE 'adventurework%'
			AND
			V.[Days_Since_Last_Diff] > 1
			)
			OR
			(
			a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind')
			AND
			a.[DBName] NOT LIKE 'adventurework%'
			AND
			V.[Days_Since_Last_Diff] IS NULL
			)
		)
		AND
		a.[DBName] NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		a.[DBName] NOT LIKE 'adventurework%'

	ORDER BY [Recovery], DBName
END

DROP TABLE #TEMP

Posted February 28, 2011 by Norm Enger in Microsoft SQL Server

Advertisements

Posted February 28, 2011 by Norm Enger in Microsoft SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: