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(millisecond, create_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'