Search This Blog

Wednesday 19 October 2011

Information on Backups



To check last backup details of all the databases use the following script. This script works on both SQL Server 2005 and SQL Server 2008


Select
db.name as 'Database_Name',
db.recovery_model_desc as 'Recovery_Model',
db.state_desc as 'State',
FB.LastBackupDate as 'LastFullBackup',
lB.LastBackupDate as 'LastLogBackup' ,
DIF.LastBackupDate as 'LastDiffBackup'
From sys.databases db
Cross Apply (Select max(backup_finish_date) as LastBackupDate from msdb.dbo.backupset where type= 'D' and database_name= db.name) FB
Cross Apply (Select max(backup_finish_date) as LastBackupDate from msdb.dbo.backupset where type= 'L' and database_name= db.name) LB
Cross Apply (Select max(backup_finish_date) as LastBackupDate from msdb.dbo.backupset where type= 'I' and database_name= db.name) dif

Order by
db.name