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:
- I tried to attach the mdf from SSMS by removing the log file item from database to attach list.
CREATE DATABASE Monitoring ON (FILENAME = N'D:\Dump\Data\Monitoring.MDF') FOR ATTACH_REBUILD_LOGsp_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
Best Answer
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.