Tag Archives: Backup Restore

A full house–We could not have asked for more

Posted on by

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! Smile

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! Smile

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.

image image

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:

Advertisement

Differential Base and VSS Backups

Posted on by

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

Some facts about the Database Recovery Advisor and Restores

Posted on by

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 →

TechEd India 2012 Session

Posted on by

TechEd

TechEd India is always a much anticipated event and I always feel that this event keeps getting better year on year. This year I have the privilege of presenting at TechEd which is being held at Bangalore from March 21-23, 2012. My presentation topic is “Lesser Known Facts of SQL Server Backup and Restore”. The topic abstract is as follows:

Backup and restore are considered the most common tasks performed by DBAs. Most of the time the knowledge does not go beyond memorizing the syntax of backup and restore. In this session we will uncover some hidden facts which will change the perspective of DBAs towards this process.

What can you expect from this session?
image1. I will be talking about the some of the unknown facts about SQL Server database backup and restore which will help you streamline your backup performance and DR strategy.
2. Demo of a backup restore using a tool that I have written to show you some interesting statistics.
3. Clearing up some common myths around backup/restore.

I will delivering this session from 3:15PM IST – 4:15PM IST on SQL Server Administration track on Day 3 (March 23rd).

There will be other speakers like Pinal Dave [B | T], Vinod Kumar [B | T], Balmukund Lakhani [B | T] and Jacob Sebastian [B | T] who will not only help you gain knowledge but also have amazing demos in their presentations which will have you running the following query: SELECT REPLICATE(‘awesome’,1000) in your head!

If you haven’t already registered for TechEd yet, then you can do so by visiting the TechEd site. The Facebook event for my session is available here.