VM Snapshot Backup Review for databases

VM administrators like VM snapshots for recovery due to standardisation and ease of use.  Lets look at some of the issues and complexities of relying on VM Snapshots for RDBMS servers

The usual benefits  cited of VM snapshots are :

1. VMs can be restored quickly
2. VMs can be restored on another server with compatible virtualisation platform
3. All data is protected. It is not OS oriented or reliant on specific backup agents

To make VM aware backups for SQL Server the Volume shadow-copy service (VSS) is required. VM Snapshot relies on Microsoft VSS to provide a consistent state for the SQL Server backups

To create the consistent state , I\O activity is quiesced prior to snapshot

A VM Snapshot can recover to a point-in-time , but is this the point-in-time required by the Recovery Point Objective (RPO). Relying on a VM Snapshot for a point-in-time recovery is inflexible when compared to a SQL Server specific RESTORE. With a full backup and transaction log backups , a specific point-in-time recovery can be manipulated

Let’s say you only need to recover a single database. With a VM Snapshot you’ll need to mount the VM , and the use different transfer nodes to export the database

Typically a transactional based  SQL Server database relies on transaction log backups. As well as allowing recovery , the other purpose of backing up logs is to clear down the transaction logs.

Clearing down transaction logs only occur by using the BACKUP LOG native commands. Avoid VM Snapshot process that break the backup log chain. For example, if a VM Snapshot system relies on BACKUP LOG myDB TO DISK =’NUL’, then you are breaking the log chain.

The Starting point in reviewing any backup strategy should be on a review of RPO and RTO. That becomes the basis of applying a strategy to protect data

Some other points about VM snapshots

1. Microsoft VSS does not support DB2. In a mixed RDBMS environment , consistency and standardisation is the key to operational efficiency
2. Without VSS you begin relying on potentially Inconsistent backups, crash consistent backups,file-level cosnsistent backups
3. From a DBA and data protection aspect – a transaction consistent backup is the aim. A transaction consistent backup includes all the data from a point in time and completed transactions.
4. If your environment includes a mixture of physical and VM – then different approaches are required.

 Read More on SQL Server Backup Strategy

Backup history for a single SQL Server database - SQL Server DBA

SQL Server - Top 10 DBA mistakes - SQL Server DBA

Creating a Disaster Recovery Plan - SQL Server DBA