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:
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 only takes a minute to sign up.Sign up to join this community
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.