Friday, February 15, 2013

Backup Details for Older Days


How to find details of older backups available in the server for all the databases

Here is the script that can be used to find out the backup details for last 7 days for all the databases in a server.

SELECT    CONVERT(CHAR(100),
SERVERPROPERTY('Servername')) AS Server,  
msdb.dbo.backupset.database_name,   
msdb.dbo.backupset.backup_start_date,   
msdb.dbo.backupset.backup_finish_date,  
msdb.dbo.backupset.expiration_date,  
CASE msdb..backupset.type      WHEN 'D' THEN 'Database'     
WHEN 'L' THEN 'Log'   
END AS backup_type,   
msdb.dbo.backupset.backup_size,   
msdb.dbo.backupmediafamily.logical_device_name,   
msdb.dbo.backupmediafamily.physical_device_name,    
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY     msdb.dbo.backupset.database_name,    msdb.dbo.backupset.backup_finish_date

No comments:

Post a Comment