Using VM Snapshots to Backup SQL Server?

“Oh, we use VM snapshots to backup our SQL Servers.” We sometimes hear this when talking with companies, especially when we’re delivering a SQL Server Health Check Assessment.

And why not? It makes sense. Companies take snapshots of their file servers. They snapshot of their web servers before upgrades. FTP servers, Citrix servers, and even some end-user VMs, are all snapshotted regularly.

The Ops Teams knows the process. And they know the tools.

So why not use the same approach for SQL Server? It’s just like the others, right?

Well, not really.

Should you use VM snapshots to backup your SQL Server?

SQL Server is different than most other servers. It has different requirements and unique capabilities. This doesn’t necessarily preclude you from using the same snapshot approach to backups. But before using VM snapshots as your primary backup mode for SQL Server, ask yourself some questions.

Will VM snapshots meet your Recovery Point Objectives?

How much data can you afford to lose in your most important databases? A day’s worth of data? A few hours worth? 15 minutes? How much before it gets really painful and costly? How much before rekeying the data is too much effort?

These questions help uncover your Recovery Point Objectives (RPOs). Each database on a SQL Server may have different requirements.

Taking nightly snapshots of your SQL Server may meet some of your RPO requirements for some of your databases. But if the business requires no more than 10 minutes worth of potential lost data, taking nightly snapshots won’t cut it.

If you need more granular backups than your VM snapshots allow, snapshots are not a good option for you.

Can you live with an “all or nothing” recovery?

Let’s say you can afford to lose up to a day’s worth of data in all your databases on a SQL Server? What then? Would nightly VM snapshots work?

Maybe.

But when you revert to a VM snapshot, you’re reverting the server, not a database.

Reverting to a prior VM snapshot means that all databases will be reverted back to the point when the snapshot was taken. That may not be what your stakeholders want.

If you want to be able to easily restore a single database, VM snapshots make that more difficult.

Can you restore a parallel VM?

Maybe an “all or nothing” restore is not acceptable. But, maybe there’s a workaround? Maybe you can restore the image to a parallel location instead of restoring over the production VM?

That could work.

If you have the space to do that. And if you segment it off so that you don’t run into naming issues. Then you could extract only the database you’d like to restore. Or query the database to retrieve rows that may have been accidentally deleted or updated.

But then again, that workaround seems like a lot of trouble just to restore a single database. Are you ok with that?

Does your process meet your Recovery Time Objectives?

So, you can live with the “all or nothing” workaround by restoring last night’s VM snapshot to another location. Good.

How long will it take?

Let’s say a user accidentally deletes all rows in the customer table. How long before you can get the data back? What if corruption is discovered in the database? How long will it take to restore the database from a VM snapshot taken before the corruption occurred?

The best way to find out is to try it. Time yourself. (And by the way, now is a great time to document the process.)

Will that meet your Recovery Time Objectives (RTOs)? Are you within your Service Level Agreements (SLAs)?

If not, again, it may be time to rethink you backup and restore strategy.

Are you ok with setting your databases to use Simple Recovery?

Assuming you’re ok with everything up to this point, the next question may seem relatively minor. Are you ok with setting your key databases to use the Simple Recovery Model?

With the Simple Recovery Model, SQL Server manages the transaction log for you. It automatically reclaims space in the log file, keeping it from continually growing. That’s nice. You don’t have to worry with it.

However, the Simple Recovery Model comes with some side effects. Since you are not backing up your transaction log, you won’t be able to perform a point-in-time recovery, if needed.

There are some other implications. For example, you’ll also forfeit the ability to implement a very simple, effective, and low-cost means for basic Disaster Recovery – Log Shipping. And databases using the Simple Recovery Model cannot participate in an Always On Availability Group.

So, your approach to backups affects your HADR capabilities. That may be a concern, either now or in the future.

For more information about SQL Server’s recovery models, see Recovery Models (SQL Server) – SQL Server | Microsoft Docs.

Do you want to be alerted when databases aren’t backed up?

Some SQL Server monitoring tools, including our own SEROShield processes, look to SQL Server to determine when the last successful database backup occurred. If a database hasn’t been backed up recently, an alert can be raised and you can be notified. That’s comforting.

However, SQL Server is unaware of most VM snapshots. So, your SQL monitoring tools won’t be able to warn you if a database hasn’t been backed up.

If you’re going to use VM snapshots, you’ll want to disable some alerts in your monitoring solution.

Are you ok not practicing your database restores?

The best way to be certain you can restore a database is to practice. As they say, practice makes perfect.

We recommend restoring a key production database to a test server regularly. Often that’s monthly. But it varies. Some clients prefer more frequent test restores while others are fine testing quarterly. See How Often Should I Test My SQL Server Backups? for more information.

After a test restore, run an integrity check to make sure the whole process completed flawlessly. See How to Test SQL Server Backups Using dbatools for more information.

This is doable, but more difficult, using VM snapshots.

Will a disk I/O freeze affect your users?

Often disk I/O activity is frozen momentarily when VM snapshots are taken. SQL Server reports excessive I/O waits during that time.

If your business is a 9 to 5 kind of business without any evening activity, that may be ok. Nightly VM snapshots may work for you. However, if you plan to take snapshots while users are connected and actively using the database, that could lead to a subpar experience for them. Expect delays.

“So, I shouldn’t take VM snapshots of my SQL Server?”

Depending on your answers to these questions, VM snapshots may work for backing up your SQL Servers.

For most of our clients, however, there are just too many drawbacks or concerns for VM snapshots to be their primary method for backing up SQL Server.

That doesn’t mean it cannot be part of the solution. Certainly we recommend taking a snapshot prior to applying patches and other significant changes to the server. Occasional snapshots can also be a part of the process.

What should you use to backup your SQL Server?

Sometimes the most simple solution is the best solution. We find that native SQL Server backups using Ola Hallengren’s SQL Server Maintenance Solution work very well. It’s flexible, it’s proven, and it’s based on native T-SQL code.

That’s what we recommend.

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.