How Long has it been since that Database was Backed up?

I suppose you backup your databases. I do, at least most of them. Scratch doesn't get backed up, and I don't usually bother backing up Northwind or Pubs.

For development databases I often use a database maintenance plan to backup databases to disk. Production databases should get backed up to tape or some offsite medium. Just in case the building burns to the ground.

Of course, "The best laid plans ..." you know the rest. Database backups don't always happen. This week's UDF is a little diagnostic tool that shows the last backup date and days since each database has been backed up.

There are a few interesting clauses of SQL that I'd like to point out. The first is the tables where we find the list of databases and the backup information. The list of databases must be obtained from master..sysdatabases. Backup information is in msdb.dbo.backupset.

Also pretty interesting is the clauses:

                            , (SELECT d.[GetDate] 
                                from Function_Assist_GETDATE d)

There are a few things to note about this statement. This SELECT statement provides the argument for an expression right smack in the middle of the select list. This feature is pretty handy but it's easy to forget that you can use a SELECT in an expression. The other thing to note is the use of the view FUNCTION_Assist_GETDATE. You'll need to create it before creating the UDF. It's required because SQL Server won't allow the use of the getdate function in a UDF. Using the view instead is a hack to get around that restriction. I wrote more about his in Volume 1 Issue #2. Here's the script to create the view:

CREATE VIEW Function_Assist_GETDATE

/********************************************************
*
* A view to return one row, with one column, the current 
* date/time from the built-in function GETDATE().  This 
* view allows a UDF to bypass the restriction on access to 
* the non-deterministic getdate() function.
*
* Attribution: Based on a newsgroup posting in by Mikhail 
*   Berlyant in microsoft.public.sqlserver.programming 
* 
* Common Usage:
DECLARE @dtVar datetime
select @dtVAr = [GetDate] from Function_Assist_GETDATE
**********************************************************/

AS 
    SELECT getdate() as [GetDate]

GO

GRANT SELECT on Function_Assist_GETDATE to PUBLIC
GO

Next, here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_DB_BackupDate (

) RETURNS TABLE
/* 
* Returns a table of databases with the days since their
* last backup and their last backup date.
*
* Example:
select * from udf_db_backupdate()
*      
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #31 07/20/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/

AS RETURN

SELECT 	TOP 100 PERCENT WITH TIES
       B.name as Database_Name
     , ISNULL(STR(ABS(DATEDIFF(day
                            , (SELECT d.[GetDate] 
                                from Function_Assist_GETDATE d)
                            , MAX(Backup_finish_date))))
              , 'NEVER') as DaysSinceLastBackup
     , ISNULL(Convert(char(10), MAX(backup_finish_date), 101)
              , 'NEVER') as LastBackupDate
    FROM master.dbo.sysdatabases B 
        LEFT OUTER JOIN msdb.dbo.backupset A 
            ON A.database_name = B.name AND A.type = 'D' 
    GROUP BY B.Name 
    ORDER BY B.name

GO
 
GRANT SELECT on dbo.udf_DB_BackupDate to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's run this on my development system. I've reinstalled everything from scratch just a week ago so there haven't been many backups.

select * from udf_db_backupdate()
GO

(Results)
Database_Name   DaysSinceLastBackup LastBackupDate 
--------------- ------------------- -------------- 
distribution    NEVER               NEVER     
master          NEVER               NEVER     
model           NEVER               NEVER     
msdb            NEVER               NEVER     
Northwind       NEVER               NEVER     
pubs            NEVER               NEVER     
ProjectOne               2          07/08/2004
ReplcationTst   NEVER               NEVER     
Scratch         NEVER               NEVER     
tempdb          NEVER               NEVER     
TSQLWorking              0          07/10/2004
UDFofWeek                0          07/10/2004

This was run on 7/10/2004, my birthday by the way. The databases that were backed up today have zero days and as you can see many of these databases haven't been backed up. Obviously, I'd better get around to backing up my system databases. All of them except tempdb.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick