THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

Have your database ever been backed up?

Recently I've been asked by one of my clients to write a simple script that would find out whether particular database had ever been backed up (full backup). The reason for a requirement was semi-automatic application which would enable end users to manage backup activity including manually backup transaction log - not via Management Studio but via much more friendly UI designed for non-DBAs. As you know, for database running in Full Recovery Mode, transaction log can't be backed up before full backup is done at least for the first time. OK, I thought. Let's just check whether full backup exists for the database:

IF EXISTS( SELECT 1 FROM msdb..backupset WHERE database_name = 'MyDB' AND [type] = 'D' ) ...

 Here comes the first catch. Imagine following scenario:

1. Create database MyDB.

2. Create full backup of 'MyDB'.

3. Drop database 'MyDB' (without deleting backup history). 

4. Create new database with the name 'MyDB' or restore from backup.

What do we have now? Query will return true while actually backup we have belongs to another non-existing database. Try to backup transaction log of the new DB - you'll receive error.

First try failed - let's give another one. Both sys.databases and msdb..backupset contain creation date of the database. In sys.databases column name is create_date, in msdb..backupset it is database_creation_date. So let's just compare database_creation_date from the latest full backup row with database's create_date. If they're equal - we have full backup. Otherwise backup belongs to the previous incarnation of our database while full backup of currently existing db had never been taken. Sure, it should also work for the normal scenario - without deleted and restored databases on the way. So first let's check whether our theory works after second step of the scenario described above. But what happened? How comes, our database creation times are different? 

Here comes second catch. For some strange reason, database_creation_date column in msdb..backupset doesn't contain milliseconds! Minutes, seconds - everything is there but for milliseconds its always 000. While create_date in sys.databases contains full date including milliseconds. Don't know who and for what reason cut milliseconds from the backup history table, but that's the way it works now.

To make long story short - it was the last barrier. After removing milliseconds part from database creation date in sys.databases, everything began to work. Here is the full script that will tell you whether some particular database had ever been backed up (full backup).

DECLARE @DBName SYSNAME
SET 
@DBName 'YourDatabase'

DECLARE @LatestBackupDate DATETIME@BackedUpDBCreationDate DATETIME
DECLARE @DBCreationDateNoMilliseconds DATETIME

-- In msdb..backupset table, database_creation_date column is in datatime format
-- but without milliseconds, so we remove milliseconds from database creation date.
SELECT @DBCreationDateNoMilliseconds =
    DATEADD(millisecond, -DATEPART(millisecondcreate_date), create_date)

FROM sys.databases
WHERE [name] @DBName
IF @DBCreationDateNoMilliseconds IS NULL
  
BEGIN
       PRINT 
'Database ' @DBName ' doesn' CHAR(39) + 't exist on the server'
      
RETURN
   END

SELECT
  
@LatestBackupDate backup_finish_date,
  
@BackedUpDBCreationDate database_creation_date
FROM msdb..backupset
WHERE 
  
backup_set_id (
      
SELECT MAX(backup_set_id)
      
FROM msdb..backupset
      
WHERE database_name @DBName AND
      
[type] 'D' )

IF @LatestBackupDate IS NULL
  
BEGIN
       PRINT 
'Full backup had never been taken for database ' @DBName
      
RETURN
   END

IF 
@BackedUpDBCreationDate @DBCreationDateNoMilliseconds
  
PRINT 'Full backup exists for database ' @DBName '; Transaction log backups can be taken'
ELSE
   PRINT 
'Full backup exists for the current version of ' @DBName ' database  had never been taken. Existing backup refers to non-existing database with the same name'



Published Saturday, March 01, 2008 5:15 PM by Michael Zilberstein
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Daniel said:

It is also possible to check if a backup was ever made by querying the sys.database_recovery_status table:

SELECT DB_NAME(rs.database_id)

FROM sys.database_recovery_status rs

WHERE rs.last_log_backup_lsn IS NULL;

The returned databases are those that have never been backed-up.

March 3, 2008 6:15 AM
 

Michael Zilberstein said:

Daniel, thanks!

Didn't know about this option. Its really much more simple.

March 3, 2008 6:58 AM
 

Alejandro Mesa said:

Hi Michael,

Good catch!

See this code that is using SMO to pull LastBackupDate and LastLogBackupDate for a specific database.

'Connect to the local, default instance of SQL Server.

Dim srv As Server

srv = New Server

'Reference the AdventureWorks database.

Dim db As Database

db = srv.Databases("Northwind")

'Display date data for the database.

Console.WriteLine(db.Name)

Console.WriteLine("Created on " + db.CreateDate)

Console.WriteLine("Last backed up on " + db.LastBackupDate)

Console.WriteLine("Log last backed up on " + db.LastLogBackupDate)

Now look at the statements it is sending to the engine:

SELECT

(select max(backup_finish_date) from msdb..backupset where type = 'D' and database_name = dtb.name) AS [LastBackupDate]

FROM

master.dbo.sysdatabases AS dtb

WHERE

(dtb.name=N'Northwind')

SELECT

(select max(backup_finish_date) from msdb..backupset where type = 'L' and database_name = dtb.name) AS [LastLogBackupDate]

FROM

master.dbo.sysdatabases AS dtb

WHERE

(dtb.name=N'Northwind')

It will be helpful if you can post what you found, in connect.microsoft.com.

Regards Danield's suggestion, very interesting but not available for SSQL Server 2000.

AMB

March 3, 2008 2:01 PM
 

Michael Zilberstein said:

March 5, 2008 12:45 PM
 

Alejandro Mesa said:

Hi Michael,

You are welcome.

I did a test in SQL Server 2008 CTP6 and could reproduce the issue. I already voted in connect and also added a comment about the test I did in SS 2008 CTP 6.

AMB

March 8, 2008 4:42 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement