asked on

Attach database without transaction log.

Hi!

I am trying to attach a single db file. Incidently I have only the DB file and not the Log file. I use this file to port data from one place to another. Generally, whenever I tried to attach such DB files, earlier, it would automatically create a log file, but off late it won't work sometimes.

I am get the following error...
Error 1813: Could not open new database 'dbasename' CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\Data\dbasename_log.ldf' may be incorrect.

One thing that I notice here is that the drive name and path is not same as that of the physical location where the dbasename.mdf file has been stored. This is still attached to the original location and is working fine on that server.

Can you tell me how to work around this problem. It would save a lot of trouble not having to restructure the data file and reprogram the export and import routines.

Gajendra

Lee W, MVP

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

gajendra

ASKER

Thanks for the quick reply.

It works, in such that I am able to salvage the data onto a separate / new database.

But, I don't think that I can pass this on as a regular solution to my client. I was wondering if we could override SQLServer's request for a specific transaction log at the time of attachment and make it accept the data file as a complete database and create a new transaction log.

sp_attach_single_file_db stored procedure is documented to this and I have been using this feature successfully until recently. Today morning it started asking for the transaction log at the location specified in the original computer's transaction log and I am unable to understand why this happened.

It would even help to understand why this happened so that in future care will be taken that the "mistake" or rather the mis-step is not repeated.

You certainly deserve some points for enlightening me on this aspect of data recovery which I was unaware of. But let me wait for now to see if my original problem can be solved.

Thank You

Gajendra

ASKER CERTIFIED SOLUTION

fuchsiasoft

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gajendra

ASKER

Thanks fuchsiasoft

This is the exact solution I was looking for.

Thanks leew

Thanks both of you for your input and responses.

Gajendra

fuchsiasoft

Oh ! Thank you very much !! All I did was pointed to you to a direction which I perceived to be correct.
fuchsiasoft