Differential Base and VSS Backups


VSS backups are a common way of taking SQL Server database backups using various backup utilities that are available today. However, one of the not commonly known facts is the differential backups that can be performed using VSS APIs. The SQL Server VSS Writer service is the one which facilitates the backups of SQL Server databases through VSS APIs.

The SQL writer supports differential Backup/Restore through two VSS differential mechanisms: Partial File and Differenced File by Last Modify Time.

  • Partial File The SQL writer uses the VSS Partial File mechanism for reporting changed byte ranges within its database files. 

  • Differenced File by Last Modify Time .   The SQL writer uses the VSS Differenced File by Last Modify Time mechanism for reporting changed files in full-text catalogs.

For more information, read the technical reference document on VSS backups and SQL Writer.

Once of the first points that you need to remember with VSS backups of your SQL Server databases is that a non-copy only VSS backup can break the differential chain. For more information on VSS Copy Only backups, read the blog post on the same topic.

Let me show that with an example. I will start with three full backups of my Adventureworks database in the following order:

1. Database Full backup using VSS backup
2. Database Full backup using native SQL backup
3. Database Full backup using VSS backup

When I look into the system catalogs, I find that the Differential Base LSN changes with each backup done. The differential base time is reported in GMT which is (+5:30 for my server’s time zone). The data shown below was retrieved from the sys.master_files output after each backup.

Differential Base LSN Differential Base Time
58000000764000000 5:09:54 PM
58000000767800000 5:11:55 PM
58000000770500000 5:12:21 PM

I validated the above entries with my SQL Server Errorlog as well:

2013-01-05 22:39:54.310 spid55       I/O is frozen on database AdventureWorks. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-05 22:39:54.330 spid55       I/O was resumed on database Adventureworks. No user action is required.
2013-01-05 22:39:54.340 Backup       Database backed up. Database: AdventureWorks, creation date(time): 2012/09/21(20:50:44), pages dumped: 21978, first LSN: 58:7640:65, last LSN: 58:7668:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{24635C74-D8AD-475C-88AE-831D0F31AD79}’}). This is an informational message only. No user action is required.

2013-01-05 22:42:04.650 Backup       Database backed up. Database: adventureworks, creation date(time): 2012/09/21(20:50:44), pages dumped: 22027, first LSN: 58:7678:37, last LSN: 58:7695:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘<directory>\adw.bak’}). This is an informational message only. No user action is required.

2013-01-05 22:42:21.300 spid55       I/O is frozen on database AdventureWorks. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2013-01-05 22:42:21.310 spid55       I/O was resumed on database Adventureworks. No user action is required.
2013-01-05 22:42:21.310 Backup       Database backed up. Database: AdventureWorks, creation date(time): 2012/09/21(20:50:44), pages dumped: 21978, first LSN: 58:7705:37, last LSN: 58:7722:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{17FAEB54-A411-4E16-BD96-FF5DE627CEDB}’}). This is an informational message only. No user action is required.

Now when I take a differential backup of the AdventureWorks database, the differential base guid is reported as 9EAAC560-B5AB-4DE6-A44B-A52E8D5BD82B and the differential base lsn is reported as 58000000770500037. This matches with the differential base guid that is reported by sys.master_files. However, this differential backup cannot be restored as the VSS backup taken was not a valid VSS backup.

The screenshot below shows the sequence of backups.

image

To summarize, if you have an application which takes VSS backups of volumes which contain SQL Server database files, then it could inadvertently invalidate your differential backups of your SQL Server databases even if you are not backing up SQL Server database files. If your VSS backup application does not use the COPY ONLY option, then the full backup of the database files taken by the snapshot backup of the volume will become your new differential base.

Reference:

SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume
http://support.microsoft.com/kb/951288

The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database
http://support.microsoft.com/kb/903643

Advertisements

5 thoughts on “Differential Base and VSS Backups

  1. …and…3 years later I revive and old thread to the scorn of everyone, but…any good links to information on SQL 2012 and/or Windows Server 2012 R2? based on my logs, it still seems to work the same way, however, the “guide for SQL Server Backup Vendors” is awesome, if I could find an updated one – it is over 10 years old…

    Like

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s