Attach database without log file.

[bnosbod]

Hello,

I am trying to Attach a db on SQL2000 using various methods. The db data file has a log file retained that is causing the Attach to fail with the error "device activation failed. The physical file name c:\program files\mssql\data\slxremote_ldf.mdf may be invalid." Is there a way to Attach a database without the log file?

I've used Query Analyzer as well as Enterprise manager as well as a third party utility all returning the same error.

Any help would be appreciated.

Bill D.

 

[tlbroadbent]

The file name looks invalid to me.

c:\program files\mssql\data\slxremote_ldf.mdf

Verify the file name. The correct name is likely to be slxremote_log.ldf. The default extension for log files is LDF.

You can attach only the MDF without the LDF if you use sp_attach_single_file_db. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[bnosbod]

Terry,

I did a typo on the file name and you are correct. I did try the sp_attach_single_file_db with the same result. Is there any other stored procedure that allows attaching without a log file?

Thanks,

Bill D.

 

[tlbroadbent]

Why do you require another stored procedure to do what sp_attach_single_file_db does? Please post your query and the exact error message. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[bnosbod]

The problem was solved by basically recreating a database with the same name as the affected database and then swapping the data files, setting the database in bypass recovery mode, and then recreating the log file. I believe the cause of the problem was that the original database was not detached properly from the host.

Thanks for your help Terry.