SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized

Four years ago, I wrote a blog post SQL SERVER – Finding Last Backup Time for All DatabaseDatabase – Last Full, Differential and Log Backup.

Well, in technology, there is always space for improvement and we should always be ready to learn and progress. SQL Expert TheSQLGuru came up with further optimized script which also lists all the details which earlier blog post listed, and the script is very quick to execute. Here is the script.

SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model

Thanks TheSQLGuru for excellent script, you win USD 20 Amazon Gift Card or INR 1000 Flipkart Gift Voucher. I have already sent you emails with details.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQLAuthority News – Microsoft Whitepaper – Migrating Content Between Report Servers
Next Post
SQL SERVER – Three Questions – Do You Know Your Servers? – Book Gift

Related Posts

3 Comments. Leave new

Leave a Reply

Menu