SQL Server – Will a VM Backup Work for Database Restoration?

sql server

I am wondering if a VM backup will restore a SQL database with out integrity issues…. Ie… we backup SQL nightly and keep 3 copies, we backup with VM and keep 14 copies…. if we had to restore day 10 from VM would we have any integrity issues, I know we could not do log recovery, but that point in time would be better than nothing. We have completed a test restore and it seemed to work, but I would like the experts answers also. Thanks

Best Answer

I've seen database servers go down in flames because the VM backup wasn't database-aware. While it MAY work, you'd best test it first to be sure -- restore the VM as a clone with a different name just to see if the DB comes online. (In disaster recovery, no backup is good unless you've restored it somewhere to prove it is good...)

I recommend you implement a two-stage backup process:

  1. Backup the virtual machine's OS and test that the OS can be recovered. It sounds like you're doing this already.
  2. Backup the databases separately, via a native SQL Server backup tool, and preserve them separately.

I recommend you do the SQL backups with a tool like the backup scripts in Ola Hallengren's solution.

In my production environments, we back up our database servers (physical and virtual) daily, but we use the Ola Hallengren scripts to backup the databases to a separate file server. We schedule the O. Hallengren jobs such that:

  • User Databases get a full backup once a week and differential the other days.
  • System databases get a full backup daily.
  • User Database logs get backed up every 1 to 30 minutes, depending on the server. (Production servers? Every minute. Dev servers? half an hour isn't a problem for them...)
  • All of this is configured to preserve two weeks of backups on the file server.

(We also schedule the DBCC and Index jobs those scripts create on a weekly basis.)

This means I can restore any of my databases to any point in time within a rolling two-week window Before I have to go get a tape restore from our backup engineer. Any further back and I can have the engineer restore the appropriate backup files from that file server and then manually restore the database from there -- rather than blowing out an entire server just to get one database (or one table on one database, Cthulhu-forbid...)

And I have two scripts (based on this) that build the series of restore database statements needed to recover via the files this produces. One just dumps all the full backups in the file share, then all the Diff backups, then all the log backups. The other is a bit smarter, in that it gets the most recent full, the most recent diff, and then the log files more current than that diff. This way, if I have to recover a crashed database or clone a database to another server, I just fire off those scripts, create the "Move X to path" statements since our system engineers never assign the same drive letters when deploying new servers (sigh), and I'm done.

Also, always remember to set your backup strategy to meet your company's RPO and RTO goals.

I also feel a need to reference the "Bad Backup Schedule" list, since it specifically mentions VSS snapshots.

Related Question