Hello everyone, the last week I have not been able to keep everyone as up to date on the Database Corruption Challenge as I would like to. Life has just turned really busy in the last 10 days, but I want to let everyone know that the next corruption challenge is coming soon. There are a few people that I still owe some email responses to from the last challenge, I promise that I will get caught up on those this coming weekend.
In the last 10 days I have presented to an online PASS Virtual Chapter, presented at SQL Saturday Redmond, and presented at the local Bellingham SQL Server users group. I have also had the opportunity to work on corruption in a real world database, utilizing my skills from the Database Corruption Challenge.
The good news is that at 7 weeks into my consulting business, work is good, there is plenty of work out there, and my weekdays are full again. The downside of that is that the Coruption Challenge needs to shift back to weekend time for me as I don’t have time on weekday to respond to all that great solution that people provide.
For those who subscribe to my newsletter you will get the exact adavance notice on the next corruption challenge. Those who aren’t subscribed, check back regularly and know that Challenge #6 is coming in the next few days.
Life is good, its has just been very busy. The next Database Corruption Challenge is coming soon.
Here is how I solved Week 5 of the Database Corruption Challenge. The following steps were tested and confirmed working on SQL Server 2008R2, SQL Server 2012, and SQL Server 2014.
To oversimplify, here are the steps:
- Restore the last known good database.
- Shut down the database, and copy off the last good database files.
- Replace some files and restart it. (Hack Attach)
- Next realizing that the boot page was corrupt page 1:9.
- Shut down the database.
- Copy the boot page from the last good database files and place it in the corrupt file.
- Restart the database.
- Realize there is other corruption.
- Fix the other corruption.
Read more ›
Find out more about the Week 5 Challenge on the overview blog post.
The winning solution, was submitted by Patrick Flynn, just 3 hours and 18 minutes after the challenge was posted. Patrick provided me with the following steps, and TSQL code to back up the steps:
The process was
(a) Restore two copies from backup (Old backup has 400 Customers and 1622 Orders)
(b) Use the supplied MDF, LDF and NDF to hack attach to instance
(c) Try using tail-log backup but log chain broken also as errors in Boot page (1:9) unable to use all files
(d) Instead using original backup replace only log file and ndf file (containing user data)
(e) After switching to emergency mode found errors in Orders table (1:175) but this does not contain new rows
(f) Able to select the newest 181 rows and insert into original orders table
Final Status was
Orders = 12803 rows
Customers = 400 rows
No corruption remaining
Here is the exact TSQL code that supported that solution.
Read more ›
Posted in Uncategorized
Tagged with: LDF
Yesterday was SQL Saturday Redmond. It was a great day, I learned lots, met some great new people, and had a chance to catch up with some old friends. When most people hear about SQL Saturday for the first time, they probably think to themselves “spend all day on a Saturday in technical presentations”, however SQL Saturday is much more than that. The presentations all all great, don’t get me wrong here, but the biggest value that I see is in the people that you get to know, and the things you can learn from the vendors. There were at least 6 people that I know online through Twitter, Fitbit, LinkedIn and other online sites that I had the opportunity to meet in person for the first time this weekend.
Picture Credit: Thanks Jimmy May (@aspiringgeek)
Read more ›
I am attending and speaking at SQL Saturday Redmond today.
My presentation is at 2:45pm and on SQL JOIN Types, and the following topics will be covered:
- Basic SELECT
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- LEFT OUTER JOIN with exclusion
- RIGHT OUTER JOIN with exclusion
- SEMI JOIN
- ANTI SEMI JOIN
- CROSS JOIN
- FULL OUTER JOIN
- FULL OUTER JOIN with exclusion
- CROSS APPLY
- OUTER APPLY
- Multiple combinations of JOINs.
For those of you following the Database Corruption Challenge, I am stepping away from the challenge until at least late Saturday night, or sometime on Sunday. If you are waiting to hear back if your solution was accepted, I probably won’t get to it until Sunday.
If you attended this session and would like to give me feedback, please fill out my feedback form.