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.

Possible Duplicate:
How do I attach a database in SQL Server?

I was given an MDF for a database, no backups and not LDF that I was asked to attach the database to a SQL server. I get the following error:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER2K8\MSSQL\DATA\something.LDF" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

I tried Paul Randall's method of creating a database of the same size, putting it in emergency, single user mode and attempting to set it online that way. Is there any other option or do I tell the person that gave me this file that the data is corrupt?

share|improve this question

marked as duplicate by JNK Dec 14 '12 at 17:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Did you also try this? -> windowscoding.com/blogs/blake/archive/2007/10/06/… –  Martin S. Stoller Oct 13 '11 at 21:09
    
I had the same issue too in SQL2K and tried the steps mentioned in Paul's blog, but it didn't help. Even the sp_attach_single_file_db didn't work. There should be some way to tell SQL to attach the mdf as is. –  StanleyJohns Oct 14 '11 at 4:40

1 Answer 1

When a database is detached, it should shut down cleanly. This file has been shadow copied or has been taken from a crashed SQL Server Instance or such. That is, the MDF is not from a cleanly detached, shutdown database.

So, possible solutions..

  1. I don't think this will work thought but please check. Try SP_ATTACH_SINGLE_FILE_DB from a query window. It is clearer than the SSMS GUI attach pop-up. Note, this is deprecated in favour of CREATE DATABASE .. FOR ATTACH_REBUILD_LOG which states as a requirement: A clean shutdown of the database.

  2. But I'm sure that you'll have to go back and state you have a corrupted file.
    Better still, ask for a BACKUP which will be smaller and consistent.
    If your source says they can attach it, they'll be lying or losing data.

  3. Looking at SO and SF, there appears to one approach that will cause data loss. You can find that for yourself: I don't endorse it.

share|improve this answer

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