I don’t like to publish small code snippets, but I’ve just rewritten one of my most used SQL scripts for SQL Server that gets the details about the last backup for every database (for both backup database and backup log).
It now makes use of with () and rank() over() to make it much easier to read and modify.
So I think it’s worth to share it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
with db as ( select [Instance] = @@SERVERNAME, [Database] = name, [RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'), [CreationTime] = crdate, [Status] = DATABASEPROPERTYEX(name, 'Status') from master..sysdatabases where name!='tempdb' ), lastfull as ( select * from ( select [Database] = s.database_name, --[Type] = s.type, [LastFullDate] = convert(varchar, s.backup_finish_date, 120), [LastFullSize] = s.backup_size, [LastFullDevice] = f.physical_device_name, [LastFullDevTyp] = f.device_type, [Nrank] = rank() over (partition by s.database_name order by s.backup_finish_date desc) from msdb.dbo.backupset s, msdb.dbo.backupmediafamily f where s.media_set_id=f.media_set_id and s.type='D' -- and f.device_type = 7 -- only backup devices ) f where nrank=1 ), lastlog as ( select * from ( select [Database] = s.database_name, --[Type] = s.type, [LastLogDate] = convert(varchar, s.backup_finish_date, 120), [LastLogSize] = s.backup_size, [LastLogDevice] = f.physical_device_name, [LastLogDevTyp] = f.device_type, [Nrank] = rank() over (partition by s.database_name order by s.backup_finish_date desc) from msdb.dbo.backupset s, msdb.dbo.backupmediafamily f where s.media_set_id=f.media_set_id and s.type='L' -- and f.device_type = 7 -- only backup devices ) l where nrank=1 ) select db.[Instance],db.[Database], db.[RecoveryMode], db.[CreationTime], db.[Status], lastfull.[LastFullDate], lastfull.[LastFullSize], lastfull.[LastFullDevice], lastfull.[LastFullDevTyp], lastlog.[LastLogDate], lastlog.[LastLogSize], lastlog.[LastLogDevice], lastlog.[LastLogDevTyp] from db left outer join lastfull on (db.[Database]=lastfull.[Database]) left outer join lastlog on (db.[Database]=lastlog.[Database]) |
As you can see, modify it to include for example incremental backups should be very easy.
Cheers
—
Ludo
The following two tabs change content below.
Latest posts by Ludovico (see all)
- Video: Where should I put the Observer in a Fast-Start Failover configuration? - November 29, 2022
- Video: The importance of Fast-Start Failover in an Oracle Data Guard configuration - November 29, 2022
- Find Ludovico at Oracle Cloud World 2022! - October 14, 2022
Pingback: Get the last database backup for all databases in a SQL Server instance - Ludovico Caldara - Blogs - triBLOG
Thanks for sharing good thinks
Certainly, or wrap an SSRS report aronud the data for presentation. The information presented here is just like any other SQL query and can be made available through a variety of presentation layers.
Pingback: Need help with this backup audit SQL Server script | Question and Answer