Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - Get Last Full Backup Date

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:  Unknown  (extended version by ChillyDBA)
Date:     17 Feb 2012

Description

 This code retrieves a list of the last time each database on a server had a full backup. 
If the backup was taken over 7 days ago, then the status column will show 'WARNING'
If the backup file is not available (either not present or no access granted under the current security context), then Not Available will be returned.
Also, if the backup was performed by an external product, the file will also likely show as not available  eg with Veritas NetBackup, the file name will take the form VNBxxxxxx and will show as not available. 
 
I also created an enhanced version of the code for use in an environment where backups are out of my direct control, and currently 'in between owners', in a domain setup where there is little in the way of coherent centralised monitoring.  It allowed me to get a heads up if the Enterprise Backup software blew a gasket.
  • Extended to encompass TXLog Backups
  • Includes differential backups
  • Can email out the results
Details of the operating parameters can be found in the code comments
 
Note that both pieces of code will only work using backup history metadata that has not been subject to cleardown/archiving.

Code

 

Full backups only:

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


Full and TXLog Backups with email alert:    

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





 
ċ
Get Latest backup for Every Database Extended.sql
(5k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
ċ
Get Latest backup for Every Database.sql
(3k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments