SET NOCOUNT ON;
DECLARE @Results TABLE (
ServerName VARCHAR(100) NULL,
DatabaseName VARCHAR(100) NULL,
RecoveryModel VARCHAR(100) NULL,
LastFullBkupTime DATETIME NULL,
DaysSinceLastFull INT NULL,
DaysSinceLastDiff INT NULL,
HoursSinceLastLogBkup INT,
DBStatus VARCHAR (100) NULL,
BkupFile VARCHAR(1000) NULL,
Media INT
)
/* Get Server and database names. */
INSERT @Results(ServerName,DatabaseName)SELECT CONVERT(VARCHAR,SERVERPROPERTY('ServerName')),a.name
FROM master..sysdatabases a
WHERE a.name <> 'tempdb'
/* Last full bkup time and media. */
UPDATE @ResultsSET
LastFullBkupTime = b.backup_start_date,
DaysSinceLastFull = DATEDIFF(dd,b.backup_start_date,GETDATE()),
Media = b.media_SET_id
FROM @Results a,(
SELECT database_name, MAX(media_SET_id)media_SET_id, MAX(backup_start_date) backup_start_date
FROM msdb..backupset
WHERE TYPE = 'D'
GROUP BY database_name) b
WHERE
a.DatabaseName = b.database_name
/* Database status */
UPDATE @Results SET DBStatus = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Status'))
/* Recovery model */
UPDATE @Results SET RecoveryModel = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Recovery'))
/* Backup file location. */
UPDATE dSET d.BkupFile = b.physical_device_name
FROM @Results d, msdb..backupmediafamily b
WHERE d.Media = b.media_SET_id
/* Days since last diff bkup. */
UPDATE d
SET d.DaysSinceLastDiff = DATEDIFF(dd,b.backup_finish_date,GETDATE())
FROM @Results d, (
SELECT database_name,MAX(backup_finish_date) backup_finish_date
FROM msdb..backupset
WHERE TYPE = 'I'
GROUP BY database_name) b
WHERE d.DatabaseName = b.database_name
/* Hours since last log dump. */
UPDATE d SET d.HoursSinceLastLogBkup = DATEDIFF(hh,b.backup_finish_date,GETDATE())
FROM @Results d, (
SELECT database_name,MAX(backup_finish_date) backup_finish_date
FROM msdb..backupset
WHERE TYPE ='L'
GROUP BY database_name) b
WHERE d.DatabaseName = b.database_name
AND d.RecoveryModel <> 'SIMPLE'
/* Bring it back for review. */
SELECT ServerName,
DatabaseName,
RecoveryModel,
LastFullBkupTime,
DaysSinceLastFull,
DaysSinceLastDiff,
HoursSinceLastLogBkup,
DBStatus,
BkupFile,
Media
FROM
@Results
SET NOCOUNT OFF;
No comments:
Post a Comment