Smit Shah's Blog

Server Crash / Power Lost / File Corrupt

One of our mobile computers lost power and the server file got corrupted.

Here is what we did...

1. Backup the data (.mdf) file! Just in case. We take no responsibility
   for anything that happens following this procedure.
2. EXEC sp_detach_db 'dbname' -- this will detach the database from
   the server
3. Restart SQL Server
   The database may still be seen in enterprise manager, but just ignore
   it.
4. Create a new database with the same name or a different name. You
   will have to use a different physical file name, which is fine.
5. Stop SQL Server.
6. Rename the new data file that was created to something else (ex: add.bak
   to the end)
7. Rename the old data file that you want to restore to the name of the
   newly created file (the same name as the file you changed in the step
   above)
8. Start SQL Server
   Now the db will still be suspect but you now have a log file.
9. Switch to emergency mode on the database. You do this by doing the
   following:
   
   
   
   1. Right click on the database root node in Enterprise manager and
      bring up the properties.
   2. Under the Server Settings tab, check of "Allow modifications
      to be made directly to the system catalogs".
   3. click ok
   4. Now go to the master database and open the sysdatabases
      table.
   5. Find the suspected database in here and modify the status column,
      setting it to: 32768. This will put it into emergency mode.
   6. stop then start sql server
   
   
10. Now here's the tricky part and I'm not sure how this will work on
    a single install, i was lucky enough to have SQL Server 2000 installed.
    But anyways, open up the Import and Export Data (DTS) program from the
    start menu. And you want to copy data from the old database to a brand
    new one. Just copy tables and views.

Reference: http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html