Infrastructure at your Service

David Barbarin

Using VSS snapshots with SQL Server – part I

 

This is probably a series of blog posts about some thoughts concerning VSS snapshots with database servers. Let’s begin with this first story:

Some time ago, I implemented a backup strategy at one of my customers based on FULL / DIFF and log backups. No issues during a long time but one day a call of my customer who told me that since some days, the differential backup didn’t work anymore with the following error message:

 

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database “demo”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

After looking at the SQL Server error log message I was able to find out some characteristic entries:

I/O is frozen on database demo. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

I/O was resumed on database demo. No user action is required.

Just in case, did you have implemented a snapshot of your database server? And effectively the problem came from the implementation of Veeam backup software for bare-metal recovery purpose. In fact after checking out the Veeam backup software user guide, I noticed that my customer forgot to switch the transaction log option value to the “perform backup only” with application-aware image processing method.

This is a little detail that makes the difference here. Indeed, in this case, Veeam backup software relies on VSS framework and using process transaction log option doesn’t preserve the chain of full/differential backup files and transaction logs. For those who like internal stuff you can interact with the VSS writers by specifying some options during the initialization of the backup dialog. The requestor may configure VSS_BACKUP_TYPE option by using the IVssBackupComponents interface and SetBackupState method.

In this case, configuring the “perform backup only” means that Veeam backup software will specify to the SQL writer to use the option VSS_BT_COPY rather than VSS_BT_FULL to preserve the log of the databases. There are probably other specific tools that will run on the same way, so you will have to check outeach related user guide.

Let’s demonstrate the kind of issue you mayface in this case.

First let’s perform a full database backup as follows:

 

BACKUP DATABASE demo TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\demo.bak' WITH INIT, STATS = 10;

 

Next, let’s take a snapshot. If you take a look at the SQL Server error log you will find the related entries that concern I/O frozen an I/O resume operations for your databases.

Moreover, thereis another way to retrieve snapshot events. Let’s have a look at the msdb.dbo.backupset table. You can identify a snapshot by referring to the is_snapshot column value
USE msdb;
GO
SELECT
backup_start_date,
backup_finish_date,
database_name,
backup_set_id,
type,
database_backup_lsn,
differential_base_lsn,
checkpoint_lsn,
first_lsn,
last_lsn,
is_snapshot
FROM msdb.dbo.backupset
WHERE database_name = 'demo'
order by backup_start_date DESC;

blog_43_-_1_-_backup_history

… and this time the backup failed with the following error message:

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database “demo”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

In fact, the differential database backup relies on the last full database backup (most recent database_backup_lsnvalue) which is a snapshot and a non-valid backup in this case.

Probably the best advice I may provide here is to double check potential conflicts you may get from your existing backup processes and additional stuff like VSS snapshots. The good thing is that one of my other customersthat uses Veeam backup software was aware of this potential issue but we had to deal with other interesting issue. I will discuss about it to the next blog post dedicated to VSS snapshots.

 

Leave a Reply


− four = 1

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader