How to attach .MDF file without .LDF file into Database SQL Server 2005

When your .LDF file growth and taking much of your hard disk space, deleting .LDF file is one of the solution. But how to attach .MDF file without .LDF file? In my experience I use to methods:

Method 1:

Attach the .MDF file like you usually attach the database file but you have to remove the .LDF file (do not specify a path to the non existant .LDF file).

Your database will get attached and a NEW log file (.ldf) will be created.

Method 2:
Use the following command and run it:
Sp_Attach_Db: Sp_Attach_Single_File_Db [ @Dbname = ] 'Dbname' , [ @Physname = ] 'Physical_Name'

Example:
EXEC sp_attach_single_file_db @dbname = 'Test',
@physname = 'D:\Microsoft SQL Server\MSSQL\Data\Test.mdf'

If Both Method doesn’t work you can try the steps below:

Problem when attaching .MDF file without LDF File
Attach database may not work in below situations:

1. If the database is not detached using sp_detach_db or using enterprise manager.

2. Sp_attach_single_file_db will fail incase if you have multiple LDF files associated.

How to Fix The Problem:

1. Create a new database with same MDF and LDF name

2. Stop SQL Server and delete the new MDF file and copy the old MDF file

3. Start SQL Server and see the status. If the database is suspect do the below steps:

- Setting the database status to emergency mode tells SQL Server to skip

- Automatic recovery and lets you access the data. To get your data, use this script:

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

- Create a new empty database and you might be able to use bulk copy program(BCP), simple SELECT commands, or use DTS to extract your data /objects from problematic database to new database.

Related Topics:
How to connect Delphi to database SQL Server
Select Distinct, Not In and Right Outer Join in SQL Command
How To Import Data Excel To SQL Server 2005
Function Date and Time in Foxpro
How to attach .MDF file without .LDF file into Database SQL Server 2005