Today, I had a call (on IM) from a friend in deep trouble. As usual, it was their fault completely... They did not make any backups for their DataBase !!! and sure enough, they didn't relize what they were doing until disaster happened... Looks like they didn't make it a requirement during a test phase (foolishness!) and didn't make any new requirement when going to production !!!
Anyway, the DataBase guy claims that all of a sudden the DataBase (on SQL Server 2000) wasn't there anymore. the .mdf and .ldf files where still there... he tried to attach the database, but it wouldn't work (they wheren't properly detached)... So they called on my friend, who had built the solution for them. She didn't have any database recovery skills (neither do I for that matter), and she pinged me. I tried the usual recovery techniques, like starting from an old database and try to recover from the transaction log, but it didn't work (transaction log corrupt). Then we tried the sp_attach_single_file_db with only the mdf file, but it kept asking for the ldf file. I googled a bit, and stumbled on DBCC Rebuild_Log. At that point, our problem was not to have consistant data, but to have any data at all, so we were ready to try desperate moves... However, before doing the trick, we needed the DataBase to be attached (even if it is in a suspect state). So what we did is create a new DB with the same name and file locations, stop the SQL Server, and replaced the mdf of the new empty database with the mdf from the broken database, and restarted SQL Server. The DataBase showed as suspect. then We executed a DBCC Rebuild_Log. Restarted SQL Server, and the DataBase showed up. After checking consistancy and the data, it looked like everything was there...
Well, I hope they learned the lesson, and that the first thing they would be doing tomorrow morning is creating a Disaster Recovery plan and testing it correctly...
I am quite happy I could help on a disaster recovery on SQL server... never thought that could ever happen.
Remember Me