Database will not re-attach

[LeonAtRC]

I detached a database and then erased the log file. Now when I try to attach the database I get the following error:

Error 1838: Could not open new database 'RUSA'. CREATE DATABASE Is aborted. Device activation error. The physical file name 'f:\MSSQL\data\RUSA_Log.LDF' may be incorrect.

The database is large but there is plenty of room on the disk.

I've done this successfully on another server but on this server this error keeps coming up. Any suggestions on restoring the RUSA_Data.MDF file would be greatly appreciated!
--Leon

 

[JayKusch]

Give this a try ... rename your ldf file so that SQL Server will not detect it during the attach process and for safety in case we need it later.

Once the ldf is renamed, run the following command in Query Analyzer ...

exec master..sp_attach_single_file_db @dbname = 'Rusa', @physname='f:\mssql\data\Rusa_data.mdf'

This should attach the DB and create a new log file, ldf. If all looks good, delete the old, renamed, ldf.

Thanks

J. Kusch

 

[LeonAtRC]

Thanks for the response Jay.
I'm beginning to think I had better start learning what all those system stored procedures do and then take advantage of them.
I tried what you said but I was using Enterprise Manager. Finally I deleted the database altogether. Recreated it and then restored from a backup.

--Leon

 

[JayKusch]

You are aware that you can evoke Query Analyzer, (QA), within Enterprise Manager,(EM), by going to the "Tool" menu option at the top of EM.

Thanks

J. Kusch

 

[LeonAtRC]

Thanks for the tip Jay. No, I wasn't aware of that but I'll keep it in mind.
I usually go through terminal services to get right on the server instead running EM from my home machine. The response seems to be much faster.

--Leon

 

[SQLBill]

For future reference....

When you don't have a log file (.ldf) (which you said you erased), you need to reattach the .mdf file using the stored procedure that Jay provided.

-SQLBill

 

[LonnieJohnson]

We tried this and still get prompted for a log file? Any other suggetions?

p.s. We inadvertantly deleted the log file also.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[SQLBaby]

check the directory, where the fist log file was exist, Like me i have different directories for Log and for Data like

c:\SQL\DATa
c:\SQL\LOGS

if you have same situation then after removing the log file database create new log file where the data file exist, just change the path of log file and attach the database, Later you can move your Log file in old postion.

Knowledge is a key of success.

 

[LonnieJohnson]

Please disregard my post here. I was (still am...) in panic mode and found this thread. My issues lie in more detail in thread962-709193

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!