I do this, so you don’t have to…Corrupt Database

Every now and then, we are in the fortunate position to walk into the office, only to be confronted by this glaring screen.

At this point, we normally start muttering to ourselves, all sorts of obscenities…
After all the fun has been taken out of the morning, we realise, that right now, it is up to you to save the day.

You start checking your backups, and going through the normal procedure of restoring, when an
angry man starts screaming about all the work that will be lost if you restore to the last backup.

Sigh… Ok. What now?
Well, not to fret, there is some light at the end of the tunnel. First things first. Before going any further, I recommend you stop SQL Server, and make copies of the offending DB’s files so that you can always come back to this point, if all else fails. You want to see what is actually wrong with this DB. So you do the logical thing.

DBCC CHECKDB, only to be confronted with the very obvious answer of… The DB is SUSPECT!!!

No, really? I couldn’t tell that from the object explorer, and then I decided to do a check DB, just for you to confirm my suspicion all along. Thanks SQL. Then something in the back of your mind
comes creeping up. A little voice telling you, to change the DB status to something a little more friendly...

So you start by doing the following:
Change the suspect status by sp_resetstatus , which turns off the suspect flag on a database, but leaves all other database options intact.

If you were to run a SELECT DATABASEPROPERTYEX('YourDbName','STATUS') you would now get an ONLINE status.

Now you want to declare an emergency situation, and go and get some coffee, because whatever comes up after this, is what you will need to fix. This is also the point where you start to consider that maybe the current backup strategy is not really sufficient for this specific DB.

So you use ALTER DATABASE (YourDbName) SET EMERGENCY

Ok, coffee down, now you can start digging around. You do your DBCC CHECKDB again, and look for any obvious errors. In my case, I know that the log file was corrupt (As I actually broke it myself) and work through the rest of this result set just to confirm that nothing else was damaged.

Once you have your problem area identified, you can now decide whether it will be advisable to continue marching on through the process of recovery. You decide that there will be some data loss, but overall, your results should be quite good. And as this is one of the few options left for
you, you push on.

ALTER DATABASE (YourDbName) SET SINGLE_USER WITH ROLLBACK IMMEDIATE

You do this in order for SQL to be able to repair your corrupt DB without outside interference.

Then you attempt the repair:
DBCC CHECKDB (‘YourDbName’, REPAIR_ALLOW_DATA_LOSS)
From here on you hope that the angry man screaming at you hasn’t lost too much data, and allow SQL to fix the problem areas.

As you can see, in my scenario, it actually recreated the Log file.

Once all these steps are completed, all that’s left to do is to give the DB back to the users, too test, and look at any potential missing data records.

This might be inevitable, as there might be corruption on specific areas that they used, and as such, SQL will discard these changes, to get to a good state. Explaining this to the angry man, might be a bit more difficult…

You do this with the command ALTER DATBASE (YourDbName) SET MULTI_USER

You will now see this beautiful screen, where you can sit back and relax, and enjoy that second cup of coffee.

Obviously, this leads you back to that thought of the backup strategy. You have to be prepared for these sort of eventualities, and revisit DR situations as often as possible, to avoid these little problems as best you can.

PS: If all of these steps fail, remember that first recommendation of copying your files away first. You can always try to extract the data from a suspect DB manually. Again setting it into emergency mode, and single user, so that you can try and access the data, to export to a new DB. If you need more info on this, let me know, and I will be glad to help.