attach a damaged mdf file in SQL server

Question

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!

Answer 1

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

Answer 2

Attach MDF File Without LDF file by using T-SQL script: You can run a TSQL Script on SQL Query to attach your MDF file and recreate your transaction log file.

Method 1:

Create Database testdb ON
(FILENAME=N'C:\File location of your mdf file\testdb.mdf')
FOR ATTACH_REBUILD_LOG
Go

For Example Here testdb.mdf is your (.mdf) file name.

Note : These methods will work only if your database is cleanly shut down and you have a healthy MDF file database. Be sure you have valid mdf file backup.

Method 2:

USE [master]
GO
CREATE DATABASE [Testdb] ON
( FILENAME = N'C:\File location of your mdf file\Testdb.mdf' )
FOR ATTACH
GO

Trying to Attach the Damaged SQL Server Database

You can also use stored procedure to attach the (.mdf) file without (.ldf) file.

USE master 
GO 

EXEC sys.sp_attach_single_file_db @dbname = 'TestDB', 
    @physname = N'C:\File location of your mdf file\TestDBCopy.mdf' 
GO

Note:

This feature (sp_attach_single_file_db) will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (SQL Server Transact-SQL). Do not use this procedure on a replicated database.

We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

For your further ref Here & Here and How to Attach a SQL Server Database without a Transaction Log and with Open Transactions