SQL Server Performance Forum – Threads Archive

Not able to attach after detaching DB

Hi I have detached database and deleted log file,but receiving error while reataching DB.Following are two things i had tried… )sp_attach_db ‘ProductDB’,’F

rogram FilesAuthoria 4.2ProductDBProductDB.mdf’ Error statement generated is: Server: Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘ProductDB’. CREATE DATABASE is aborted. Device activation error. The physical file name ‘F

rogram FilesAuthoria 4.2ProductDBProductDB_log.LDF’ may be incorrect. 2)sp_attach_single_file_db @dbname =’ProductDB’,@physname = ‘F

rogram FilesAuthoria 4.2ProductDBProductDB.mdf' Server: Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘ProductDB’. CREATE DATABASE is aborted. Device activation error. The physical file name ‘F

rogram FilesAuthoria 4.2ProductDBProductDB_Log.LDF’ may be incorrect. Its using Simple recovery model and had only one .MDF and .LDF file Can anyone tell me whts the issue here. Its urgent .more than 100 users are affected <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> rajiv

I am alos looking into this problem. But why did you do such a nasty this without a backup <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by gkrishn Hi I have detached database and deleted log file,but receiving error while reataching DB.Following are two things i had tried… )sp_attach_db ‘ProductDB’,’F

rogram FilesAuthoria 4.2ProductDBProductDB_log.LDF’ may be incorrect. 2)sp_attach_single_file_db @dbname =’ProductDB’,@physname = ‘F

‘ /> rajiv <hr height="1" noshade id="quote"></blockquote id="quote">

Hi gkrishn,
have u detached the DB using sp_detach_db.[?]

quote:Originally posted by gkrishn Hi
I have detached database and deleted log file,but receiving error while reataching DB.Following are two things i had tried…

Dinesh, Tht was done by my smart client. Only backup available is of 5th this month <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> .But I am not sure how he detached DB, I am waitin for his reply on this. rajiv

if he has done detattched by sp_detach_db, sp_attch_db should work <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by gkrishn Dinesh, Tht was done by my smart client. Only backup available is of 5th this month <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> .But I am not sure how he detached DB, I am waitin for his reply on this. rajiv <hr height="1" noshade id="quote"></blockquote id="quote">

If he did not detach with sp_detach_db, the attach isn’t working with the methods you said earlier, AND you have no backup for that long then you are kind of in trouble and deserve it. Who is the DBA that should have been backing this database up? Hopefully, this isn’t for production. Follow the directions in this article: http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html Basically, you’re going to create a new database. Shut SQL Server off, delete the log file, and replace the data file with the one you have. You are going to then bring the server back up, reset the suspect status, and pray. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi My client used sp_detach_db only, but this happend . He only used to tak backup.All fault are from his side only, but as a dba, i shud bring it up. Derri,
I saw the article you gave, But i am not sure wht to do after bringin it to emergency mode.can you please brief me that step by step. i am in a big trouble now . Thnx in advance rajiv

Is the database accessible now? If so, follow the article to conclusion. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

noo , as of now there is no such database.I only have one .MDF file with me. I think i can do up to step8 (makin it as a emergency mode), DOnno wht next .Can you please tell me wht can i do after that? rajiv

I think I need to use DTS after that.ok, when i run DTS i can see 2 choises
*copy table and views
*Copy objectes and data between SQL Server databases which one shuld i use to get my Database as before. After this i need to create all users in here?
Please advice

May be I’m missing something, but if you try with EM to attach database and find MDF, you can attach database without log.
After a new log can be created.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Martin, somtimes that wont work,for me its not working.I am getting above error. This error is a serious discussion in many forums. you may get better idea fromhttp://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html .

There is no guarantee that detaching a database, removing the log and attaching it will work. Never do it on a critical system. If you have problem with log size then shrink it using normal DBCC commands. Adding another article that might help you:
http://myitforum.techtarget.com/articles/18/view.asp?id=7381

]]>