Sql-server – Strange Backup and Alerting Issue

alertsbackupsql-server-2008-r2

I received alerts from one of my database servers over the weekend that was somewhat disconcerting. There was an alert for each single database within the maintenance plan.

A full backup has never been taken for database *SeanDatabase*

I've had a maintenance plan in place for years that has run without fail and the associate logs backup this claim. Also, the backups are time stamped and in the place expected.

I did a restore in a test environment and ran a consistency check without any errors.

I'm not sure that I really see this as a true error or cause for concern considering the aforementioned evidence. So, maybe this is just an anomalous event? Either way, I'd like to poke around and a assuage my sensibilities.

Has anyone ran into this situation before? If so, what did you do to investigate?

  • Virtualized system on VMWare
  • SQL ver. 10.50.6220
  • Windows Server 2008R2 w/SP1

Best Answer

You can get an idea of what backups have been taken, and when, by looking at the result of this query:

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = 
        CASE bs.type 
            WHEN 'D' THEN 'Database' 
            WHEN 'L' THEN 'Log' 
            ELSE '[unknown]' 
        END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf 
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = @DBName
    OR @DBName IS NULL
ORDER BY bs.backup_start_date DESC;

By default, this will show you all backup activity that has occurred on the current instance since the last time msdb was created/restored, assuming backup history has not been deleted.

Related Question