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:
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.
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.