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

I lost the transaction log file. I tried re-attaching the data file without the log file, to create a new log file, SQL Server is giving error:

enter image description here

  • 1
    How did you get this file? It sounds like the DB didn't get shut down cleanly so there are some transactions still being processed in the log. – JNK Apr 9 '12 at 13:21
  • 1
    Sounds like it's a backup of an active database file. This is a very common problem when people don't understand how SQL Server locks files, how transactions work, and how to run a regular SQL Server backup. You're probably not going to be able to attach this database. Do you have a regular SQL Server backup available? – Grant Fritchey Apr 9 '12 at 14:15
  • 1
    There are some hackish workarounds but you will almost definitely lose data even if you do manage to get it to attach. – JNK Apr 9 '12 at 14:24
  • I detached an existing database. Than I deleted the log file and trying to re-attach the database and opting out log file. I did this before as well to get rid of long log file and create a new one. While detaching, SQL Server didn't display an error. I tried the same with another database and it is working. – RPK Apr 9 '12 at 17:27

This is the problem with detaching the database and deleting the transaction log file as a method of keeping the transaction log file short. You can end up with corruption in the database that can't be resolved.

First try attaching the database using the sp_attach_db_single_file system stored procedure. If you can't get it attached using that google for "hack attach sql server" and look at the first link from SQLSkills.com. If that doesn't work then the database is lost and you won't be attaching it back to the SQL Server instance.

Next STOP DOING THIS. Configure the databases for simple recovery so that the transaction log isn't kept any longer than needed. While the transaction log appears to be just a log you can delete isn't actually a very important file that the SQL Server requires to keep the database up and running and in a consistent state.

| improve this answer | |

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