Monday, October 10, 2011

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




1 comment:

  1. That pretty good syntax, thank you. Keep rock, sql dude.

    ReplyDelete