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
  • 2
    Your server probably shifted for daylight saving time and got confused during that hour (maybe the last backup was in the future or never happened, depending on what logic the maintenance plan is using). This is one reason I strive to set all servers in all environments to UTC. – Aaron Bertrand Nov 2 '15 at 17:47
  • Hmm, interesting, never even thought of that! Is there a "quick and dirty" way to implement UTC? Group Policy, I assume? – Sean Perkins Nov 2 '15 at 17:57
  • Perhaps, I am often lucky enough to intervene when the operating system is being installed, and have a documented setup procedure for settings that should be enabled - language, time zone, roles, etc. – Aaron Bertrand Nov 2 '15 at 18:12
  • I would also see if someone is changing recovery model of the databases. You can search through SQl Server errorlog to find this out. Other thinh VMWARE snapshot backups has history of disrupting LSN chain, please check in that respect as well. – Shanky Nov 3 '15 at 7:19
  • We couldn't find any evidence that it was VMware :( Thanks for the assist and info, all. I appreciate your help! – Sean Perkins Nov 4 '15 at 15:13

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

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]' 
    , 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.

  • 1
    Well, folks, I don't know what to say other than it was a ghost in the machine. There has been no evidence of what caused this, so I'm going to go with Daylight Savings Time /shrug – Sean Perkins Nov 4 '15 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.