All you need to know about your backups and Database Mirror Status

This script shows all the backup status information as well as the current database mirroring status (if enabled)


- Database Name
- Last Full backup
- Last Transaction Log backup
- Last Restore
- Recovery Model and Compatibility Level
- Database Mirror
   - Curent Role
   - Status
- Transaction Log usage (Percentage)

-- MAIN SCRIPT

--// SQL Version

USE

msdb

GO

-- Last Full Backup Info

SELECT

D.name as Database_Name

--,D.database_id as dbid

,B.last_backup_finish_date as [Last Full Backup Finish Date]

,TR.last_TRLog_backup_finish_date as [Last T.Log Backup Finish Date] --Transaction Log

,R.last_restore_date as [Last Restore Date]

,D.recovery_model_desc+' ('+cast(D.compatibility_level as varchar(3))+')' as [Recovery Model / Compatibility Level]

,DM.mirroring_role_desc+'('+DM.mirroring_state_desc+')' as [DBMirror Info]

, LOG.cntr_value as [% T.Log Used]

FROM sys.databases D

LEFT JOIN (

SELECT

BS.database_name

--,BS.backup_start_date

,max(BS.backup_finish_date) as last_backup_finish_date

--,BS.backup_size

FROM

msdb.dbo.backupset BS (NOLOCK)

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

AND BS.type='D'

GROUP BY

BS.database_name

--ORDER BY

-- BS.backup_start_date ASC

) B on D.name=B.database_name

LEFT JOIN (

SELECT

BS.database_name

,max(BS.backup_finish_date) as last_TRLog_backup_finish_date

FROM

msdb.dbo.backupset BS (NOLOCK)

INNER JOIN

msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)

AND BS.type='L'

GROUP BY

BS.database_name

) TR on D.name=TR.database_name

LEFT JOIN (

SELECT

rh.destination_database_name,

max(rh.restore_date) as last_restore_date

FROM

msdb.dbo.restorehistory rh (NOLOCK)

INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id

WHERE

BS.type= 'D' AND

RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

GROUP BY

rh.destination_database_name

-- ORDER BY

-- max(rh.restore_date) desc

) R on D.name=R.destination_database_name

LEFT JOIN

msdb.sys.database_mirroring dm (nolock) on d.database_id=dm.database_id

LEFT JOIN (

select object_name,instance_name,cntr_value from sys.dm_os_performance_counters

where object_name = 'SQLServer:Databases'

--and instance_name = 'Interlink_Main90'

and counter_name in ('Percent Log Used')

--order by cntr_value desc

) LOG on d.name=LOG.instance_name

order by

--D.name asc,

R.last_restore_date desc

GO