Tuesday, February 26, 2013

Database Experts:Last backup and Last Backup restore Information

For Finding latest backup details of all databases.

SELECT 
   
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   
msdb.dbo.backupset.database_name
   
MAX(msdb.dbo.backupset.backup_finish_dateAS last_db_backup_date FROM   msdb.dbo.backupmediafamily 
   
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id msdb.dbo.backupset.media_set_id  WHERE  msdb..backupset.type 'D' --'L' for transaction logsGROUP BY
   
msdb.dbo.backupset.database_name  ORDER BY 
   
msdb.dbo.backupset.database_name



For Finding latest restore backup for all databases.

SELECT MAX(restore_date) AS LastRestore,COUNT(*) AS CountRestores ,destination_database_name
FROM msdb.dbo.restorehistory
GROUP BY destination_database_name
order by 1 Desc
 

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response