SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

22 SQL SERVER   What is Piecemeal Restore   Quiz   Puzzle   22 of 31

The work of a DBA is never to be underestimated. An effective DBA’s role maybe understated for the 364 days in a year, but that 1 day when the database gets into a corrupt state is when all the skills come to the fore.

 Recently, while working on a 24×7 large database split into multiple filegroups, it suddenly got into a corrupt state. The DBA brought the database ONLINE quickly and was doing a restore process in the background. To understand what he had done, what is Piecemeal Restore inside SQL Server? When was this introduced and what are the options of using this feature?

Link to participate in SQL Quiz

Notes of Vinod Kumar

The behaviors and options of using a Database backup are indeed critical, yet an effective DBA should be able to harness the best results in the shortest time. Hint: it is like a great bookmark to understand the options available when looking for restore strategies with SQL Server. To help you more, read topics around ONLINE restores.

Notes of Pinal Dave

Here are a few quotes from users I often hear:

“I have all the necessary backups but I could not restore the backup.”
“Three hours have passed and yet our restore is still going on?”
“We have a mirroring set up and never faced an issue of corruption.”

When I hear them, I often wonder if they really know the aspects of backup and restore. Piecemeal is another similar concept that people often overlook, ignore and avoid learning. I think all of us should explore this technology further and see if we can avoid saying the above quotes.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#161
Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
Effect of Compressed Backup Setting at Server Level on Database Backup
Target Recovery Time of a Database – Advance Option in SQL Server 2012
A Quick Script for Point in Time Recovery – Back Up and Restore
Copy Database from Instance to Another Instance – Copy Paste in SQL Server
Mirrored Backup and Restore and Split File Backup
Corrupted Backup File and Unsuccessful Restore


There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

2 thoughts on “SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31

  1. hello sir,
    i am using sql server 2008 and i have got 5 tables in my database.
    i want to make sure that 3 of the 5 tables could not be dropped by any user
    (even the admin) and the other two can be dropped.
    the 3 tables (not to be dropped) does not have any referential integrity.
    does this problem have any other solution than schemabinding, i.e. using triggers?


  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #013 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s