Restoring an sql 2000 database from mdf file only

A few days back, a pc’s hard disk got infected with bad sectors. Old disks tend to do so. Anyway, I had to recover all the SQL server 2000 databases because nobody was keeping backups. Looking around the net I found the following steps which worked just fine for all my sql 2000 databases but not for the 2005 ones. So let’s get started. We do have the mdf file only. I assume the log file (ldf) is either corrupted or we couldn’t even recover it. That’s ok if you follow the next check list:

1. Create a new database with the same name and the same mdf and ldf files
2. Stop sql services (through the sql server service manager)
3. Replace the new mdf file with the one you recovered from the disk
4. Start sql services
   By this time you should have an attached suspect database (something you could not do otherwise if you miss the ldf file).
   
   
5. Fire up query analyzer and connect to the sql server. What we will do next is, allow updates on system tables. This is done with the following sql code:
   
   
   
   Use Master
   
   Go
   
   sp_configure 'allow updates', 1
   
   reconfigure with override
   
   Go
   
   
   
   
6. Now we have to note down the status of the attached database. In order to find out the current status simply execute the following sql code:
   
   
   
   select status from sysdatabases where name='DBNAME'
   
   
   
   
7. Now it’s time to set the database into emergency mode. Emergency mode bypasses recovery and allows us to execute the restoration commands (keep patient). In order to set the database into emergence status execute the following command:
   
   
   
   update sysdatabases set status = 32768 where name = 'DBNAME'
   
   
   
8. Restart the sql services (you’ll have to stop services and then start them up since restart became available into gui on Sql Server Management studio 2005)
9. Now it’s time to rebuild the missing log file. Do this with the following command:
   
   
   
   DBCC REBUILD_LOG('DBNAME', 'PHYSICAL_PATH_TO_LOG_FILE')
   
   
   
   Where DBNAME is the actual database’s name (I hope you have figured that out till now) and PHYSICAL_PATH_TO_LOG_FILE is the physical path to the new log file i.e. c:\MyDBS\NewDB.ldf. As you may have also noticed it does require the single quotes. So if I had a NewDB database the command I would issue would be the following:
   
   
   
   
   DBCC TRACEON (3604)
   
   DBCC REBUILD_LOG('NewDB', 'c:\MyDBS\NewDB.ldf')
   
   Go
   
   
   
   If the command is successful, the following message appears in red letters:
   
   
   
   
   Warning: The log for database 'NewDB' has been rebuilt.
   
   Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency.  Database options will have to be reset, and extra log files may need to be deleted.
   
   
   
   
10. In order to checkdb we have to set the database into single user mode.:
    
    
    
    sp_dboption 'DBNAME', 'single user', 'true'
    
    
    
11. Do run the checkdb
    
    
    
    DBCC CHECKDB('DBNAME')
    
    
    
12. Now it’s time to restore the status of the database to its original value (the one noted on step 6). In the following code ORIGINALVALUE is a number so no quote is required:
    
    
    
    update sysdatabases set status = ORIGINALVALUE where name ='DBNAME'
    
    
    
13. Restore the database to multi user mode
    
    
    
    sp_dboption 'DBNAME', 'single user', 'false'
    
    
    
14. Turn off the updates to system tables
    
    
    
    sp_configure 'allow updates', 0
    
    reconfigure with override
    
    Go
    
    
    

Hopefully by now you do have a working database which may be consistent and you may be god blessed and not have lost any data. One thing is for sure. If you have been through this, the next time you are asked to setup a sql server you’ll probably not forget or neglect to setup a backup mechanism.

Special thanks on MohammedU who inspired me on rewriting the walkthrough in a (hopefully) more clear way. The original post can be found here.