Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We had a crash in the sql server and the database remained in inconsistent mode. We lost the transaction log file and the last generated backup is too old.

I googled this issue but mostly relevant posts I saw refer to SQL 2000

I saw this post http://stackoverflow.com/questions/773059/how-to-recover-database-from-mdf-in-sql-server-2005 and run the steps:

  1. Detach database and move your mdf to save location.
  2. Create new database 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 "

I got till step 8 [DBCC CHECKDB (yourdb, REPAIR_ALLOW_DATA_LOSS)] but each time I repeat the processes "DBCC CHECKDB" does not seems to do the job .

I get error "query completed with errors" and that the "Database 'dbname' is being recovered. Waiting until recovery is finished."

Any Help will be greatly appreciated Thanks Ron

share|improve this question

migrated from stackoverflow.com Aug 16 '10 at 19:45

This question came from our site for professional and enthusiast programmers.

2  
I can't believe you blindly followed such a bad advice as the one in the link... I feel no empathy for your problem. –  Remus Rusanu Aug 16 '10 at 22:37

1 Answer 1

Take a look here: Recover a database without the ldf file to use the SP_ATTACH_SINGLE_FILE_DB proc

share|improve this answer
1  
+1 I'd also add you can't bolt a random LDF onto the MDF. There are internal identifiers + LSNs to think about –  gbn Aug 16 '10 at 19:10
    
OP is unable to comment but notes "when run this script SP_ATTACH_SINGLE_FILE_DB I get "DB was not shut down cleanly" –  Jeff Atwood Aug 19 '10 at 12:39

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.