Blake Savage gazed in horror at the string of errors in the database activity log of his communications module. It was always at the worst possible moment that SQL Server grenaded itself and produced a corrupt database. Without the database data, all off-planet communications were impossible! How were they to get back to their ship?

The front of his state-of-the-art ‘Goldfish’ helmet began to steam up, as panic set in.

“Captain”, shouted Blake over his futuristic intercom, “it’s happened again, damn it.”

What could have caused such a travesty in his wonderful chrome-plated rig, he mused, moodily. Maybe the strange acidic sands of Moon Lunix 245B had penetrated the disk of the machine.

“No worries,” said Captain ‘Pop’ Rivett airily, as he swayed into view over the Moon’s soft surface. “The database is using the full recovery model so just restore it from the last good backup, followed by the transaction logs, in sequence”

Blake swore under his breath. Somewhere far above them cruised their great intergalactic battleship, the SS Sicromoft. There, in the locker of his cabin, lay the last known good backup, safe within its Formica package. He froze in panic at the implications of this.

“You have got your last-known good backup haven’t you?” asked Pop, instinctively flicking the dial on his stun gun to ‘maximum pain’.

Observing the beads of sweat that had appeared on Blake’s brow, Pop clenched his teeth and tightened his grip on stun gun, before sighing wearily and returning it to its holster.

“Right, so the database is coming up with a string of DBCC errors, showing all the symptoms of a torn page, and … for whatever reason …” he said, glaring meaningfully at Blake, “there’s no backup. The first thing to do is to stay calm. You may still be able to get the data off the database. Let’s go through this step by step.”

1. Get the database up and running, flagged as ‘suspect’

First, you need to get to the stage where the database is at least running, but flagged as ‘suspect’. Restart SQL Server. It will attempt to recover the corrupted database. The Recovery process makes the database consistent by redoing or undoing all the transactions that were either started after or uncommitted at the time of the last checkpoint. This involves reading each log record, comparing its timestamp to the timestamp of the corresponding database page, and either undoing the change (in the case of an uncommitted transaction) or redoing the change (in the case of a committed transaction). If SQL Server cannot complete the recovery procedure it sets one of the bits in the status field in the sysdatabases table so that the database is flagged as ‘suspect’.

Blake Savage tapped nervously at the keyboard. “It looks like the file is too badly corrupted even to get it to that stage!” he cried forlornly.

“All may not be lost” said Pop “If the database is too damaged to allow this, there is still a chance of rescuing the situation. Try creating a database with an mdf database file of the same size as the old one, then stop the server, copy the corrupt mdf over the newly created one and finally restart the server.”

“OK, that seems to have worked. The database is started up and flagged as being ‘suspect’. But now what? What good is a ‘suspect’ database?” Blake remained uninfected by any of Pop Rivett’s optimism.

2. Attempt to recover the suspect database

Firstly, note the errorlog message that is causing recovery to fail. You now need to get the server to retry recovery. First, however, you need to reset the ‘suspect’ flag. And to do that, you’ll have to allow updates to system tables:

Sp_configure ‘allow updates’, 1
Reconfigure with override

Of course, you need to remember to set it back afterwards! Anyway, with the system table updates enabled you can attempt to reset the ‘suspect’ status by executing:

sp_resetstatus ‘mydbname’

Or:

update master..sysdatabases SET status = status ^ 256
  where name = ‘mydbname’

OK, now restart the server. If you are in luck, the database will recover the second time.

“No!” wailed Blake, “it’s still set in ‘suspect’ mode! We’re doomed!”

3. If recovery fails, set the database to Emergency mode

“Not quite” replied Pop sagely. To be honest I suspected this would be the case. If you really do have a ‘torn page’ error, then the database will definitely stay in ‘suspect’ mode. You simply need to move on to the next step, and that’s to put the database into ’emergency’ mode.

“Emergency mode?” Blake gulped.

Yes. If you can do this, then you will be able to get to as much of the data as possible. To set the database to ’emergency’ mode you should use:

update master..sysdatabases set status = status |  32768
  where name = ‘mydbname’

Incidentally, for v7 I believe the equivalent command was:

update master..sysdatabases set status = status |  -32768
where name = ‘mydbname’

After issuing this command you must stop and restart the SQL Server service. While a given database is in Emergency Mode, SQL Server will not attempt to restore it when starting up. On start-up the database will still be in emergency mode, but it should allow you to access the data.

“It worked!” cried Blake, for the first time allowing a hint of optimism to creep into his voice.

4. Transfer all non-corrupt data to another database

“Excellent” said Pop, encouragingly. If the database is now accessible, you should be able to read data using standard techniques. The data can be transferred to another database via DTS, BCP or SELECT queries – I prefer BCP native format.

Of course, you will get an error when trying to access the corrupt data but once you know the ranges of the data where the errors will occur, you can use WHERE clauses and indexes to access data around the corrupt pages.

5. Get your sorry ass out of there

“So, we have a chance of getting off this godforsaken moon and back to the SS Sicromoft after all!!!”

“We?” said Pop, affecting bemusement, “I shall be going back to the ship right now, by means of my one-man, officer-class, personal escape capsule. As for you, it all depends on whether you can get the data restored to the point where you can radio in your position. Good luck old boy. Toodle Pip!”

“Smoke me a kipper, I’ll be back for breakfast…!!!” shouted Blake defiantly, but somewhat pathetically, as he returned his attention to his wounded server.