ldf file deleted

[EveS]

Hi,
I have accidentaly deleted the ldf file of a SQL 2000 database after
detaching it knowing that you can reattach the mdf file without the ldf.
But what I didn't know was that the database had two ldf files. In this
case SQL does not let me attach the database using a single file. Is
there any workaround? I need the database urgently since it has
crical information.
Thank you for all the help you can provide me.
Please let me know if further clarification is needed.

[skhanal]

You can't. Try to use utilities to recover lost files (something like norton utility)

[MAK]

yes. there is a work around. (if u r lucky)

I simulated your whole situation by creating a database "Mydatabase". In your case it is from step 4. Read it completely.


--1. Create Database

USE master
GO
CREATE DATABASE mydatabase
ON PRIMARY ( NAME = mydatabase_data,FILENAME = 'c:\mydatabase.mdf',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)
LOG ON ( NAME = mydatabase_Log1,FILENAME = 'c:\mydatabase1.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1),
( NAME = mydatabase_Log2,FILENAME = 'c:\mydatabase2.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)
GO
--2. Add data
use mydatabase
go
create table x123(id int)
insert into x123 select 111223
insert into x123 select 111223
insert into x123 select 111223
insert into x123 select 111223
insert into x123 select 111223


--3. Detach Database
use master
go

sp_detach_db mydatabase

--4. Accidental Deletetion.
Delete mydatabase1.ldf and delete mydatabase2.ldf

--5. Try to attach mydatabase.mdf
sp_attach_db 'mydatabase','c:\mydatabase.mdf'

Error message:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'mydatabase'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'c:\mydatabase1.ldf' may be incorrect.
Device activation error. The physical file name 'c:\mydatabase2.ldf' may be incorrect.

--6. Rename c:\mydatabase.mdf to MydatabaseXXXXXX.mdf

--7. Create database mydatabase

USE master
GO
CREATE DATABASE mydatabase
ON PRIMARY ( NAME = mydatabase_data,FILENAME = 'c:\mydatabase.mdf',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)
LOG ON ( NAME = mydatabase_Log1,FILENAME = 'c:\mydatabase1.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1),
( NAME = mydatabase_Log2,FILENAME = 'c:\mydatabase2.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)
GO

--8 Stop SQL Server

--9 Delete mydatabase.mdf

--10 rename MydatabaseXXXXXX.mdf to mydatabase.mdf

--11 Start SQL Server service

--12 run the following

use Master
go
sp_configure "allow", 1
go
reconfigure with override
go

--13
update sysdatabases set status = 32768 where name = 'Mydatabase'
go
checkpoint
go
shutdown with nowait
go


--14. delete mydatabase1.ldf and mydatabase2.ldf

--15. run this query

dbcc traceon(3604)

--16. rebuild Log

dbcc rebuild_log('Mydatabase','c:\Mydatabase1.ldf')

--17.
update sysdatabases set status = 0 where name = 'mydatabase'

--18. restart sql server
--19. run the following query
use mydatabase
go
dbcc checkdb
go
dbcc checkalloc
go
backup database mydatabase to disk = 'c:\mydatabase.bak'
go
select * from x123
go

[EveS]

Thank you very much MAK. It worked! Actually I cannot thank you enough. You saved my life!!!

[MAK]

I am glad

[KingSexy182]

Your a GENIUS MAK!!!!!!!!!!!!!!!

[MAK]

Not really. Somebody had this idea posted. I enhanced ,tested and presented it.

[KingSexy182]

Well whoever invented the wheel did not achieved it in 1 go

[ACE74]

This solution doesn't work for 2005. For 2005:

Do up to step 11 as above. Then run the following commands:

1. ALTER DATABASE YourDatabseName
SET Emergency

2.ALTER DATABASE YourDatabseName
SET single_user

3. DBCC checkdb (YourDatabseName, repair_allow_data_loss)

* if this works then run the following 2 steps

4. ALTER DATABASE YourDatabseName
SET multi_user

5. ALTER DATABASE YourDatabseName
SET online

[rmiao]

But this is sql7/sql2k forum.