Table of contents
Available SQL Server backups
Have you recently taken your SQL Server backups ? Where I work we do full database backup on disk with T-SQL BACKUP command. Those backup files are then put on tape using Tivoli Storage Manager (TSM).
The below T-SQL will list backups that have been taken, or not (backup_start_date column will be NULL), for all your databases (outer join) within the last 10 days:
SELECT e.name AS database_name, d.backup_start_date, d.backup_finish_date, DATEDIFF(mi,d.backup_start_date,d.backup_finish_date) AS "backup duration (mi)", d.name, d.physical_device_name, CAST(ROUND(d.backup_size/(1024*1024),0) AS INT) AS "backup size (MB)", d.is_compressed, d.recovery_model FROM ( SELECT a.backup_set_id, a.name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.database_name, a.recovery_model, b.is_compressed, c.physical_device_name FROM msdb.dbo.backupset a, msdb.dbo.backupmediaset b, msdb.dbo.backupmediafamily c WHERE a.media_set_id = b.media_set_id AND b.media_set_id = c.media_set_id AND a.type = 'D' AND a.backup_start_date > GETDATE()-10 ) d RIGHT OUTER JOIN sys.databases e ON d.database_name = e.name ORDER BY d.backup_set_id DESC; |
Where type can take below value:
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.
References
