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:
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:
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...
PingBack from http://outdoorceilingfansite.info/story.php?id=4419
PingBack from http://outdoorceilingfansite.info/story.php?id=22055
PingBack from http://portablegreenhousesite.info/story.php?id=32093
PingBack from http://thestoragebench.info/story.php?id=5312
PingBack from http://www.officeops.com/blogengine/post/How-to-recover-a-crashed-SQL-database-using-an-intact-Transaction-Log.aspx