How to attach SQL 2000 database (MDF) without a LDF file.

See if this helps:
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

Thanks TAB8 for your comments.. but I got and error in the step where you said..

Update sysdatabases set status =  32768 where name = "BadDbName"
go

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BadDbName'.

when I ran with ' '  without " " I got this:

update sysdatabases set status = 32768 where name = 'BadDbName'
go

(0 row(s) affected)

If this ok?  Or I have to do other thing?

Regards

RTL

TAB8

These are the steps that I follow with your document.

1. Create a new database with the same name and same MDF and LDF files
The Databasename is dbSIM

So I created a database called dbSIM with Filename dbSIF_Data and the dbSIM_Log with Filename dbSIF_Log

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

I stopped the sql server and rename the dbSIM.MDF to dbSIMRTL.mdf and the dbSIM_Log.ldf to dbSIM_LogRTL.ldf and then I copied the file that I restored from the backup
to the path where are these files: E:\Microsoft SQL Server\MSSQL\Data
so dbSIM.MDF is over there now (the good one)

3. Start SQL Server

I started SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

I saw dbSIM (Suspect)

Sp_configure "allow updates", 1
go

I got this message:
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

Update sysdatabases set status =  32768 where name = "BadDbName"
go

I got this message:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BadDbName'.

so I change the syntaxis " "  for ' '

update sysdatabases set status = 32768 where name = 'BadDbName'
go

I got this message:
(0 row(s) affected)

Sp_configure "allow updates", 0
go

I got this message:
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

6. Restart sql server. now the database will be in emergency mode

I restart the sql server and the database dbSIM is still showing dbSIM (Suspect) in the Enterprise Manager

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')  -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

I type it in the query like this:

DBCC REBUILD_LOG(dbSIM,'E:\Microsoft SQL Server\MSSQL\dbSIM_Log.ldf')

I got this message:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8. Execute sp_resetstatus <dbname>

I type it:  sp_resetstatus dbSIM

I got this message:
Prior to updating sysdatabases entry for database 'dbSIM', mode = 0 and status = 16 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.


9. Restart SQL server and see the database is online.

I restarted the sql server and still looking dbSIM (Suspect)

what step do I fail?

Regards..

RTL

Hi Realhawk,

Another method ,

create a new file named  "urDatabaseName_log.ldf"   (for creating this , right click-> new text document-> open this document -> save as - within double Quotes give the name )
Now you can attach the urDatabase using either EM or QA
                                                                                                                       



Aneesh R!

Aneesh

I did what you said and I got this error..

Microsoft SQL-DMO (ODBC SQLState: HY000)
Error:823: I/O error 38 (Reached the end of the file.)
detected during read at offset 0000000000000000 in file 'E:\Microsoft SQL Server\MSSQL\Data\dbSIM_Log.ldf'

now what?

ok, Let us try this

create a database with the same name, detach the database, overwrite the mdf file with urs, then reattach it

Here is the Real Steps to make this work..

TAB8 here are the steps...  and PEOPLE, IF YOU HAVE PROBLEMS TO ATTACH MDF FILE WITHOUT LDF FILE HERE ARE THE STEPS THAT WORKS.. IT REALLY WORKS..

These are the steps that I follow to make this work.

1. Create a new database with the same name and same MDF and LDF files
The Databasename is dbSIM

So I created a database called dbSIM with Filename dbSIF_Data and the dbSIM_Log with Filename dbSIF_Log

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

I stopped the sql server and rename the dbSIM.MDF to dbSIMRTL.mdf and the dbSIM_Log.ldf to dbSIM_LogRTL.ldf and then I copied the file that I restored from the backup
to the path where are these files: E:\Microsoft SQL Server\MSSQL\Data
so dbSIM.MDF is over there now (the good one)

3. Start SQL Server

I started SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

I saw dbSIM (Suspect)

Sp_configure "allow updates", 1
go

I got this message:
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

Update sysdatabases set status =  32768 where name = 'dbSIM'
go

I got this message:
(1 row(s) affected)

Sp_configure "allow updates", 0
go

I got this message:
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

6. Restart sql server. now the database will be in emergency mode

I restart the sql server and I select Query Analyzer to connect to the Master Database

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')  -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

I type it in the query like this:

DBCC REBUILD_LOG(dbSIM,'E:\Microsoft SQL Server\MSSQL\dbSIM_Log.ldf')

I got this message:
Warning: The log for database 'dbSIM' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8. Execute sp_resetstatus <dbname>

I type it:  sp_resetstatus dbSIM

I got this message:
Prior to updating sysdatabases entry for database 'dbSIM', mode = 0 and status = 2048 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

9. Restart SQL server and see the database is online.

I restarted the sql server and dbSIM is now online.. in the first time looks like dbSIM (DBO use Only) , so click with the right bottom and select properties and the select the Options tab and deselect "Restrict Access" and click OK.

and the database will look online... is really working!!!!!!  :)

Finally here is a real document to make this nightmare works...

Regards..

Roberto Tamez

Realhawk - did you even look at the link I provided... ?

Yes leew,

but it was more easy to understand the steps that TAB8 said...

that's why I prefered to document here with an example...

your link I already check, but some steps doesn't apply with me...

thanks anyway for your help..

Regards

RTL