Following statement can be used to see the last backups of all databases.<\/p>\n\n\n\n
You get information of last backup’s start time, end time, duration in minutes, hours since last backup both for full and log backups.<\/p>\n\n\n\n
SELECT db.name,db.recovery_model_desc,d.[Database Backup Start],d.[Database Backup End], datediff(mi,d.[Database Backup Start],d.[Database Backup End]) as [Minutes Duration],
cast (datediff(hh,d.[Database Backup End],SYSDATETIME()) as nvarchar)+':'+ right ('000'+cast (datediff(mi,d.[Database Backup End],SYSDATETIME()) % 60 as nvarchar),2) [Since Last Backup],i.[Database Backup Start],i.[Database Backup End], datediff(mi,i.[Database Backup Start],i.[Database Backup End]) as [Minutes Duration],
cast (datediff(hh,i.[Database Backup End],SYSDATETIME()) as nvarchar)+':'+ right ('000'+cast (datediff(mi,i.[Database Backup End],SYSDATETIME()) % 60 as nvarchar),2) [Since Last Backup],
l.[Log Backup Start],l.[Log Backup End] , datediff(ss,l.[Log Backup Start],l.[Log Backup End]) as seconds,
case when db.recovery_model_desc='SIMPLE' then 'SIMPLE RM' else cast (datediff(mi,L.[Log Backup End],SYSDATETIME()) as nvarchar) end [RPO]
from
(select name,recovery_model_desc from sys.databases where name<>'tempdb') db left outer join
(
SELECT sdb.Name AS DatabaseName, max(backup_start_date) as [Database Backup Start],MAX(bus.backup_finish_date) as [Database Backup End]
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where type='D'
and sdb.dbid<>2
GROUP BY sdb.Name,type) d on d.DatabaseName=db.name
left outer join
(SELECT sdb.Name AS DatabaseName, max(backup_start_date) as [Database Backup Start],MAX(bus.backup_finish_date) as [Database Backup End]
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where type='I'
and sdb.dbid<>2
GROUP BY sdb.Name,type) I
on d.databaseNAme=i.DatabaseName
left outer join
(SELECT sdb.Name AS DatabaseName, max(backup_start_date) as [Log Backup Start],MAX(bus.backup_finish_date) as [Log Backup End]
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where type='L'
and sdb.dbid<>2
GROUP BY sdb.Name,type) L
on d.databaseNAme=l.DatabaseName
GO
<\/pre>\n","protected":false},"excerpt":{"rendered":"Following statement can be used to see the last backups of all databases. You get information of last backup’s start time, end time, duration in minutes, hours since last backup both for full and log backups. SELECT db.name,db.recovery_model_desc,d.[Database Backup Start],d.[Database Backup End], datediff(mi,d.[Database Backup Start],d.[Database Backup End]) as [Minutes Duration],cast (datediff(hh,d.[Database Backup End],SYSDATETIME()) as nvarchar)+’:’+ […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[10],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-mssql","tag-backup"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[]}},"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=13"}],"version-history":[{"count":3,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/13\/revisions"}],"predecessor-version":[{"id":232,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/13\/revisions\/232"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}