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
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:
- Backup the virtual machine's OS and test that the OS can be recovered. It sounds like you're doing this already.
- 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.
I am wondering if a VM backup will restore a SQL database with out integrity issues
Maybe. If you have databases that span local volumes, then your VM backup must be "app consistent" by either using the Windows Volume Shadow Copy Service (VSS), or by ensuring write-ordering across the VM's volumes.
If you're not 100.000% sure of this, you can't rely on VM backups for database recovery. You can always start with a VM backup and restore your SQL Server backups on it for a more robust recovery solution.
Make sure that you see the snapshot reflected as a SQL Server backup in the SQL Server backup history and the errorlog file. You will also see messages like "I/O is frozen for database X", "I/O is frozen for database Y", etc. And subsequent "resuming" messages after the snapshot was established. Without those messages, the SQL Server data is not to be trusted.
Yes that is possible but as you noted, if this is a backup solution, you lose all capability to restore individual databases, point in time recovery, and of course as you know will lose any OS changes made. However if you wanted to restore it, backup the SQL Server database so you have a backup of it now, or just continue using it, you're good to go assuming the VM was shut down and brought up properly.
The 'cleaner' method would be to backup the SQL Server on here, then restore it on your primary VM. This way you know exactly what you're working with. However reverting to a snapshot, if shutdown and started up properly, is perfectly acceptable too.
Just check the Hypervisor, OS, and SQL Server error logs to ensure it shut down and started up properly.