Toolbox - Do You Have Current Backups?

On our managed servers we run a job each day to check that either a FULL or DIFF has run in the last 24 hours, but all-too-frequently I find myself on a server that we don't manage, trying to determine the current situation (aka the "health check").

An important thing to check with the health check - or even when responding to a 911-SAVEME call - is whether there are current backups.

https://stephcalvertart.com/wp-content/uploads/2014/07/funny-memes-wordpress-maintenance-backups-updates-hearts-and-laserbeams-star-wars-obi-wan-kenobi.jpg

Similar to my previous Toolbox post,  I wanted a script I could just run without worrying whether the server was 2005 or 2016, and it also needed to handle availability groups.  This second requirement made the first even more important as their are new DMV's for availability groups that are needed in SQL 2012+ but *don't exist* in 2005-2008.

I used the same SQLVersion logic as the previous post, CONVERTing the SERVERPROPERTY('ProductVersion') into a four character value:

9.00
10.0
10.5
11.0
12.0
13.0

...which I then used to branch the code.

Like most of my code, I started with several general queries (cited in the code) and then modified them significantly for my needs. #AlwaysGiveCreditWhereDue

--

https://memegenerator.net/instance/53270254/yeah-thatd-be-great-yeah-if-you-could-just-show-me-some-code-thatd-be-great

Without further ado, the code:

--

/*
Most Recent Backups

There is a code branch to allow for availability groups so that it is visible
whether the given database is the preferred backup replica or not
since backups may appear “missing” on the non-preferred replicas

Guts of availability group backup query modified from:
http://www.centinosystems.com/blog/sql/when-was-your-last-backup/

Modified to exclude AG-only fields for pre-2012
*/


DECLARE @SQLVersion as NVARCHAR(4)

SET @SQLVersion = LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4)

/*
PRINT @SQLVersion
*/


IF @SQLVersion in ('9.00', '10.0', '10.5')
BEGIN
SELECT @@SERVERNAME as [InstanceName]
, db.[name] as [DatabaseName]
, db.[recovery_model_desc] as [RecoveryModelDescription]
, 'Not AG Database' as [IsAGDatabase] /* Placeholder since no AGs before 2012 */
, 'N/A' as [AGName]
, 'N/A' as [IsPreferredReplica] /* Placeholder since no AGs before 2012 */
,  CASE
WHEN LastFullBackup is NULL
THEN 'NONE'
ELSE 
CONVERT(varchar(10),t.[LastFullBackup], 111) + ' ' /* Format YYYY/MM/DD */
+ CONVERT(varchar(10),t.[LastFULLBackup], 108) /* Format HH:MM:SS */
  END  as [LastFullBackupDate]
, CASE
WHEN LastDiffBackup is NULL
THEN 'NONE'
ELSE 
CONVERT(varchar(10),t.[LastDiffBackup], 111) + ' '  /* Format YYYY/MM/DD */
+ CONVERT(varchar(10),t.[LastDiffBackup], 108) /* Format HH:MM:SS */
  END  as [LastDiffBackupDate]
, CASE
WHEN db.[recovery_model_desc] = 'SIMPLE'
THEN 'N/A - SIMPLE RECOVERY' /* No LOG backups in SIMPLE */
WHEN LastLogBackup is NULL
THEN 'NONE'
ELSE 
CONVERT(varchar(10),t.[LastLogBackup], 111) + ' ' /* Format YYYY/MM/DD */ 
+ CONVERT(varchar(10),t.[LastLogBackup], 108) /* Format HH:MM:SS */
  END as [LastLogBackupDate]
, CASE
WHEN LastFullBackup is NULL
THEN 'N/A - NO FULLS'
ELSE  cast(t.[DaysSinceLastFullBackup] as varchar(100))
  END as [DaysSinceLastFullBackup]
, CASE
WHEN LastDiffBackup is NULL
THEN 'N/A - NO DIFFS'
ELSE  cast(t.[DaysSinceLastDiffBackup] as varchar(100))
  END as [DaysSinceLastDiffBackup]
, CASE
WHEN db.[recovery_model_desc] = 'SIMPLE'
THEN 'N/A - SIMPLE RECOVERY' /* No LOG backups in SIMPLE */
WHEN LastLogBackup is NULL
THEN 'N/A - NO LOGS'ELSE
cast (t.[MinutesSinceLastLogBackup]  as varchar(100))
  END as [MinutesSinceLastLogBackup]
