SQL Server Restore DB from MDF file, lost LDF and DB was not cleanly shutdown

Question

My dev DB server was formatted by some admin without my knowledge. Thankfully I had mdf file kept in some other drive but ldf was in its default location. Since I don’t have DB backup I am now left with mdf file. I googled and found various tricks to restore DB from mdf file. Since database was not cleanly shutdown none of the tricks worked. I have SQL Server 2008 sp2 installed in my machine. As I remember lost DB server was SQL Server 2005 instance. Please let me know how I could recover my database.

Tricks i tried:

1. I tried to attach the mdf from SSMS by removing the log file item from database to attach list.
2. CREATE DATABASE Monitoring ON (FILENAME = N'D:\Dump\Data\Monitoring.MDF') FOR ATTACH_REBUILD_LOG
3. sp_attach_single_file_db N'Monitoring', N'D:\Dump\Data\Monitoring_Data.MDF'

In all above I got

Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options

Then I tried DBCC REBUILD_LOG('Monitoring',N'C:\Program Files ..MSSQL\Data\Monitoring_log.ldf') which also resulted in

Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options

Answer 1

There is a hack you can try: where you create a dummy DB with the same name and size as your old data and log files. Then you have to stop SQL server, replace the new empty files with the old files that have data in them.

Check out this blog link by Paul Randal where the hack is explained in detail. It looks like you have tried the first method mentioned, but read along and try the other methods. Please let us know how it goes. I do not know how well it will work without log files, but it is worth a try.

Answer 2

You can try tools that restore data from MDF files. They don't require LDF files. I don't know of any free ones, but you can try ApexSQL Recover.