How to Attach a database without a transaction log file (.ldf)

Eric Vaillancourt — Thu, 17 Apr 2008 16:46:48 GMT

If you ever lose the drive that contains your log file, your database will become suspect and will stop working.

Notice that you cannot expand the database. If you do, you will get an error message.

You do not need to restore the backup in such a crash. If you do, you will lose everything since your last BACKUP. All you have to do is to detach the database and reattach it without the log. SQL Server 2005 does allow you to do this. You can reattach the database by following these simple steps:

first, you need to detach the suspect database:

The “detach database” dialog appears, click ok.

You might get an error just ignore it and the database will be detached. You will have to refresh the database list to remove your database from the list.

Right-click on Databases and select Attach

The “attach databse” dialog appears. Click on the “Add” button on locate your mdf file.

Under database details, select the .LDF file and click the Remove button.

By doing this, you are telling SQL Server to create a new log file.

Click OK. The database will be attached with a new logfile.

Another way would have been to use the CREATE DATABASE with the FOR ATTACH command:

USE [master]

CREATE DATABASE [Prod_db] ON

( FILENAME = N'C:\data\prod_db.mdf' )

FOR ATTACH

Hope this helps,

Eric Vaillancourt

www.sqlprof.com

Technorati : SQL server 2005,Attach Databse,Log file,.ldf,CRASH

MS SQL Admin : Attach Databse, FOR ATTACH

How to Attach a database without a transaction log file (.ldf)