FROM sys.databases db
LEFT OUTER JOIN
(
SELECT p.[database_name] as [DatabaseName]
, MAX(p.[D]) as [LastFullBackup]
, MAX(p.[I]) as [LastDiffBackup]
, MAX(p.[L]) as [LastLogBackup]
, DATEDIFF(DAY, MAX(p.[D]), GETDATE()) as [DaysSinceLastFullBackup]
, DATEDIFF(DAY, MAX(p.[I]), GETDATE()) as [DaysSinceLastDiffBackup]
, DATEDIFF(MINUTE, MAX(p.[L]), GETDATE()) as [MinutesSinceLastLogBackup]
FROM msdb.dbo.backupset bs
PIVOT (MAX(bs.backup_finish_date) FOR [type] IN ([D],[L],[I])) as p
GROUP BY p.[database_name]
) t
ON db.[name] = t.[DatabaseName]
/*
-- Commented Out since no AG's before 2012
LEFT OUTER JOIN sys.dm_hadr_database_replica_states agdb
ON agdb.[database_id] = db.[database_id]
AND agdb.[is_local] = 1
LEFT OUTER JOIN sys.dm_hadr_name_id_map agmap
ON agdb.group_id = agmap.ag_id
*/
WHERE db.[name]<>'tempdb'
ORDER BY LastFullBackupDate DESC
END

ELSE /* SQL 2012+ */

BEGIN
SELECT @@SERVERNAME as [InstanceName]
, db.[name] as [DatabaseName]
, db.[recovery_model_desc] as [RecoveryModelDescription]
, CASE
  WHEN agdb.[database_id] IS NOT NULL
  THEN 'AG Database'
  ELSE 'Not AG Database'
  END as [IsAGDatabase]
, ISNULL(agmap.ag_name,'N/A') as [AGName]
, CASE
  WHEN
  sys.fn_hadr_backup_is_preferred_replica(db.[name]) = 1
  AND agdb.[database_id] IS NOT NULL
  THEN 'YES'
  WHEN sys.fn_hadr_backup_is_preferred_replica(db.[name]) = 1
  AND agdb.[database_id] IS NULL
  THEN 'N/A'
  ELSE 'NO'
  END as [IsPreferredReplica]
, CASE
WHEN LastFullBackup is NULL
THEN 'NONE'
ELSE 
CONVERT(varchar(10),t.[LastFullBackup], 111) + ' ' /* Format YYYY/MM/DD */ 
+ CONVERT(varchar(10),t.[LastFULLBackup], 108) /* Format HH:MM:SS */
  END  as [LastFullBackupDate]
, CASE
WHEN LastDiffBackup is NULL
THEN 'NONE'
ELSE 
CONVERT(varchar(10),t.[LastDiffBackup], 111) + ' ' /* Format YYYY/MM/DD */ 
+ CONVERT(varchar(10),t.[LastDiffBackup], 108) /* Format HH:MM:SS */
  END  as [LastDiffBackupDate]
, CASE
WHEN db.[recovery_model_desc] = 'SIMPLE'
THEN 'N/A - SIMPLE RECOVERY' /* No LOG backups in SIMPLE */
WHEN LastLogBackup is NULL
THEN 'NONE'
ELSE 
CONVERT(varchar(10),t.[LastLogBackup], 111) + ' ' /* Format YYYY/MM/DD */ 
+ CONVERT(varchar(10),t.[LastLogBackup], 108) /* Format HH:MM:SS */
  END as [LastLogBackupDate]
, CASE
WHEN LastFullBackup is NULL
THEN 'N/A - NO FULLS'
ELSE  cast(t.[DaysSinceLastFullBackup] as varchar(100))
  END as [DaysSinceLastFullBackup]
, CASE
WHEN LastDiffBackup is NULL
THEN 'N/A - NO DIFFS'
ELSE  cast(t.[DaysSinceLastDiffBackup] as varchar(100))
  END as [DaysSinceLastDiffBackup]
