Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
Why do you not have a backup? –  Aaron Bertrand Apr 2 '12 at 10:12
    
Good question... But unfortunately I don't :( –  Krishnaraj Barvathaya Apr 2 '12 at 10:47
add comment

2 Answers

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.

share|improve this answer
    
I have even tried this option too. No luck... –  Krishnaraj Barvathaya Apr 3 '12 at 12:08
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.