5

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?

Community
  • 1
Justin Dearing
  • 2,717
  • 6
  • 36
  • 52

1 Answers1

6

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.

gbn
  • 70,237
  • 8
  • 167
  • 244