Emergency mode repair? In a couple of previous posts I explained how the two worst things you could do to your database are rebuilding your transaction log and running REPAIR_ALLOW_DATA_LOSS. Well, in SQL Server 2005, we combined them into a new documented feature!

Are you off your head, I hear you ask? No - and here's why.

(Maybe some other people are asking what 'off your head' means. It's a Scottish thing, as in 'Yer off yer head ya eejit, that's no a real dug'. Translation for all those who watched Trainspotting with subtitles or couldn't understand Brad Pitt's character in Snatch - 'Surely you are mistaken my friend, that's clearly not a real dog'). But I digress...

As you may know, if you don't have a backup strategy then its possible for some hardware problem to corrupt your transaction log so the database in unusable. In this case, you'd be forced to call up Product Support and have them walk you through rebuilding the log and running repair, possibly losing lots of precious business data in the process.

We created emergency mode repair as a documented and supported way of doing all this in a single operation, without the need for calling Product Support (saving you hassle and saving us hassle) and ensuring that anyone who does have to do this runs all the correct steps (saving you hassle and saving us even more hassle - you get the idea)

So what does it do? Once you've switched the database with the corrupt transaction log into emergency mode then the only repair option available is REPAIR_ALLOW_DATA_LOSS. In emergency mode, this does some special operations that it doesn't usually do:

1. force transaction recovery to run, skipping all errors (kind of similar to the CONTINUE_AFTER_ERROR option that we added to RESTORE for SQL Server 2005). This operation scavenges as much out of the transaction log as it can.
2. if any errors were seen in step 1, the transaction log is rebuilt (with the same gut-wrenching tearing noise I alluded to in a previous post - imaging the noise of a boot being pulled out of wet mud)
3. run REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transactionally consistent state.

And if everything goes well then your database should be available again. Now, you'll most likely have lost a bunch of data, broken constraints and inherent business logic in the database but at least you haven't lost everything. Now would be the time to do root cause analysis to figure out what happened and you've just had a painful lesson on why you should have a comprehensive backup strategy ('Yer off yer head ya eejit, that's no a real backup strategy...')

Here are a few things to bear in mind about emergency mode repair:

• it's fully documented and supported. This isn't some dodgy advice off the internet from Random User, its right there in Books Online.
• it's a one-way operation. Anything it does cannot be rolled back or undone. If this worries you (which would surprise me because if someone ever got into that state then surely they don't have the healthy concern about their data that they should have in the first place) then make a copy of the damaged database before you run emergency mode repair.
• as its a one-way operation, you cannot wrap it in an explicit user-transaction.
• it's the only repair option available in emergency mode - if you try to use REPAIR_REBUILD then it won't work.

What if it doesn't work? I've never seen it fail, but it really is the last resort - documented or not - so if it doesn't work or your lack of backups causes you to lose the data from your CEO's favorite customer, start working on your resume...