Sql-server – attach a damaged mdf file in SQL server

sql server

In SQL Server 2012 I am trying to attach a .mdf file to a database. The thing is there were still open transactions when my server was shut down. Then I reinstall the server and I have deleted the .ldf file.

Now when I try to attach the .mdf file in SSMS, an error says without .ldf file a .mdf file cannot be attached because there was open transaction.

Does anyone know a solution?

Many thanks!

Best Answer

Ypu can use hack attach method described by Paul Randal here: hack-attach a damaged database

To attach the database you need to fool SQL Server into thinking it’s already attached. The set of steps to go through are as follows:

This only works if the server instance you’re using is the same version as the database you’re trying to attach

  • Create a dummy database, with the same name, the same number of data and log files, with the exact same file names (extremely important!) and same file IDs (make sure you have instant file initialization enabled so the file creations don’t take ages – see this blog post). This can be tricky to do if you had added or removed files to the database before it was corrupted, but you need to make sure the file IDs are exactly the same.
  • Set the dummy database offline (ALTER DATABASE RealDatabaseName SET OFFLINE) or shut the server down
  • Delete all the data and log files from the dummy database
  • Drop in as many of the data and log files from the damaged database as possible
  • Set the dummy database online (ALTER DATABASE RealDatabaseName SET ONLINE) or start the server if you shut it down for step 2

In your case you should delete both mdf and ldf files of newly created database and copy only your mdf to the file location.

Your database will be attached but cannot run recovery, so it will be marked as SUSPECT Then you can try to put it into EMERGECY mode and extract as much data as possible.

Seee here an example: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

Related Question