Is backing up a SQL Server via doing a complete backup of the VM image rather than backing up individual databases a bad practise?

I can imagine that backing up the entire image looses the granularity of having several tlogs of each db for each day. Is this generally a bad thing?



If you do not back up the db at the db level, you lose the ability to do not only complete DB restores but also:

  • File restores (without restoring the whole db)
  • Page restores
  • Piecemeal restores

In addition you can have a very reasonable backup of SQL server and not take up too much space. With restore points from 15 minutes to 2 hours ago. Taking a snapshot of the image every 15 minutes seems like a ludicrous proposition.

However, IF your database is not very transactional (legacy, read-only), I suppose backing it up by taking a vm snapshot could in theory be okay.

  • 1
    Good point about the legacy databases thing - We have a client with Pervasive SQL which is quite old-school in its manner of operation. They've always backed it up by simply copying the flat files. Now we're moving them to a more modern version, we're also implementing the pervasive backup agent which puts the database into a consistent state, then unlocks it when you're finished. At least this way, there's no 'maybe' about the consistency of the data. Aug 13 '10 at 2:16

If you do a backup of the VM, then the database won't be in a consistent state when you back it up as the database won't flush its writes to disk because it doesn't know that there is a backup operation in progress.


Backing up SQL Server solely by doing VM snapshots is not officially supported by Microsoft:

Virtualization Snapshots for Hyper-V or for any virtualization vendor are not supported to use with SQL Server in a virtual machine. It is possible that you may not encounter any problems when using snapshots and SQL Server, but Microsoft will not provide technical support to SQL Server customers for a virtual machine that was restored from a snapshot.

Source: Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment (emphasis mine)

More information: Virtual machine snapshots and tier 1 apps: Not always supported

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.