Sql-server – Windows native backups makes SQL Server think that databases backup has been done

backupsql serversql-server-2008

I've Windows Server 2008 R2 which hosts few virtual machines.
One of machines is Windows 2008 R2 server with SQL server 2008 R2 Express installed.
Here's the thing:
I wrote a script that backups databases at 05:00AM everyday.
Mon, Wed, Fri – Full backups.
Tue, Thu, Sat, Sun – Transaction log backups.

A few days ago i had to try to restore data from backups and i couldn't. I recieved an error message that my Transaction log is to recent to use. It was on Tuesday. So basicaly i had to restore monday's full backup and tuesday's early morning transaction log backup.

I started to research the cause of it and soon discovered that everyday at 04:00AM and 11:00PM sql server backups all the databases to some VIRTUAL_DEVICE with two different SIDs.
I realized that at 11:00PM starts Windows Server Backups on that concrete virtual machine. Backup contains only Bare metal recovery + System state + C:
Later i understood from where comes backup at 04:00AM. There's similar story. At 04:00AM starts backup of Hyper-v Host with the same parameters(it's strange but sql server somehow realizes that machine is being backed up.

So we have:

  • Backup of Virtual machine at 11:00PM
  • Backup of Hyper-V host at 04:00AM

After procedures described above EventLog recieves message that SQL server has been backed up. But There's no physical place where those "backups" are stored. Since SQL Server thinks that he'd been backuped he change LSNs so any correct backups of transaction logs can't realy be restored after another correct full backup.

Please note: Hyper-v host and virtual machine itself are being backed up to the personal colume for backups. After assigning letter to the volume i can see VHDs of disk that had been backed up. And no sign of sql databases backup inside.

The main problem is to stop SQL from reacting on system backups.

Looking forward for your replies and thanks in advance,

Best Answer

This KB isn't directly related but the quote in it is:

MSKB2615182: Windows Server Backup May Fail Because of the SQL VSS Writer:

When Windows Server Backup attempts to backup a disk volume, a Volume Shadow Copy Snapshot is created for the volume. When the snapshot is created any VSS writer associated with the volume is called.

If there are SQL Server VSS writer backups being taken then you will see records in the MSDB.dbo.BackupMediaFamily table in the physical_device_name column. You might want to look into disabling the SQL Writer Service.

TechNet: SQL Writer Service