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

Some facts about the Database Recovery Advisor and Restores


A new feature in SQL Server 2012 Management Studio is the Database Recovery Advisor. So why am I writing a post on a SQL Server 2012 enhancement which has been blogged already. Database Recovery Advisor There are some unique nuances that you need to be aware of while using this new utility. The Database Recovery Advisor facilitates constructing restore plans that implement optimal correct restore sequences.

Read on if you are interested in learning more about the Disaster Recovery Advisor. I will be referring to the Database Recovery Advisor as DRA in this post going forward.

The facts mentioned in this post are answers for frequently asked questions about DRA that I get from customers.

Continue reading

Moving those large files for secondary databases


I remember seeing a question on the #sqlhelp hashtag on how to move the secondary database files to a new physical location. While this might seem a mundane task but can throw up a few surprises. If you don’t want to tear down your log shipping configuration and re-establish it with the files in the new physical location, then this post will definitely interest you.

If you are running your log shipping in standby mode, you will first need to switch to norecovery mode. This is due to the fact that you cannot execute an ALTER DATABASE command on a standby database as it is not writable. You will be presented with a following error if you attempt to do so:

Msg 5004, Level 16, State 4, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

So to avoid the above issue, you need to switch to norecovery mode. If you are already running in this mode, then you have one task reduced from your list. Keep in mind that the switch of the operating mode takes effect only after the next log restore operation.

Now here are the exact set of steps that you need to follow:

1. Disable the Log Shipping Restore SQL Agent job.
2. Use ALTER DATABASE command to change the location of the secondary database files
3. Stop the SQL Server instance hosting the secondary database files
4. Move the secondary database files to the new location as updated in Step #1. (OS File Copy)
4. Start the SQL Server instance
5. Enable the Log Shipping Restore SQL Agent job

Verify that the log shipping jobs are running without any errors. The reason you need to stop the SQL Server instance is because an ALTER DATBASE…OFFLINE doesn’t work when the database is in RESTORING state. You will be blocked by the following error if you attempt to set the database offline:

Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Hope the above helps in moving the database files of the secondary databases which are configured for log shipping. The steps mentioned above works for both data and log files.