2

I have a virtual server running SQL Server 2008 R2. On the server I have configured two maintenance plans, one that takes differential backups every day except Saturday and a job that takes full backups on Saturdays. Now when I tried to restore a differential backup I didn't manage to do it because it complained that the diff backup was out of sync with the selected full backup. After some investigation I noticed that because I run Windows Server Backup on my Hyper-V host machine (the one hosting the virtual server) Windows Server Backup makes snapshots of the databases on the virtual server. The differential backups starts to point to the snapshots instead of the full backups. I now disabled the service SQL Server VSS Writer on my virtual server to prevent snapshots from being taken, but will Windows Server Backup now succeed in taking backup of the virtual server or does it need that specific service to lock the database files? What is the correct way to have snapshots and "normal" backups running side-by-side? Can e.g. the snapshots be configured to be "copy-only" so that they don't mess up the normal backups?

brunnsbe
  • 31
  • 4
  • 1
    I have run into this exact same issue using Symantec Backup Exec 12.5. Backup Exec transaction log backups fail as soon as it performs a backup of the VM as a whole. I am interested to know what solution, if any, you came up with. – pat o. Mar 15 '11 at 13:06

2 Answers2

1

Can e.g. the snapshots be configured to be "copy-only" so that they don't mess up the normal backups?

Yes, Windows Server Backup allows to choose between these two options:

  • VSS full backup
  • VSS copy backup

Choosing "VSS copy backup" should leave your SQL Server backups intact.

This tech community article explains the setting.

If you're using the configuration wizard for your Windows Server Backup, the setting is availabe during the step "Select items for Backup". Click the button "Advanced Settings" and then switch to the tab "VSS settings".

This blog entry has a few screenshots.

Karl
  • 21
  • 4
0

If you disable a VSS writer in the VM, the VM backups will not be coherent with respect to the database. So you're much more likely to end up in a situation where you try to restore your VM backup and you find your database corrupt. Since database repair can take a while, and since corruption can lead to data loss, I doubt that you should do that.

Jake Oshins
  • 5,146
  • 18
  • 15
  • But how are you supposed to handle normal backups if the VSS writer is on? With the VSS writer I have to restore the whole server to a point in time instead of just a single database. However I now first take normal backups of the database before running the Windows Server Backup on the Hyper-V supervisor server, so if the databases become corrupt I always have fresh database backups available. – brunnsbe Feb 08 '11 at 07:50
  • That's really what "backing up a VM" means. You can restore the whole server to a point in time. If you want to back up a database, do that within the VM. Many people would suggest that you might, now that you're using VMs, want to have a VM for each database. – Jake Oshins Feb 08 '11 at 18:54