Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to recover database from MDF in SQL Server 2005?

I have an MDF file and no LDF files for a database created in MS SQL Server 2005. When I try to attach the MDF file to a different SQL Server, I get the following error message.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

I would like to accomplish any one of the following options:

  1. Attach the database without data loss (unlikely but would save me some time).
  2. Attach the database with data loss (whatever transactions were open are lost).
  3. Recover the schema only (no data) from the MDF file.

What SQL commands can I try to get my database going again?

like image 203
Martin Avatar asked Apr 21 '09 15:04

Martin


People also ask

Can we restore database from MDF file?

You can restore your SQL database by repairing the corrupted MDF file with a professional SQL recovery program. And EaseUS MS SQL Recovery is such software that enables you to restore database from MDF file in SQL Server with a few clicks.

How do I open an MDF file in SQL Server?

Launch SSMS -> Connect to the SQL Server instance -> Right-click on Database -> Click Attach. In the new Locate Database Files window, browse the file system to locate the MDF file. Double-click it. The associated data files and log files are populated in the associated files grid view in the Attach Databases window.

How do I import MDF and LDF files?

Run SQL Server management studio as an administrator and attach the database. Explicitly grant full control access to the MDF file and LDF file of the database. To do that, Right-click the database files Select the security tab select the appropriate user and grant full control to the user.


2 Answers

I found the following document on Experts Exchange.

patrikt: You will have data loss but it can be done.

1. Detach database and move your mdf to save location.
2. Create new databse of same name, same files, same file location and same file size.
3. Stop SQL server.
4. Swap mdf file of just created DB to your save one.
5. Start SQL. DB will go suspect.
6. ALTER DATABASE yourdb SET EMERGENCY
7. ALTER DATABASE yourdb SET SINGLE_USER
8. DBCC CHECKDB (yourdb, REPAIR_ALLOW_DATA_LOSS)
9. ALTER DATABASE yourdb SET MULTI_USER
10. ALTER DATABASE yourdb SET ONLINE
like image 67
Martin Avatar answered Oct 21 '22 10:10

Martin


Here are details that cover parts 2) and 3) in case re-creating log doesn’t work which can happen if MDF file is corrupted.

You can recover data and structure only by reading MDF file with some third party tool that can de-code what’s written as binary data but even with such tools you can’t always do the job completely.

In such cases you can try ApexSQL Recover. From what I know this is the only tool that can do this kind of job but it’s quite expensive.

Much better idea is to try to recover these from any old backups if you have any.

like image 32
George Ober Avatar answered Oct 21 '22 11:10

George Ober