Have you recently taken your SQL Server backups ?
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.




