How to recover database from MDF in SQL Server 2005?

Question

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?

Gunstock

You can check the solution of this query: #38995880 – Leduc

Answer 1

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

Gunstock

The quality of this advice is increadibly bad. Whoever runs into this solution, just steer away. You'll end up like serverfault.com/questions/171145/… – Tania

Could you define exactly what is bad about this advice? The question clearly states that data loss is acceptable. – Gunstock

I need to create some fake accounts, so I can upvote this more. – Underglaze

DONT DO IT Its make me crazy... I can not start server after swap – Bury

Answer 2

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.

Ahq

Answer 3

FROM a post at SQL Server Forums Attaching MDF without LDF:

If you want to attach a MDF without LDF you can follow the steps below It is tested and working fine

1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus

9. Restart SQL server and see the database is online.

UPDATE: DBCC REBUILD_LOG does not existing SQL2005 and above. This should work:

USE [master]
GO
CREATE DATABASE [Test] ON 
    (FILENAME = N'C:\MSSQL\Data\Test.mdf')
    FOR ATTACH_REBUILD_LOG
GO

Mcvey

I came across that article as well. DBCC REBUILD_LOG does not exist in SQL Server 2005. – Gunstock

Using the ATTACH_REBUILD_LOG flag yields the same error message as the original post. – Gunstock

The DB will not always come up as status SUSPECT. It is possible to get the error in this Question, perform these steps, and end up with a DB that's fine (status ONLINE). It depends on what was going on when the DB detached unexpectedly. – Chance

´´Sp_configure "allow updates", 1}´´ No longer Works on sql 2005 or above use ALTER DATABASE yourdb SET EMERGENCY – Birl

if above method fails to recover database from .mdf file then using Free SQL MDF Recovery Tool is the best solution here. – Arta

Answer 4

have you tried to ignore the ldf and just attach the mdf:

sp_attach_single_file_db [ @dbname = ] 'dbname' , [ @physname = ] 'physical_name'

i don't know exactly what will happen to your open transactions (probably just lost), but it might get your data back online.

-don

Dependent

This worked like a charm for me--everything else created errors. – Excellency

dude, you rock big time – Mucronate

Answer 5

See here : Rebuild master and restore system databases from complete disk failure which has a very nice explanation

Disrepair

Answer 6

Found a another way that works completely:

1. Create new database with same name to default database location.
2. Stop SQL server.
3. Copy old mdf file to overwrite newly created mdf file and delete new ldf file
4. Start SQL Server, database will be in emergency mode
5. Detach the emergency mode database
6. Copy original ldf file to default database location (where new LDF file as created and deleted under step 3 above.
7. Attach the database MDF file.

I got a working database after trying all of the above that failed for me.

Reliable

Answer 7

Just had this problem myself, but none of the above answers worked for me.

But instead, I found this which worked a treat and so I thought I'd share this for everyone else:

http://www.kodyaz.com/articles/sql-server-attach-database-mdf-file.aspx

Decide

Answer 8

I hope it is easy to do so,

1. Open SQL Server
2. Click New Query

3. Execute the following query

   sp_attach_single_file_db @dbname='dbname',@physname='C:\Database\dbname.MDF'

Where dbname is you want to show in Object Explorer, where @physname is the local filepath location of your mdf file.

Hope it will help someone, i done by above, got both structure and also data.

Tested in Sql Server 2000 and 2008. In Sql Server 2000 it is not working, but works perfectly in 2008.

Gallimaufry