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

Click here to get free chapters (PDF) in the mailbox

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

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

Prize

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 (https://blog.sqlauthority.com)