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
You may also refer:
"How to create a Full Backup in SQL server"
"How to create a Full Backup in SQL server"
