Posts see when the last backup was taken on a SQL Server
Post
Cancel

see when the last backup was taken on a SQL Server

snap069

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--https://gist.github.com/anonymous/e925d700ad70b52ef57c
select
	bs.database_name
	,bs.backup_finish_date
	,bs.backup_size/1024.0/1024.0 [backup_size_mb]
	,bs.compressed_backup_size/1024.0/1024.0 [compressed_size_mb]
	,datediff(ss,backup_start_date,backup_finish_date) [backup_time]
	,((bs.backup_size/1024.0/1024.0)/(datediff(ss,backup_start_date,backup_finish_date)+1)) [mb_per_second]
	,1.0-(bs.compressed_backup_size*1.0/bs.backup_size) [compression_ratio]
	,bm.physical_device_name
from
	msdb.dbo.backupset bs
	join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
	join (select database_name
			,max(backup_finish_date) last_backup
		  from msdb.dbo.backupset
		  where type = 'D'
		  group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'

origin - http://www.pipiscrew.com/?p=3489 sql-see-when-the-last-backup-was-taken-on-a-sql-server

This post is licensed under CC BY 4.0 by the author.
Contents

Trending Tags