How to attach ".mdf" to SQL Server Or recover ".ldf" file when is deleted/corrupted

The following article can be also used to "brute" shrink of your database but please be aware and always make a copy of your database before processing. To brute shrink your database, run SQL Server's Enterprise Manager and Detach the database you want to shrink. You can backup and/or pack the database's .mdf and .ldf files. Then you can remove the database's .ldf file. Now, in Enterprise Manager you can try Attach the database by choosing the database's .mdf file and follow these instructions:

1. If the DB shows as suspect in Enterprise Manager and you can see it in sysdatabases goto step 5

2. If the DB does not show in the Enterprise Manager but you have the .mdf file run following: (supposing your database's .mdf file is located on e:\file_name.mdf)

Exec sp_attach_single_file_db @dbname=’databaseName’, @physname=’e:\file_name.mdf’

3. If this does not create the .LDF (and it probably won’t) file for you, rename the .mdf file and create a brand-new database the same way the original one was created (the same name, same partitions a.s.o.). Run following to be sure it got created:

use master
go
Select dbid, status, name From dbo.sysdatabases (noLock)

4. Delete the .mdf file you just created and rename back the original one. Restart the SQL Server. The database should show up in Enterprise Manager as SUSPECT.

5. Now, to put the DB into Emergency Bypass Status (32768) run following:

sp_configure ‘allow’, 1

Go
reconfigure with override
update dbo.sysdatabases set status = 32768 where dbid = 9 -- check this dbid of your DB (see step 3)

6. Stop SQL Server – rename log file (you’ll delete it later), start SQL Server

7. To build the log run following:

dbcc rebuild_log(‘database_name’, ‘e:\file_name.ldf’)

8. Put DB back from Emergency Bypass Status (32768) to status 16

update dbo.sysdatabases set status=16 where dbid = 9 -- check this dbid of your DB (see step 3)

9. Clean up

sp_configure ‘allow’, 0
go

reconfigure with override

That's all. Right now, your database should be restored and shrinked to maximum.