Get Last Full Backup Date

SET NOCOUNT ON

GO

DECLARE

@Counter SMALLINT,

@DBName SYSNAME,

@DB_BKPdate VARCHAR(100),

@Status VARCHAR(20),

@ServerName SYSNAME,

@Media_Set_ID VARCHAR(20),

@FilePath VARCHAR(1000),

@FileStatus INT,

@FileAvailable VARCHAR(20),

@BackupSize FLOAT

CREATE TABLE #Backup_Details

(

ServerName SYSNAME,

DatabaseName SYSNAME,

BkpDate VARCHAR(20) NULL,

BackupSize_in_MB VARCHAR(20),

Status VARCHAR(20),

FilePath VARCHAR(1000),

FileAvailable VARCHAR(20)

)

SELECT @ServerName = CAST(SERVERPROPERTY('ServerName')AS SYSNAME)

-- start looping through all databases

SELECT @Counter=MAX(dbid)

FROM MASTER..sysdatabases

WHILE @Counter > 0

BEGIN

/* Need to re-initialize all variables*/

SELECT

@DBName = NULL ,

@DB_BKPdate = NULL ,

@Media_Set_ID = NULL ,

@BackupSize = NULL ,

@FilePath = NULL ,

@FileStatus = NULL ,

@FileAvailable = NULL ,

@Status = NULL ,

@BackupSize = NULL

SELECT @DBName = name

FROM MASTER..sysdatabases

WHERE dbid = @Counter

SELECT @DB_BKPdate = MAX(backup_start_date)

FROM msdb..backupset

WHERE database_name = @DBName

AND TYPE='D'

SELECT

@Media_Set_ID = media_set_id,

@BackupSize = backup_size

FROM msdb..backupset

WHERE backup_start_date =

(

SELECT MAX(backup_start_date)

FROM msdb..backupset

WHERE database_name = @DBName

AND TYPE='D'

)

AND database_name = @DBName

AND TYPE='D'

SELECT @FilePath = physical_device_name

FROM msdb..backupmediafamily

WHERE media_set_id = @Media_Set_ID

EXEC MASTER..xp_fileexist @FilePath , @FileStatus OUT

IF @FileStatus = 1

SET @FileAvailable = 'Available'

ELSE

SET @FileAvailable = 'NOT Available'

IF (DATEDIFF(DAY,@DB_BKPdate,GETDATE()) > 7)

SET @Status = 'Warning'

ELSE

SET @Status = 'Healthy'

SET @BackupSize = (@BackupSize/1024)/1024

INSERT INTO #Backup_Details

(

ServerName,

DatabaseName,

BkpDate,

BackupSize_in_MB,

Status,

FilePath,

FileAvailable

)

SELECT

@ServerName,

@DBName,

@DB_BKPdate,

@BackupSize,

@Status,

@FilePath,

@FileAvailable

UPDATE #Backup_Details

SET status = 'Warning'

WHERE bkpdate IS NULL

SET @Counter = @Counter - 1

END

SELECT *

FROM #Backup_Details

WHERE databasename NOT IN ('tempdb','northwind','pubs')

ORDER BY databasename

DROP TABLE #Backup_Details

SET NOCOUNT OFF

GO

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DailyBackupCheck]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_DailyBackupCheck]

GO

CREATE PROC [dbo].[usp_DailyBackupCheck]

AS

/***************************************************************************

Purpose: To create a list of backups that are overdue.

Full, Differential and Log backups are considered.

An overdue status would be:

1a. Full Backup not taken for 24 hours where there is no Differential backup

1b. Full backup not taken for 7 days where there is has been Differential Backup within the last 24 hours

2. Differential backup not taken for the last 24 hours

3. Log backup not taken for the past 6 hours

This script will only work automatically for backup schemes that have performed at least one successful backup of each type required

This is an acceptible limitation, as when setting up/changing backups, one should manually check the first few cycles anyway

Author: ChillyDBA

History: 10 May 2012 - Initial Issue

****************************************************************************/

CREATE TABLE #BackupList1

(

DatabaseName SYSNAME,

BackupType CHAR,

LastBackupTaken DATETIME,

UserName VARCHAR(100)

)

CREATE TABLE #BackupList2

(

DatabaseName SYSNAME,

LastFullBackupTaken DATETIME,

LastDiffBackupTaken DATETIME,

LastLogBackupTaken DATETIME,

FullOverdue BIT DEFAULT 0,

DiffOverdue BIT DEFAULT 0,

LogOverdue BIT DEFAULT 0

)

CREATE TABLE ##MailBody

(

LineID INT IDENTITY(1,1),

LineText VARCHAR(1000)

)

DECLARE @Now DATETIME,

@Cmd VARCHAR(1000)

SELECT

