Sunday, 28 January 2018

Identifying Last Full, Differential and Log Backup Time for All Databases in SQL Server

In a consistent environment to maintain database backups on a regular basis, there is always a need for manual checks. Obvious to the fact that the alerts for failed backups would be implemented on the server. But, to manually check without logging into the server disk drive, here is an easy way to find the last Full, Differential, and Log backups in a single script.

Single script to check All databases backup times:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'D' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTFULLBACKUP
--CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'I' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTDIFFBACKUP
--CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'L' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTLOGBACKUP
--CONVERT(VARCHAR(16), MAX(CASE WHEN B.[TYPE] NOT IN ('D','I','L') THEN B.BACKUP_FINISH_DATE END), 120) AS LASTOTHERBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Full backup time for all databases:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'D' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTFULLBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Differential backup time for all databases:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'I' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTDIFFBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last Log backup time for all databases:
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE B.[TYPE] WHEN 'L' THEN B.BACKUP_FINISH_DATE END), 120) AS LASTLOGBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

To check last other database backups (apart from Full/Differential/Log) 
SELECT @@Servername AS SERVER_NAME,LEFT(D.NAME,20) AS DATABASE_NAME,
CONVERT(VARCHAR(16), MAX(CASE WHEN B.[TYPE] NOT IN ('D','I','L') THEN B.BACKUP_FINISH_DATE END), 120) AS LASTOTHERBACKUP
FROM SYS.DATABASES D
LEFT OUTER JOIN MSDB.DBO.BACKUPSET B ON D.NAME = B.DATABASE_NAME
WHERE D.NAME NOT IN ( 'TEMPDB','MASTER','MODEL','MSDB')
GROUP BY D.DATABASE_ID, D.NAME
ORDER BY CASE WHEN D.DATABASE_ID <= 4 THEN 0 ELSE 1 END, D.NAME

0 comments: