Tag Archives: backups

Database information (online, readonly, backed up, etc.)

Have you ever noticed any inconsistencies in the various states of your databases?  Online? Read_only? Mirrored?  Not backed up?!?

I wrote a brief script that I could run on my central management server.  This script provides database information relating to the status of:  online, read only, mirrored, backed up locally.

/*

Gary Gwyn
1/27/14

This query provides information for all databases on a server:
Name: database name
Status: online or offline
ReadOnly: yes or no
Mirrored: yes or no
BackedUpLocally: yes or no (1 Full backup in past 24 hours)

*******************************/
SELECT db.NAME AS 'Name'
 ,db.state_desc AS 'Status'
 ,CASE
 WHEN db.is_read_only = 0
 THEN ''
 ELSE 'YES'
 END AS 'ReadOnly'
 ,CASE
 WHEN m.mirroring_state IS NOT NULL
 THEN 'YES'
 ELSE ''
 END AS 'Mirrored'
 ,CASE
 WHEN db.NAME IN (
 SELECT DISTINCT database_name
 FROM msdb..backupset
 WHERE backup_finish_date > GETDATE() - 1
 AND type = 'D'
 )
 THEN 'YES'
 ELSE ''
 END AS 'BackedUpLocally'
FROM sys.databases db
INNER JOIN sys.database_mirroring m ON db.database_id = m.database_id

Advertisements
Tagged , , , ,