@Now = GETDATE(),

@Cmd = ''

-- get raw backup details, but only consider the past 2 months.

-- if it has been worng for longer, then tough!

-- THis screens out DBs that might have changed backup type some time ago

INSERT #BackupList1

(

DatabaseName,

BackupType,

LastBackupTaken,

UserName

)

SELECT

T1.Name as DatabaseName,

t2.type,

MAX(T2.backup_finish_date) as LastBackUpTaken,

MAX(T2.user_name)as UserName

FROM sys.databases T1

LEFT OUTER JOIN msdb.dbo.backupset T2

ON T2.database_name = T1.name

WHERE T1.state_desc = 'ONLINE'

AND backup_finish_date > DATEADD(mm, -2, GETDATE())

GROUP BY T1.Name, t2.type

ORDER BY T1.Name, t2.type

INSERT #BackupList2

(

DatabaseName,

LastFullBackupTaken,

LastDiffBackupTaken,

LastLogBackupTaken

)

SELECT

x.DatabaseName,

MAX(x.LastFullBackupTaken),

MAX(x.LastDiffBackupTaken),

MAX(x.LastLogBackupTaken)

FROM

(

SELECT

DatabaseName AS DatabaseName,

LastBackupTaken AS LastFullBackupTaken,

NULL AS LastDiffBackupTaken,

NULL AS LastLogBackupTaken

FROM #BackupList1

WHERE BackupType = 'D'

UNION

SELECT

DatabaseName AS DatabaseName,

NULL AS LastFullBackupTaken,

LastBackupTaken AS LastDiffBackupTaken,

NULL AS LastLogBackupTaken

FROM #BackupList1

WHERE BackupType = 'I'

UNION

SELECT

DatabaseName AS DatabaseName,

NULL AS LastFullBackupTaken,

NULL AS LastDiffBackupTaken,

LastBackupTaken AS LastLogBackupTaken

FROM #BackupList1

WHERE BackupType = 'L'

) as x

GROUP BY x.DatabaseName

-- Identify states 1a and 1b

UPDATE #BackupList2

SET FullOverdue = 1

WHERE

(

((DATEDIFF(hh, LastFullBackupTaken, @Now) > 24) AND (LastDiffBackupTaken IS NULL))

OR

((DATEDIFF(hh, LastFullBackupTaken, @Now) > 168) AND (DATEDIFF(hh, LastDiffBackupTaken, @Now) <= 24))

)

-- Identify state 2

UPDATE #BackupList2

SET DiffOverdue = 1

WHERE

(

(DATEDIFF(hh, LastDiffBackupTaken, @Now) > 24)

)

-- Identify state 3

UPDATE #BackupList2

SET LogOverdue = 1

WHERE

(

(DATEDIFF(hh, LastLogBackupTaken, @Now) > 6)

)

/*

--Some test broken backups

update #BackupList2 set fulloverdue = 1 where databasename = 'msdb'

update ##BackupList2 set diffoverdue = 1 where databasename = 'msdb'

update #BackupList2 set logoverdue = 1 where databasename = 'model'

update #BackupList2 set fulloverdue = 1 , logoverdue = 1 where databasename = 'model'

update #BackupList2 set fulloverdue = 1 , diffoverdue = 1, logoverdue = 1 where databasename = 'master'

*/

INSERT ##MailBody(LineText)

VALUES ('The following database backups are overdue:') ;

INSERT ##MailBody(LineText)

VALUES ('') ;

INSERT ##MailBody(LineText)

SELECT

CHAR(9)

+ CHAR(9)

+ DatabaseName

+ REPLICATE(CHAR(9), (50 - DATALENGTH(DatabaseName) + 4)/8)

+ ' ==> ::'

+ CASE WHEN FullOverdue = 1 THEN ' FULL ::' ELSE '' END

+ CASE WHEN DiffOverdue = 1 THEN ' DIFF ::' ELSE '' END

+ CASE WHEN LogOverdue = 1 THEN ' LOG ::' ELSE '' END

+ ' <=='

FROM #BackupList2

WHERE CAST(FullOverdue AS INT) + CAST(DiffOverdue AS INT) + CAST(LogOverdue AS INT) > 0

SET @cmd = 'SELECT LineText FROM ##MailBody ORDER By LineID' ;

IF (SELECT COUNT(*) FROM ##MailBody) > 2

BEGIN

--select 'send mail'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = N'<profile name>',

@recipients = N'<user or group email>',

@subject = N'Daily Backup Status Check ' ,

@query = @cmd,

@query_result_header = 0

END

SELECT * FROM #BackupList1

SELECT * FROM #BackupList2

SELECT * FROM ##MailBody

DROP TABLE #BackupList1

DROP TABLE #BackupList2

DROP TABLE ##MailBody