Blog Post

Easy Backup History Review Script

,

I often use these scripts to check on the backups of all databases in an unfamiliar SQL Server instance, regardless of the platform that is taking the backups (SQL maintenance plans, T-SQL scripts in an agent job, and third-party software).

The query sorts databases by the backup type and then the recovery model configured. Because the backups are grouped by database name, it shows the most recent full backup for each database, most recent differential if it exists, and then the most recent transaction log backup (but only for databases in FULL or BULK_LOGGED recovery models).

Most importantly, it will show the most recent transaction log backup for all databases in FULL or BULK_LOGGED recovery models, even if a transaction log backup has never been taken.

If any of the dates look out of place, too old or NULL, they will stand out right away.

  • The first two scripts are functionally equivalent. The first uses SQL 2005 system views (sys.databases) that are only appropriate if all databases are running SQL 2005 compatibility mode or higher. 
  • The second script is using SQL 2000 system views (sysdatabases), which are now deprecated.
  • The third script provides a granular list of all backup events by database, and is valid for SQL 2000 and up.
--sql2005 and above
select
database_Name
, backuptype
, d.recovery_model_desc
, BackupDate = MAX(BackupDate)
from sys.databases d
inner join
(
select distinct
database_name
, backuptype = case type WHEN 'D' then 'Database'
WHEN 'I' then 'Differential database'
WHEN 'L' then 'Transaction Log'
WHEN 'F' then 'File or filegroup'
WHEN 'G' then 'Differential file'
WHEN 'P' then 'Partial'
WHEN 'Q' then 'Differential partial' END
, BackupDate = MAX(backup_start_date)
from msdb.dbo.backupset bs
group by Database_name, type
UNION
select distinct
db_name(d.database_id)
, backuptype = 'Database'
, null
FROM master.sys.databases d
UNION
select distinct
db_name(d.database_id)
, backuptype = 'Transaction Log'
, null
FROM master.sys.databases d
where d.recovery_model_desc in ('FULL', 'BULK_LOGGED')

) a
on db_name(d.database_id) = a.database_name
group by database_name, backuptype, d.recovery_model_desc
order by backuptype, recovery_model_desc, BackupDate asc


 --sql 2000 and above
select distinct
database_name = d.name
, a.backuptype
, RecoveryModel = databasepropertyex(d.name, 'Recovery')
, BackupDate = Max(a.backup_start_date)
from master.dbo.sysdatabases d
left outer join
( select distinct
database_name
, backuptype = case type WHEN 'D' then 'Database'
WHEN 'I' then 'Differential database'
WHEN 'L' then 'Transaction Log'
WHEN 'F' then 'File or filegroup'
WHEN 'G' then 'Differential file'
WHEN 'P' then 'Partial'
WHEN 'Q' then 'Differential partial' END
, backup_start_date = MAX(backup_start_date)
from msdb.dbo.backupset bs
group by Database_name, type
UNION
select distinct
d.name
, backuptype = 'Database'
, null
FROM master.dbo.sysdatabases d
UNION
select distinct
d.name
, backuptype = 'Transaction Log'
, null
FROM master.dbo.sysdatabases d
where databasepropertyex(d.name, 'Recovery') in ('FULL', 'BULK_LOGGED')

) a
on d.name = a.database_name
group by d.name , backuptype , databasepropertyex(d.name, 'Recovery')
order by backuptype, RecoveryModel, BackupDate asc
--granular backup history
select distinct
database_name
, type
, backuptype = case type WHEN 'D' then 'Database'
WHEN 'I' then 'Differential database'
WHEN 'L' then 'Transaction Log'
WHEN 'F' then 'File or filegroup'
WHEN 'G' then 'Differential file'
WHEN 'P' then 'Partial'
WHEN 'Q' then 'Differential partial' END
, BackupDate = backup_start_date
, database_backup_lsn
, bf.physical_device_name
from msdb.dbo.backupset bs
left outer join msdb.dbo.[backupmediafamily] bf
on bs.[media_set_id] = bf.[media_set_id]
--where database_name = 'databasenamehere'
--and type in ('d', 'i')

order by database_name asc, backupdate desc

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating