Thursday, May 30, 2013

Find Backup history for one week


use msdb
go
SELECT
SERVERPROPERTY('Servername') AS Server_Name,
bs.Database_name,
bs.Backup_start_date,
bs.Backup_finish_date,
bs.Expiration_date,
CASE bs.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential '
WHEN 'L' THEN 'Log'
END AS Backup_type,
(bs.backup_size/1048576) AS Backup_size_MB ,bmf.Logical_device_name,
bmf.Physical_device_name FROM backupmediafamily AS bmf INNER JOIN backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY bs.database_name,
bs.backup_finish_date
Reference :http://msdn.microsoft.com/en-us/library/ms186299.aspx