A Script A Day - Day 27 - Missing Database Backups

Today’s script is one I have used many times in the past to check for missing backups.  The script will return the database name and the last full backup date of all databases that are in the FULL recovery model and have not had a FULL database backup in the last 24 Hours.

/*

      -----------------------------------------------------------------

      Missing Database Backups

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE msdb;

GO

-- Create temporary table for database names

IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL

      DROP TABLE #Database ;

CREATE TABLE #Database

(

      ID INT IDENTITY (1,1),

      DatabaseName VARCHAR(255)

);

GO

-- Declare variables

DECLARE @Date DATETIME

-- Set variables

SET @Date = GETDATE()-1

-- Get database in FULL recovery WITH a FULL backup in the last 24 hours

INSERT INTO #Database

SELECT DISTINCT

      database_name

FROM

      msdb.dbo.backupset

WHERE

      recovery_model = 'FULL'

      AND [type] = 'D'

      AND backup_finish_date > @Date;

-- Get databases in FULL recovery without a FULL backup in the last 24 hours

SELECT

      b.database_name AS DatabaseName,

      MAX(b.backup_finish_date) AS LastFullBackup

FROM

      msdb.dbo.backupset b

WHERE

      b.database_name NOT IN (SELECT DatabaseName FROM #Database)

      AND b.recovery_model = 'FULL'

      AND b.[type] = 'D'

      AND b.backup_finish_date < @Date

GROUP BY b.database_name

ORDER BY b.database_name ASC;

GO

Enjoy!

Chris