When was a database backed up?

A common requirement is to be able to report on the backup status of each database in an instance.  There are of course many tools out there that do just this, but what if your organisation has not purchased one of these tools, or the tools are not installed, for instance in a development environment?

Here I present a stored procedure that will produce a table summarising the status of each database in an instance based on the type of backup that you are interested in.  It takes an input parameter called @aintSPMode which accepts the following values:

1 – If details of full backups are required

2 – If details of transactional backups are required

4 – If details of differential backups are required

These values can be added together to return multiple tables if required.

   1: /**************************************************

   2: Name: dba_GetBackupHistory

   3: Written by: Al Eardley

   4: Date: December 2010

   5: Purpose:

   6: This script creates a stored procedure that returns the dates of the last backups of every database in an instance

   7: 

   8: Parameters:

   9: @aintSPMode changes the results that are returned:

  10: 1 - details of full backup

  11: 2 - details of transaction backup

  12: 4 - details of differential backup

  13: 

  14: Add the values together to get multiple output tables

  15: 

  16: Execution:

  17: EXEC dbo.dba_GetBackupHistory @aintSPMode = 1

  18: 

  19: **************************************************/

  20:  

  21: /**************************************************

  22: ** Initialise Script

  23: **************************************************/

  24: PRINT 'dbo.dba_GetBackupHistory'

  25:  

  26: SET ANSI_NULLS ON

  27: SET NOCOUNT ON

  28:  

  29: /**************************************************

  30: ** Delete SP

  31: **************************************************/

  32: IF OBJECT_ID('dbo.dba_GetBackupHistory') IS NOT NULL

  33: BEGIN

  34:     PRINT CHAR(9) + 'dropping'

  35:     DROP PROCEDURE dbo.dba_GetBackupHistory

  36: END

  37: GO

  38:  

  39: /**************************************************

  40: ** Create SP

  41: **************************************************/

  42: CREATE PROCEDURE dbo.dba_GetBackupHistory (

  43:     @aintSPMode                            int                 = 0

  44: ,   @aintErrorID                        int                    = 0 OUTPUT

  45: ) AS

  46:  

  47: SET NOCOUNT ON

  48: SET QUOTED_IDENTIFIER ON

  49:  

  50: /**************************************************

  51: Declare default variables

  52: **************************************************/

  53: DECLARE

  54:     @intErrorID                int = 0

  55: ,    @strErrorDesc            varchar(256) = 'No Error'

  56: ,    @intReturnValue            int = 0

  57: ,    @intRowcount            int = 0

  58: ,    @strSPName                 varchar(128) = 'dbo.dba_GetBackupHistory'

  59:  

  60: /**************************************************

  61: ** Validate input parameters

  62: **************************************************/

  63: IF @aintSPMode IS NULL

  64: BEGIN

  65:     SET @aintSPMode = 0

  66: END

  67:  

  68: IF @aintSPMode NOT BETWEEN 1 AND 7

  69: BEGIN

  70:     SET @intReturnValue = -1

  71:     SET @strErrorDesc    = '@aintSPMode input should be one of the following values:

  72: 1 - Returns details of full backups

  73: 2 - Returns details of transaction backups

  74: 4 - Returns details of differential backups

  75: Any combination of the above values for multiple results'

  76:     GOTO ErrorHandler

  77: END

  78:  

  79: /**************************************************

  80: Declare local variables

  81: **************************************************/

  82: DECLARE

  83:     @sqlSelect                nvarchar(2048)

  84:  

  85: DECLARE    @tblDatabases TABLE (

  86:     DatabaseID                    smallint IDENTITY(1,1) NOT NULL PRIMARY KEY

  87: ,    DatabaseName                sysname    NOT NULL

  88: ,    RecoveryModel                tinyint    NOT NULL

  89: ,    MostRecentFullBackupDate    datetime

  90: ,    MostRecentDiffBackupDate    datetime

  91: ,    MostRecentTranBackupDate    datetime

  92: ,    ProcessingIsComplete        bit DEFAULT 0

  93: )

  94:  

  95: /**************************************************

  96: Populate @tblDatabases

  97: **************************************************/

  98: BEGIN TRY

  99:     INSERT INTO @tblDatabases (

 100:         DatabaseName

 101:     ,    RecoveryModel

 102:     ) SELECT

 103:         SD.name

 104:     ,    SD.recovery_model

 105:     FROM sys.databases SD WITH(NOLOCK)

 106:     ORDER BY

 107:         SD.name

 108: END TRY

 109: BEGIN CATCH

 110:     SET @strErrorDesc = 'Fatal Error: Populate @tblDatabases: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()

 111:     SET @intReturnValue = -1

 112:     GOTO ErrorHandler

 113: END CATCH

 114:  

 115: /**************************************************

 116: Update the MostRecentFullBackupDate

 117: **************************************************/

 118: IF @aintSPMode & 1 = 1

 119: BEGIN

 120:     BEGIN TRY

 121:         ;WITH cLastFullBackup (

 122:                 DatabaseName

 123:             ,    MostRecentFullBackupDate

 124:             ) AS (

 125:             SELECT

 126:                 BS.database_name

 127:             ,    MAX(BS.backup_finish_date)

 128:             FROM msdb.dbo.backupset BS WITH(NOLOCK)

 129:             WHERE

 130:                 BS.type = 'D'

 131:             AND

 132:                 CONVERT(datetime, BS.backup_start_date, 102) >= (GETDATE() - 7)

 133:             GROUP BY

 134:                 BS.database_name

 135:         )

 136:         UPDATE @tblDatabases

 137:         SET

 138:             MostRecentFullBackupDate = cLFB.MostRecentFullBackupDate

 139:         FROM @tblDatabases tD

 140:             JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)

 141:     END TRY

 142:     BEGIN CATCH

 143:         SET @strErrorDesc = 'Fatal Error: Update the MostRecentFullBackupDate: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()

 144:         SET @intReturnValue = -1

 145:         GOTO ErrorHandler

 146:     END CATCH

 147: END

 148:  

 149: /**************************************************

 150: Update the MostRecentTranBackupDate

 151: **************************************************/

 152: IF @aintSPMode & 2 = 2

 153: BEGIN

 154:     BEGIN TRY

 155:         ;WITH cLastFullBackup (

 156:                 DatabaseName

 157:             ,    MostRecentTranBackupDate

 158:             ) AS (

 159:             SELECT

 160:                 BS.database_name

 161:             ,    MAX(BS.backup_finish_date)

 162:             FROM msdb.dbo.backupset BS WITH(NOLOCK)

 163:             WHERE

 164:                 BS.type = 'L'

 165:             AND

 166:                 CONVERT(datetime, BS.backup_start_date, 102) >= (GETDATE() - 7)

 167:             GROUP BY

 168:                 BS.database_name

 169:         )

 170:         UPDATE @tblDatabases

 171:         SET

 172:             MostRecentTranBackupDate = cLFB.MostRecentTranBackupDate

 173:         FROM @tblDatabases tD

 174:             JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)

 175:     END TRY

 176:     BEGIN CATCH

 177:         SET @strErrorDesc = 'Fatal Error: Update the MostRecentFullBackupDate: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()

 178:         SET @intReturnValue = -1

 179:         GOTO ErrorHandler

 180:     END CATCH

 181: END

 182:  

 183: /**************************************************

 184: Update the MostRecentDiffBackupDate

 185: **************************************************/

 186: IF @aintSPMode & 4 = 4

 187: BEGIN

 188:     BEGIN TRY

 189:         ;WITH cLastFullBackup (

 190:                 DatabaseName

 191:             ,    MostRecentDiffBackupDate

 192:             ) AS (

 193:             SELECT

 194:                 BS.database_name

 195:             ,    MAX(BS.backup_finish_date)

 196:             FROM msdb.dbo.backupset BS WITH(NOLOCK)

 197:             WHERE

 198:                 BS.type = 'I'

 199:             AND

 200:                 CONVERT(datetime, BS.backup_start_date, 102) >= (GETDATE() - 7)

 201:             GROUP BY

 202:                 BS.database_name

 203:         )

 204:         UPDATE @tblDatabases

 205:         SET

 206:             MostRecentDiffBackupDate = cLFB.MostRecentDiffBackupDate

 207:         FROM @tblDatabases tD

 208:             JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)

 209:     END TRY

 210:     BEGIN CATCH

 211:         SET @strErrorDesc = 'Fatal Error: Update the MostRecentDiffBackupDate: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()

 212:         SET @intReturnValue = -1

 213:         GOTO ErrorHandler

 214:     END CATCH

 215: END

 216:  

 217: /**************************************************

 218: Select details of Databases

 219: **************************************************/

 220: BEGIN TRY

 221:     IF @aintSPMode & 1 = 1

 222:     BEGIN

 223:         SELECT

 224:             CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server

 225:         ,    tD.DatabaseName

 226:         ,    tD.MostRecentFullBackupDate

 227:         ,    CASE

 228:                 WHEN DATEDIFF(HOUR, tD.MostRecentFullBackupDate, GETDATE()) > 24 THEN '> 24 hrs'

 229:                 WHEN DATEDIFF(WEEK, tD.MostRecentFullBackupDate, GETDATE()) > 1 THEN '> 1 week'

 230:                 WHEN DATEDIFF(MONTH, tD.MostRecentFullBackupDate, GETDATE()) > 1 THEN '> 1 month'

 231:                 WHEN tD.MostRecentFullBackupDate IS NULL THEN 'NEVER'

 232:                 ELSE 'Safe'

 233:             END AS MostRecentFullBackupAge

 234:         FROM @tblDatabases tD

 235:     END

 236:  

 237:     IF @aintSPMode & 2 = 2

 238:     BEGIN

 239:         SELECT

 240:             CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server

 241:         ,    tD.DatabaseName

 242:         ,    tD.MostRecentTranBackupDate

 243:         ,    CASE

 244:                 WHEN DATEDIFF(HOUR, tD.MostRecentTranBackupDate, GETDATE()) > 24 THEN '> 24 hrs'

 245:                 WHEN DATEDIFF(WEEK, tD.MostRecentTranBackupDate, GETDATE()) > 1 THEN '> 1 week'

 246:                 WHEN DATEDIFF(MONTH, tD.MostRecentTranBackupDate, GETDATE()) > 1 THEN '> 1 month'

 247:                 WHEN tD.MostRecentTranBackupDate IS NULL THEN 'NEVER'

 248:                 ELSE 'Safe'

 249:             END AS MostRecentTranBackupAge

 250:         FROM @tblDatabases tD

 251:         WHERE

 252:             tD.RecoveryModel = 1

 253:     END

 254:  

 255:     IF @aintSPMode & 4 = 4

 256:     BEGIN

 257:         SELECT

 258:             CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server

 259:         ,    tD.DatabaseName

 260:         ,    tD.MostRecentDiffBackupDate

 261:         ,    CASE

 262:                 WHEN DATEDIFF(HOUR, tD.MostRecentDiffBackupDate, GETDATE()) > 24 THEN '> 24 hrs'

 263:                 WHEN DATEDIFF(WEEK, tD.MostRecentDiffBackupDate, GETDATE()) > 1 THEN '> 1 week'

 264:                 WHEN DATEDIFF(MONTH, tD.MostRecentDiffBackupDate, GETDATE()) > 1 THEN '> 1 month'

 265:                 WHEN tD.MostRecentDiffBackupDate IS NULL THEN 'NEVER'

 266:                 ELSE 'Safe'

 267:             END AS MostRecentTranDiffAge

 268:         FROM @tblDatabases tD

 269:         WHERE

 270:             tD.RecoveryModel = 1

 271:     END

 272: END TRY

 273: BEGIN CATCH

 274:     SET @strErrorDesc = 'Fatal Error: Select details of Databases: ' + CAST(ERROR_LINE() AS nvarchar) + ' - ' + CAST(ERROR_NUMBER() AS nvarchar) + ' - ' + ERROR_MESSAGE()

 275:     SET @intReturnValue = -1

 276:     GOTO ErrorHandler

 277: END CATCH

 278:  

 279: /**************************************************/

 280: ErrorHandler:

 281: /**************************************************/

 282: PRINT @strErrorDesc

 283:  

 284: /**************************************************

 285: Return

 286: **************************************************/

 287: RETURN @intReturnValue

 288:  

 289: GO

The table that is used as the source of the information is msdb.dbo.backupset which, is defined in “Books Online” as:

Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

There are many ways that this procedure can be enhanced and in particular, used as the basis of reporting or alerting and I shall cover these uses in later posts.