Thursday, November 15, 2007

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
  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
  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

    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
    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.


This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]