Home / server / Questions / 169957
In Process
GurdeepS
Asked: 2010-08-13 13:17:11 +0800 CST

Backing up a SQL Server vm by doing a complete vm backup as opposed to individual dbs?

  • 772

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

sql-server backup

3 Answers

  1. 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.

    • 2

  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.

    • 2

  3. 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

    • 1