September 8, 2018 at 1:05 am
Hope someone can help a bit. I've been going through some practice questions online for 70-765 and there is a mistake in the sample. But then it got me thinking to which is the right answer as the question is a little vague.
I pasted the question below too. But here is the link: Freecram.com/question/Microsoft.70-765.v2018-03-20.q60/you-have-a-database-named-db1-that-uses-simple-recovery-mode-full-backups-of-db1-are-taken-daily-and
They say the answer is B - restore the database with page, but the database is in simple recovery. You can't restore a page while in simple recovery.
So C and D are left. I would have opted for D - Reapair_rebuild, but the question states that you use sys.columns to discover the corrupt pages. But these wouldn't be non-clustered indexes returned by sys.columns.
Leaving C - Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS.
Does anyone else consider this to be the right answer?
Anything anyone can add?
Question:
You have a database named DB1 that uses simple recovery mode.
Full backups of DB1 are taken daily and DB1 is checked for corruption before each backup.
There was no corruption when the last backup was complete.
You run the sys.columns catalog view and discover corrupt pages.
You need to recover the database. The solution must minimize data loss.
What should you do?
A. Run RESTORE DATABASE WITH RECOVERY.
B. Run RESTORE DATABASE WITH PAGE.
C. Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS parameter.
D. Run DBCC CHECKDB and specify the REPAIT_REBUILD parameter.
Correct Answer: B A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
RESTORE DATABASE WITH PAGE
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models.
References: r/https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements- transact-sql
September 9, 2018 at 4:34 pm
Looks like you're discovering the value of cram sites. Hope your box didn't get rooted going there.
Eddie Wuerch
MCM: SQL
September 9, 2018 at 4:54 pm
Eddie Wuerch - Sunday, September 9, 2018 4:34 PMLooks like you're discovering the value of cram sites. Hope your box didn't get rooted going there.
Thanks for the concern.
September 10, 2018 at 7:45 am
The answer here is A. You restore the database since there was no corruption in that backup. Of course, you need to re-run dbcc to verify this after the restore.
September 18, 2018 at 5:20 pm
Steve Jones - SSC Editor - Monday, September 10, 2018 7:45 AMThe answer here is A. You restore the database since there was no corruption in that backup. Of course, you need to re-run dbcc to verify this after the restore.
Thanks. My issue with A is that the you have lost all the data since the backup was made. I was hoping for something that would have resulted in less data loss.
September 19, 2018 at 4:55 pm
I agree with you that option D is the lesser of those four evils.
The correct answer is:
E - Run CHECKDB with ALL_ERRORMSGS, NO_INFOMSGS, and no repair options to determine the correct path to a solution.
Until you've checked the entire database, you don't know the blast radius of the corruption, or if you even need to go to a backup. There are a variety of corruption issues, such a a damaged index page, can be easily fixed by CHECKDB without any loss of data. Sys.columns is a view over several internal system tables, and there is at least one non-clustered index in play (sys.singleobjrefs.nc1). A complete CHECKDB analysis run should be the first step of any corruption remediation.
But that's not a listed answer, so let's look at what's left:
Each of the other answers is wrong for various reasons:
A. Run RESTORE DATABASE WITH RECOVERY.
Would lose all changes recorded since the last recoverable backup, which might not be the most recent backup (the line 'There was no corruption when the last backup was complete' has no bearing on the quality of the most recent backup. Also, you can back up a corrupted database and restore it without hitting errors or any mention of the corruption contained therein.). Since you may be able to clean the corruption without losing data, this is a wrong choice that might become the unfortunate right choice depending on the depth of the damage.
B. Run RESTORE DATABASE WITH PAGE.
The database is running in the Simple Recovery Model. Page restores are not possible in databases running in the Simple Recovery Model, making this an incorrect choice. (thus my comment about the quality of cram sites - their 'correct' answer is the only one that is absolutely wrong) Also, there are a variety of metadata page types that cannot be page-level repaired under any recovery model. It's a great technique for the data and index pages that make up the majority of databases, though, once you've run CHECKDB and can examine which pages are affected.
C. Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS parameter.
You don't know the extent of the damage, you can't measure the extent what the repair will delete. Some types of corruption repair can drop a table that, although damaged, would otherwise hold data that can still be queried out of the table (saving as much data as possible) before the repair process drops it. REPAIR_ALLOW_DATA_LOSS could delete more data than what would be lost by going to a backup potentially several days old. (Again, taking a backup does not mean you can restore a database from that backup - you will build DR plans with a higher success rate if you always assume the most recent backup is corrupt and you'll need one or two older backups in order to pull off a successful restore). The full CHECKDB should tell you where the damage is so you can determine what, if anything, would be lost in a REPAIR_ALLOW_DATA_LOSS operation. If the repair only blows up a few tables that held only stale or otherwise never-used data, that's not Data Loss - that's Good Database Hygiene 🙂
D. Run DBCC CHECKDB and specify the REPAIR_REBUILD parameter.
This is the closest-to-'correct' answer of the list provided. It may not fix the corruption, but it won't destroy any data in the process. It would result in a corruption-free database with no lost data in many situations. It's the only option on the list that runs DBCC CHECKDB and doesn't lose data.
-Eddie
Eddie Wuerch
MCM: SQL
September 26, 2018 at 11:06 am
Eddie Wuerch - Wednesday, September 19, 2018 4:55 PMI agree with you that option D is the lesser of those four evils.The correct answer is:
E - Run CHECKDB with ALL_ERRORMSGS, NO_INFOMSGS, and no repair options to determine the correct path to a solution.
Until you've checked the entire database, you don't know the blast radius of the corruption, or if you even need to go to a backup. There are a variety of corruption issues, such a a damaged index page, can be easily fixed by CHECKDB without any loss of data. Sys.columns is a view over several internal system tables, and there is at least one non-clustered index in play (sys.singleobjrefs.nc1). A complete CHECKDB analysis run should be the first step of any corruption remediation.But that's not a listed answer, so let's look at what's left:
Each of the other answers is wrong for various reasons:
A. Run RESTORE DATABASE WITH RECOVERY.
Would lose all changes recorded since the last recoverable backup, which might not be the most recent backup (the line 'There was no corruption when the last backup was complete' has no bearing on the quality of the most recent backup. Also, you can back up a corrupted database and restore it without hitting errors or any mention of the corruption contained therein.). Since you may be able to clean the corruption without losing data, this is a wrong choice that might become the unfortunate right choice depending on the depth of the damage.B. Run RESTORE DATABASE WITH PAGE.
The database is running in the Simple Recovery Model. Page restores are not possible in databases running in the Simple Recovery Model, making this an incorrect choice. (thus my comment about the quality of cram sites - their 'correct' answer is the only one that is absolutely wrong) Also, there are a variety of metadata page types that cannot be page-level repaired under any recovery model. It's a great technique for the data and index pages that make up the majority of databases, though, once you've run CHECKDB and can examine which pages are affected.C. Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS parameter.
You don't know the extent of the damage, you can't measure the extent what the repair will delete. Some types of corruption repair can drop a table that, although damaged, would otherwise hold data that can still be queried out of the table (saving as much data as possible) before the repair process drops it. REPAIR_ALLOW_DATA_LOSS could delete more data than what would be lost by going to a backup potentially several days old. (Again, taking a backup does not mean you can restore a database from that backup - you will build DR plans with a higher success rate if you always assume the most recent backup is corrupt and you'll need one or two older backups in order to pull off a successful restore). The full CHECKDB should tell you where the damage is so you can determine what, if anything, would be lost in a REPAIR_ALLOW_DATA_LOSS operation. If the repair only blows up a few tables that held only stale or otherwise never-used data, that's not Data Loss - that's Good Database Hygiene 🙂D. Run DBCC CHECKDB and specify the REPAIR_REBUILD parameter.
This is the closest-to-'correct' answer of the list provided. It may not fix the corruption, but it won't destroy any data in the process. It would result in a corruption-free database with no lost data in many situations. It's the only option on the list that runs DBCC CHECKDB and doesn't lose data.-Eddie
Thanks for the detailed run down.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply