I published a blog post on the Tiger blog on a recent change that was introduced for SQLVID. You can also use the SQL Server Backup Simulator which is available on our tigertoolbox GitHub repository for checking backup/restore behavior using SQLVDI APIs. The updated SQLVDI header files required to use VDC_Complete is available on the Microsoft SQL Server Samples GitHub repository.
Tag Archives: Backup
A full house–We could not have asked for more
The turnout at today’s event had an overwhelming response today. A big thank you to everyone who attended today’s event. An overwhelming response… So thank you everyone who attended and made this UG meeting a grand success! This was our very own version of introducing SQL Server 2014 to the SQLBangaloreUG community!
We had a brilliant line-up of topics… Just to re-cap the day.. Here is a short summary..
The day started off with a keynote session from Anupam Tiwari who is Business Program Manager and closely works with the team that supports developer technologies at Microsoft. He talked about where IT as we know it today is headed tomorrow.
Sourabh Agarwal [Blog | Twitter] covered the performance enhancements in SQL Server 2014 and explained why this version is bigger, faster and stronger! 
Then I had the opportunity of explaining the new enhancements in backup and restore in SQL Server 2014. I talked about backing up to a URL i.e. a storage account in Azure, managed backups to Azure and encrypted backups. The slide deck from today’s presentation is available below.
This was followed by Pinal Dave’s [Blog | Twitter] session on “Performing an effective Presentation” One key takeaway from this session is that the art of story-telling can weave out a presentation which can keep the audience hooked! He demonstrated this during his session and kept the audience awake after lunch!
Then we had Balmukund Lakhani [Blog | Twitter] talking about InMemory Enhancements with SQL Server 2014. You can follow his SQL Server 2014 learning series to gain in-depth insights into the various enhancements that he talked about.
His session was followed by Vinod Kumar [Blog | Twitter] who talked about the Some more lesser known enhancements with SQL Server 2014 where he talked about managed lock priority, delayed durability and more.
And last but not the least was Kane Conway – (Support Escalation Engineer at Microsoft) who talked about Power Packed – Power BI with SQL Server. If you have not played around with PowerBI, then you should start now! Presenting data with compelling visualizations just got a new definition.
It was a house full event and I had to put in two pictures to showcase the turnout today! It is not possible to have a successful event without the audience participation. We personally want to apologize to folks who came in late and had to return because the hall was overcrowded and the seats were full. Please do come in early for next UGs for sure.
A special thanks to Microsoft GTSC for giving us the space and the support till date. Also a special thanks to SQL Server Product Marketing team for sponsoring this Launch Event. Finally, a special thanks to team Pluralsight for supporting the UG till date and giving us numerous gifts as giveaway at the event.
The presentation that I used at the event today is available below:
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.
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
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.
Concurrent backups: What is the big deal?
You are probably wondering why am I writing a blog post on concurrent backups! Well this is a topic that brings up a few questions during my customer visits. Apart from that the biggest reason, I want to talk about concurrent backups is because of backup softwares that you have active in your environments.
A lot of the backup softwares available in the market have the option of scheduling backups of the entire system. This involves backing up of SQL Server databases using the interfaces exposed through sqlvdi.dll. Ain’t your software really smart? However, sometimes the right hand doesn’t really talk to the left hand! It is quite possible that your server admins have scheduled backups of your systems around the same time you have scheduled a maintenance plan to take a backup of the SQL Server instance.
If you initiate two differential or full database backups concurrently, then you will experience a blocking condition as shown in the table below. SPID 53 was taking a VDI full-backup using the SQL Server Backup Simulator tool where as SPID 55 was also taking a native full-backup of the same database. In such a scenario, you will find that the backup which was running first will complete and only then will the second backup will continue. The second concurrent backup will be blocked during the entire duration of the first backup.
| spid | blocked | waittime | lastwaittype | waitresource | cmd | dbid |
| 53 | 0 | 6302 | ASYNC_IO_COMPLETION | BACKUP DATABASE | 8 | |
| 53 | 0 | 0 | MISCELLANEOUS | BACKUP DATABASE | 8 | |
| 53 | 0 | 412 | BACKUPBUFFER | BACKUP DATABASE | 8 | |
| 53 | 0 | 412 | BACKUPIO | BACKUP DATABASE | 8 | |
| 55 | 53 | 2386 | LCK_M_U | DB: 8 [BULKOP_BACKUP_DB] | BACKUP DATABASE | 8 |
This issue doesn’t manifest itself when you are you taking a volume snapshot backup of the database. The wait resource will always be DB: <dbid> [BULKOP_BACKUP_DB] with a wait type of LCK_M_U (update lock) for the second session that is running the concurrent backup operation. Concurrent log backups are allowed though starting from SQL Server 2005.
One of the situations where this can pose to be problematic is when the application that installed the database creates a maintenance plan during the application setup process. This can cause problems if you have concurrent full or differential backups scheduled from another software during the same time when your backup maintenance plan is running. Another big pitfall of concurrent backups running is that sometimes, the same job is responsible for both full and log backups of the database. If your log backup job doesn’t kick-off because your database backup is still being blocked, then you could run into a bloated transaction log issue for a database in full or bulk-logged recovery model. My colleague, Arvind, has also blogged about the same issue a few years back.
The following T-SQL script can help you determine if you have overlapping backups being taken for a SQL Server 2012 function. This script can also work for releases lower than SQL Server 2012 if you remove the LAG function as that T-SQL enhancement was introduced in SQL Server 2012.
select backup_start_date, backup_finish_date, LAG(backup_start_date, 1) OVER (PARTITION BY database_name ORDER BY backup_start_date) as previous_start_date, CASE datediff(mi,(LAG(backup_start_date, 1) OVER (PARTITION BY database_name ORDER BY backup_start_date)),backup_start_date) WHEN 0 THEN 'OVERLAPPING' ELSE '-' END as [Status] , datediff(mi,backup_start_date,backup_finish_date) as backup_time_mins, type from msdb..backupset where database_name = '<database name>' -- Replace with appropriate database name and type <> 'L' order by backup_start_date
TroubleshootingSQL 