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: Restore
Restore a deleted Azure SQL Database
There could be a need where you have to restore a database that was deleted accidentally due to malicious intent or a plain old mistake (people end up writing resumes for such mistakes) or for some other inexplicable need! This is where backups come into the picture! For the Web and Business editions, this is not an option unless you have exported the database or you had made a copy of the existing database. Note that Business and Web service tiers will be retired in 12 months from April 24, 2014.
If you have just entered the world of Azure SQL Database, then this will seem even more difficult because you do not have the traditional scheduled backup job that you can trigger. So what is possible? Read on to find out…
Windows Azure SQL Database automatically creates backups of every active database using the following schedule: Full database backup once a week, differential database backups once a day, and transaction log backups every 5 minutes. The full and differential backups are replicated across regions to ensure availability of the backups in the event of a disaster. In addition, premium database offers the ability to create secondary databases in different regions to protect against datacenter disasters.
If you have played around with the new Azure SQL Database tiers (Basic, Standard and Premium), you will notice that there is a new tab available for Deleted Databases. This tab shows you the option of restoring a database (Basic, Standard and Premium tiers only) which was recently dropped. See screenshot 1.
I had dropped the bumblebee database which was a Basic tier database on 30th June. I can choose to restore this using the RESTORE button available at the bottom of the page. In the restore settings page popup page, you will have to specify the following:
1. Database name – This cannot be same as the original database
2. The time to which you want to restore available through a slider and text boxes. This is similar to the Timeline feature which was introduced in SQL Server Management Studio.
Note that the target server name cannot be changed. You will have to restore the deleted database backup to the same server. You cannot restore a database backup across Azure SQL Database Servers using this Wizard. Cross server restores are not currently supported.
While the database is restoring, you will see the database in the Databases tab but the status will show up as “RESTORING”. This is currently a PREVIEW feature. So you will need to sign up for the new database service tier preview to explore this feature.
Note that the deleted database backup is not retained indefinitely. As of July 1st, the following retention policy applies for the backups as shown in Screenshot 3:
More about Azure SQL Databases in a future post!
Reference:
Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx
Azure SQL Database Backup and Restore
http://msdn.microsoft.com/en-us/library/azure/jj650016.aspx
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.
Get your log shipping restore to run faster
Recently I was working on a customer environment where the requirement was to use a log shipped secondary database for reporting purposes. The catch here was that the restore of all the transaction log backups needed to complete within a fixed time.
Right at the outset, I would like to point out that the restore of transaction logs are dependent on a number of parameters like disk subsystem performance, number of transactions present in the log backup, resource availability on the server where the restore is occurring. Considering that all the factors remain constant, there is a basic difference between a secondary log shipped database in No Recovery and in Standby mode. In Standby mode, a partial recovery is done with the help of a transaction undo file to get the database into a read-only state. This partial recovery operation and undo that occurs while performing the next log restore (with the use of the transaction undo file) requires time and resources and can slow down the time of restore for each transaction log backup that is restored on the secondary database, especially if there are resource contention issues on the server. In this blog post, I shall outline how you can workaround the same in case you have to meet strict restore time SLAs.
Microsoft TechEd 2012–Multiple rows affected
If you were at Microsoft TechEd India 2012 held at Bangalore, then you would agree with me without a shadow of doubt that it is quite difficult to write a wrap-up post of the entire event. The event for me was what I refer to as PPP. And by PPP, I am not talking about Point-to-Point Protocol. What I am referring to is a self-coined term…. People, Passion and Parallel.
People because it was great to connect a face to the twitter handle or online persona that you have interacted with in the past. What amazed me was some of the folks that I met at TechEd introduced themselves with their Twitter handle first and their actual name later! It is phenomenal to see how social media can empower collaboration between a technical community! The #TechEdIn twitter hashtag was trending on Twitter India in less than 15 minutes after the event started on Day 1 and continued to trend for the next three days!
Passion for technology that was evident from Day 1 to Day 3 where the rooms overflowed with people attending the sessions at different tracks. It was as if people were watching technical blockbusters at TechEd!
Parallel is the name of the game as everything that was happening at TechEd was in parallel… Whether it was the networking that was happening between folks or be in the technical sessions being delivered in parallel at the different tracks or the technology showcases displaying the several cool stuff from the sponsors at the venue. I heard multiple conversations in the hallways where debates were being raged (among the attendees) to decide on which session they should attend under which track! And obviously Twitter stands testament to the fact that such a dilemma existed since there were numerous tweets on this topic!
Since my daily bread and butter revolves around Microsoft SQL Server, it was quite obvious that I was lurking around the sessions which had remotely anything to do with data! On day one, I would be found mostly at “Data and Developer – Synergy with SQL Server” and on day three I was mostly at “Know Your Data – A Day in Life of a DBA”. Though there was a copious amount of information exchange in the session, there were also some really humorous moments which deserve a mention:
Vinod [B | T] and Pinal’s [B | T] session on “SQL Server Misconceptions and Resolution – A Practical Perspective” probably landed them as top candidates for a thin one and the fit one series in case they ever choose to pursue a media related career option!
Pinal Dave’s session on “Speed Up! – Parallel Processes and Unparalleled Performance” left people in splits…
Balmukund’s [B | T] session on “Keeping Your Database Available – ‘AlwaysOn’“ made learning fun and even though he had presented this in the past at other forums… This time around he came back with a totally new look and feel along with a high quality technical session on SQL Server 2012 AlwaysOn features with demos.
The VDI demo that I showed was using the “SQL Server Backup Simulator” which can be downloaded from here. The snapshot demo was shown using a modified version of the code present in the snapshot.cpp file from the Microsoft SQL Server Virtual Device Specification.
I had a lot of fun presenting my session on the “Lesser Known facts of SQL Server Backup and Restore” on March 23rd at TechEd. The slide deck used for the presentation is available below without a few situational humorous elements which wouldn’t really make sense to a person who had not attended the session. (Had to add the disclaimer before I got pulled up with comments that this was the incomplete slide deck!)
Here are some additional resources on VDI and VSS in case you want to indulge in some light reading 
IO Frozen messages while taking NT Backup for SQL databases
INFORMATIONAL- SHEDDING LIGHT on VSS & VDI Backups in SQL Server
Troubleshooting SQL Server Backup Issues
Incorrect Buffercount can cause Out of Memory Errors
VDI (VSS) Backup Resources
How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)
The photographs from the session are available on TroubleshootingSQL’s Facebook page. Last but not the least, there needs to a be special vote of thanks to Pinal Dave who ensured that we had a seamless experience during the event.
The session recording is embedded below:
People… Passion… Parallel… @TechEdIn
TroubleshootingSQL 