, CASE
WHEN db.[recovery_model_desc] = 'SIMPLE'
THEN 'N/A - SIMPLE RECOVERY' /* No LOG backups in SIMPLE */
WHEN LastLogBackup is NULL
THEN 'N/A - NO LOGS'ELSE
cast (t.[MinutesSinceLastLogBackup]  as varchar(100))
  END as [MinutesSinceLastLogBackup]
FROM sys.databases db
LEFT OUTER JOIN
(
SELECT p.[database_name] as [DatabaseName]
, MAX(p.[D]) as [LastFullBackup]
, MAX(p.[I]) as [LastDiffBackup]
, MAX(p.[L]) as [LastLogBackup]
, DATEDIFF(DAY, MAX(p.[D]), GETDATE()) as [DaysSinceLastFullBackup]
, DATEDIFF(DAY, MAX(p.[I]), GETDATE()) as [DaysSinceLastDiffBackup]
, DATEDIFF(MINUTE, MAX(p.[L]), GETDATE()) as [MinutesSinceLastLogBackup]
FROM msdb.dbo.backupset bs
PIVOT (MAX(bs.backup_finish_date) FOR [type] IN ([D],[L],[I])) as p
GROUP BY p.[database_name]
) t ON db.[name] = t.[DatabaseName]
LEFT OUTER JOIN sys.dm_hadr_database_replica_states agdb
ON agdb.[database_id] = db.[database_id]
AND agdb.[is_local] = 1
LEFT OUTER JOIN sys.dm_hadr_name_id_map agmap
ON agdb.group_id = agmap.ag_id
WHERE db.[name]<>'tempdb'
ORDER BY LastFullBackupDate DESC
END

--

The results look like this (split for readability):

--

InstanceName	DatabaseName	Recovery Model Description	IsAGDatabase	AGName	IsPreferredReplica
Instance01	Database01	FULL	Not AG Database	N/A	N/A
Instance01	Database02	FULL	AG Database	AvailGroup99	YES
Instance01	Database03	FULL	AG Database	AvailGroup99	YES
Instance01	Database04	FULL	AG Database	AvailGroup99	YES
Instance01	Database05	FULL	AG Database	AvailGroup99	YES
Instance01	Database06	FULL	AG Database	AvailGroup99	YES
Instance01	msdb	SIMPLE	Not AG Database	N/A	N/A
Instance01	model	FULL	Not AG Database	N/A	N/A
Instance01	master	SIMPLE	Not AG Database	N/A	N/A

InstanceName	DatabaseName	LastFullBackupDate	LastDiffBackupDate	LastLogBackupDate
Instance01	Database01	NONE	NONE	NONE
Instance01	Database02	2018/04/03 22:30:39	NONE	2018/04/04 13:01:09
Instance01	Database03	2018/04/03 22:30:38	NONE	2018/04/04 13:01:08
Instance01	Database04	2018/04/03 22:30:38	NONE	2018/04/04 13:01:09
Instance01	Database05	2018/04/03 22:29:40	NONE	2018/04/04 13:01:02
Instance01	Database06	2018/04/03 22:28:29	NONE	2018/04/04 13:01:01
Instance01	msdb	2018/04/03 22:00:05	NONE	N/A - SIMPLE RECOVERY
Instance01	model	2018/04/03 22:00:04	NONE	NONE
Instance01	master	2018/04/03 22:00:02	NONE	N/A - SIMPLE RECOVERY

InstanceName	DatabaseName	DaysSinceLastFullBackup	DaysSinceLastDiffBackup	MinutesSinceLastLogBackup
Instance01	Database01	N/A - NO FULLS	N/A - NO DIFFS	N/A - NO LOGS
Instance01	Database02	1	N/A - NO DIFFS	23
Instance01	Database03	1	N/A - NO DIFFS	23
Instance01	Database04	1	N/A - NO DIFFS	23
Instance01	Database05	1	N/A - NO DIFFS	23
Instance01	Database06	1	N/A - NO DIFFS	23
Instance01	msdb	1	N/A - NO DIFFS	N/A - SIMPLE RECOVERY
Instance01	model	1	N/A - NO DIFFS	N/A - NO LOGS
Instance01	master	1	N/A - NO DIFFS	N/A - SIMPLE RECOVERY

--

The code flags whether each database is in an AG, and if so whether it is currently the primary replica and the name of its availability group.

It calculates the age of the most recent backup of each type and then sorts by the FULL value desc.

--

I use this query all the time - hope this helps!