Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

community. 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, Alexey.

share|improve this question
    
What do the various backup* tables (i.e. backupset, backupmediafamily, backupmediaset) say about it? Incidentally, the restore that you were "forced" to perform doesn't sound odd to me given how you've set up your backups. What restore did you try that didn't work as intended? –  Ben Thul Apr 12 '13 at 12:09
    
What are you using to perform the Windows backups? Are your database files stored on the C:? –  Mike Fal Apr 12 '13 at 19:47
    
If the backup is happening within MS SQL Server to the point where the LSN numbers are being affected, then it has to write it to the MSDB history table. Check out this link for the query to execute. You should see the additional backups in the history table to validate the hypothesis: mssqltips.com/sqlservertip/1601/… –  Ali Razeghi Apr 12 '13 at 22:37
    
1.I'm doing "correct" backups with stored procedure + batch script that is being executed by task scheduler. 2.backup* tables are telling that full backup has been done to VIRTUAL_DEVICE with long GUI(no clue hav to convert it to the real path) 3. Databases are being held on C: cause machine isn't so much of value. Disk C: is a virtual HDD which is stored on RAID10 partition of Hyper-V Host. So the chance of data loss is extremely low. –  AlexeyLoire Apr 15 '13 at 6:02
add comment

migrated from stackoverflow.com Apr 12 '13 at 19:33

This question came from our site for professional and enthusiast programmers.

1 Answer

I have a similar problem:

  • I have my own backups running as expected
  • The native Windows backup backs up to a VIRTUAL_DEVICE

Both appear as standard database backup entries in the SQL Server error log

I can not restore from the VIRTUAL_DEVICE. It is just a GUID. This worries me

What I'm trying is to disable the SQL Server VSS Writer service to see if this phantom backup goes away.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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