2

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?

Thaks

GurdeepS
  • 1,646
  • 5
  • 26
  • 33

3 Answers3

2

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.

GregD
  • 8,713
  • 1
  • 24
  • 36
  • 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. – Chris Thorpe Aug 13 '10 at 02:16
2

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.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
1

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

Heinzi
  • 2,217
  • 5
  • 32
  • 52