Wednesday, January 28, 2009

No backup and the database in suspect mode

This week, I got a call to help out with a database in suspect mode. I tried the usual MS SQL arsenal of tricks to get the CRM-database of that customer back. After more than 20 hours of repair commands (those included a night of sleep) I had to give an negative answer to the customer.

In the afternoon I had another call to ask if we could try to extract the data and dump it into another database or flat file. I had no clue how we could do this since everything in the last 20 hours failed. My collegue Gert Lievens found a technique on the Internet that we never tried before but worked :). We got everything back except for a primary key on 1 table and 1 index on that same table.

This is how it works:
First you change the database from suspect mode to emergency mode. Next you make sure you're the only one using it by forcing it in single user mode (with no wait of course). Then you make a DTS package where you use the copy database component. We configured the package to work in small steps (tables, views, functions, ....) and finally we found that the error was on the primary key and index for that table. So we told the DTS package to make a copy of that specific table but leave the primary key and indexes out. At the end of the day we had an identical copy of the database and a happy customer.

There are some lessons to be learned here:
1. Make sure your backups are ok if you manage a database.
2. There is another technique to get data back that I learned about.

1 comment:

Darrel said...

thanks for this post... it really saved my ass!