Tuesday, February 26, 2013

Database Experts:Last backup and Last Backup restore Information

For Finding latest backup details of all databases.

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
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 

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