Posts

Showing posts from 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_date )  AS  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 logs GROUP 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