Friday, May 30, 2008

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.

23 comments:

Anonymous said...

I just used this, Thanks

Alex said...

There is fine tool-sql mdf recovery,repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension), can process
huge databases, their file size can reach 16 TB, supports data extraction via the local area network, you can save recovered data as SQL scripts, it is also possible to split data into files of any size, can save extracted information directly to the SQL server. It allow to recovery SQL Server faster.

Myrna said...

Good words.

Alexis said...

For realize this actions for solve the problems,I recommend next software-sql server repair,application helped me many times and has not one facility,software repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

CAO said...

Excellent, Thanks.
I have just used this way to restore a database only from mdf file.

Anonymous said...

AWESOME! Thanks for the instructions! Worked like a charm!

Anonymous said...

WOW!!! Sincere thanks for taking the time to post this. I thought I was "hozed".

Worked like a charm!

Alex said...

Yesterday I had one unpleasant problem.Something happened with my sql server and my site was working very bad.To my surprise one man advised me-repair sql server 2005 in 2 hours.And utility solved my issue in seconds and free of cost.Besides it showed me how repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Anonymous said...
This comment has been removed by a blog administrator.
David said...

Nice, really nice.
But one thing that i want to share is that sometime the process that you discuss fails to access mdf file. During that time you need to use mdf repair software to repair and access your mdf file.

absithick said...

It was awesome to recover the corrupted database. Cheers to Blogger!!!!!!!!

miked said...

Worked! I had to use:

alter database db-name set SINGLE_USER

and

alter database db-name set MULTI_USER

to get in and out of single user mode, but otherwise it saved my bacon. Thanks!

Dhinesh sk said...

Very thanks... I restored database with out data loss...

folk jenkins said...

Try to repair sql database owing to SQL Server Fix Toolbox software is able to restore sql db of more than 2GB

james mascarenhas said...

hi,
If you wish to recover 100% of the data element of the .mdf and .ldf then install the SQL Reocvery Software and bring back the corrupt database back to normal.
Regards
James

john bell said...

When I have lost my sql database then I am in very big problem. There are so many data records available in the database that is very important for me then I have downloaded sql database recovery tool by which it is possible to recover sql data.

John Brad said...

SQL repair tool comfortably able to repair large sized MDF Files with its crucial objects in much lesser time. Read for more info :- Repair MDF File of SQL Server || Data Recovery and Email Management

Kaye R. Jenkins said...

Get Sql Database Repair Tool that can also repairs corrupt MDF file of the SQL Server database created all the SQL Server including 2012/2000/2005/2008/2008 R2 and gracefully recovers SQL XML data type files also.

See more:- http://www.filesrecoverytool.com/sql-database-repair.html

Maria Clark said...


Powerful SQL Database Recovery software retrieve MDF and NDF file from SQL server database. In addition, this utility repairs all your SQL server database tables, views, triggers, stored procedures, user defined functions, rules and indexes from the corrupted MS SQL database.

Download now:- http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

rodick willision said...

Get more effective highly advanced software, you can use Kernel for SQL Database Recovery Tool. This software quickly recovers lost data, tables, views, stored procedures, rules, defaults, user defined data types and triggers from corrupt MDF database. To know more detail click here - http://www.sqlrecoverytool.mdfrecovery.org

john doe said...

Especially develop for such purposes like to repair many damaged database files (MDF) of SQL Server. It thoroughly scans corrupted MDF file without making any changes to the original files and displays all the recovered data including objects like Key, Rules, views, Index, triggers, etc.

Download Now : http://www.recoveryfix.com/sqldatabaserecovery.html

John Brooks said...

To fix all error of SQL server data use SQL Database Recovery software is the best SQL database recovery tool that comes with plethora of options to recover data from damaged or corrupt SQL databases. The software can repair both the MDF and NDF SQL database files.

So download now: http://www.tools4recovery.com/sql-database-recovery.html

John Walker said...

You can also take the help of this link to attach mdf file without ldf file: http://www.sqlserverlogexplorer.com/how-to-attach-mdf-file-without-ldf-file/