January | 2013 | TroubleshootingSQL

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.

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

Happy New Year to all of you out there!!

The year that was…. 2012… was an action packed year in all things SQL. Microsoft launched SQL Server 2012 which introduced a large number of enhancements and new features in the database engine, integration services, analysis services and reporting services. If I would have to summarize my learning time year-on-year, then I have spent the maximum time in 2012 studying and enhancing my knowledge in SQL Server due to all the new features introduced.

TroubleshootingSQL also had a good year… The Facebook page now has over 300 likes… The Twitter avatar of TroubleshootingSQL has over 200 followers and my blog showed a significant YoY growth in visitor count!! So thank you for all the patronage and I am glad the SQL folks find my posts useful! The TOP 5 posts of the year in my blog were:

The new Wiki series that I started also received significant amount of coverage as well and the posts #2 and #3 mentioned above are part of my Wiki series.

I did speak at various community events throughout the year, the most significant of them being TechEd India.

Another highlight was the book launch at SQL PASS 2012 which I co-author: Professional SQL Server 2012: Internals and Troubleshooting, which is now available both as e-books as well as paperback across many regions.

What can you look forward to in 2013. Since the world did not end, I will write about all the posts that I have been holding onto (partly due to procrastination) due to the fact that world was going to end in December!!

You will see new additions to the SQL Wiki series, more posts about Power View, useful features in SQL Server 2012 that are not commonly used and much more! So stay tuned….