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_LOG
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 resulted in:
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.” error.
Best Answer
Here are some notes (for SQL 2000) that might help. I've never tried these personally.
Put the DB into Emergency Bypass Status by doing the following:
Stop the SQL service (instance).
Execute the following in QM:
Revert DB from Emergency Bypass Status by executng in QM:
Cleanup by executing the following in QM: