26 November 2009 14:34 david.castro

Where are my database backups?

Don’t you ever ask yourself, are my maintenance back up plans working?

Today I’ve been looking at the database backup history information of my servers to make sure that I have my back covered. So using our loved msdb system database tables I have done a couple of useful script which I have schedule as  jobs to email the results on a regular basis.

The main tables I’ve used are:

MSDB.DBO.BACKUPMEDIAFAMILY - http://msdn.microsoft.com/en-us/library/ms190284(SQL.90).aspx

MSDB.DBO.BACKUPSET - http://msdn.microsoft.com/en-us/library/ms186299(SQL.90).aspx

My 2 simple scripts are:

1. Identify Database Backups for all db’s on the last 7 days:

   1: SELECT 
   2:         CONVERT(VARCHAR(64), SERVERPROPERTY('SERVERNAME')) AS SERVER,
   3:         BST.DATABASE_NAME, 
   4:         BST.BACKUP_START_DATE, 
   5:         BST.BACKUP_FINISH_DATE,
   6:         BST.EXPIRATION_DATE,
   7:         CASE BST.TYPE 
   8:                 WHEN 'D' THEN 'FULL'  
   9:                 WHEN 'I' THEN 'DIFFERENTIAL' 
  10:                 WHEN 'L' THEN 'LOG'
  11:                 WHEN 'F' THEN 'FILE / FILEGROUP'
  12:                 WHEN 'G' THEN 'DIFFERENTIAL FILE'
  13:                 WHEN 'P' THEN 'PARTIAL'
  14:                 WHEN 'Q' THEN 'DIFFERENTIAL PARTIAL'
  15:         END AS TYPE, 
  16:         BST.BACKUP_SIZE, 
  17:         BMF.LOGICAL_DEVICE_NAME, 
  18:         BMF.PHYSICAL_DEVICE_NAME,  
  19:         BST.NAME AS BACKUPSET_NAME,
  20:         BST.DESCRIPTION
  21: FROM   MSDB.DBO.BACKUPMEDIAFAMILY  AS BMF
  22:             INNER JOIN MSDB.DBO.BACKUPSET AS BST 
  23:                 ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 
  24: WHERE  BST.BACKUP_START_DATE >= GETDATE() - 7 
  25: ORDER BY 
  26:    BST.DATABASE_NAME,
  27:    BST.BACKUP_FINISH_DATE

2. Identify the most recent full (type D) Backup for each DB with detail information about the backup:

   1: SELECT 
   2:        BMFBST.SERVERNAME, 
   3:        BMFBST.LAST_BACKUP_DATE, 
   4:        BMFBSTD.BACKUP_START_DATE, 
   5:        BMFBSTD.EXPIRATION_DATE,
   6:        BMFBSTD.BACKUP_SIZE, 
   7:        BMFBSTD.LOGICAL_DEVICE_NAME, 
   8:        BMFBSTD.PHYSICAL_DEVICE_NAME,  
   9:        BMFBSTD.BACKUPSET_NAME,
  10:        BMFBSTD.DESCRIPTION
  11: FROM
  12:    (
  13: /*
  14:     List of the most recent full backups for each database    
  15: */
  16:     SELECT 
  17:          CONVERT(VARCHAR(64),SERVERPROPERTY('SERVERNAME')) AS SERVERNAME
  18:         ,BST.DATABASE_NAME
  19:         ,MAX(BST.BACKUP_FINISH_DATE) AS LAST_BACKUP_DATE
  20:     FROM   MSDB.DBO.BACKUPMEDIAFAMILY BMF
  21:            INNER JOIN MSDB.DBO.BACKUPSET BST
  22:                 ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 
  23:     WHERE  BST.TYPE = 'D'
  24:     GROUP BY  BST.DATABASE_NAME 
  25:    ) AS BMFBST
  26:    LEFT JOIN 
  27:    (
  28: /*
  29:     Detail information about back to join with the previous list
  30: */
  31:     SELECT  
  32:        CONVERT(VARCHAR(64), SERVERPROPERTY('SERVERNAME')) AS SERVER,
  33:        BST.DATABASE_NAME, 
  34:        BST.BACKUP_START_DATE, 
  35:        BST.BACKUP_FINISH_DATE,
  36:        BST.EXPIRATION_DATE,
  37:        BST.BACKUP_SIZE, 
  38:        BMF.LOGICAL_DEVICE_NAME, 
  39:        BMF.PHYSICAL_DEVICE_NAME,  
  40:        BST.NAME AS BACKUPSET_NAME,
  41:        BST.DESCRIPTION
  42:     FROM   MSDB.DBO.BACKUPMEDIAFAMILY AS BMF
  43:             INNER JOIN MSDB.DBO.BACKUPSET BST
  44:                 ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 
  45:         WHERE  BST.TYPE = 'D'
  46:    ) AS BMFBSTD
  47:    ON BMFBST.SERVERNAME = BMFBSTD.[SERVER] AND 
  48:       BMFBST.DATABASE_NAME = BMFBSTD.[DATABASE_NAME] AND 
  49:       BMFBST.LAST_BACKUP_DATE = BMFBSTD.[BACKUP_FINISH_DATE]
  50: ORDER BY 
  51:    BMFBST.DATABASE_NAME

I hope you find these useful,

 

Cheers,

 

@dcastrogavino

 

Comments

No Comments