SQL Server Performance

Urgent question - restore db from only .mdf file

Discussion in 'General DBA Questions' started by danan_xu, Apr 27, 2005.

  1. danan_xu New Member

    guys:

    It's really urgent. Can anyone give me advice on how to restore db form only .mdf file.
    ( .ldf file has been deleted!)

    I tried sp_attach_single_file_db, but there is error

    Server: Msg 1813, Level 16, State 1, Line 1
    Could not open new database . CREATE DATABASE is aborted.
    Log file 'C:DATABASEMSSQLdatadbname_Log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

    Many thanks!!!

    Wait online!!
  2. dineshasanka Moderator

    sp_attach_single_file_db @dbname = 'RA'
    , @physname = 'C:program FilesMicrosoft SQL ServerMSSQLDataRMT.mdf'
    I used as above it works fine
    can you paste yoor code here please

    quote:Originally posted by danan_xu

    guys:

    It's really urgent. Can anyone give me advice on how to restore db form only .mdf file.
    ( .ldf file has been deleted!)

    I tried sp_attach_single_file_db, but there is error

    Server: Msg 1813, Level 16, State 1, Line 1
    Could not open new database . CREATE DATABASE is aborted.
    Log file 'C:DATABASEMSSQLdatadbname_Log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

    Many thanks!!!

    Wait online!!
  3. danan_xu New Member

    the problem is I didn't use the sp_detach_db to detach database!!!

    I lost log file then can't start db any more.

  4. dineshasanka Moderator

    Yes i delete the log file and re did it again. Then I got a warning and it creates the db
    warning is

    Device activation error. The physical file name 'C:program FilesMicrosoft SQL ServerMSSQLdatasqlbpa_log.LDF' may be incorrect.
    New log file 'C:program FilesMicrosoft SQL ServerMSSQLDataRA1_log.LDF' was created.

  5. danan_xu New Member

    thanks for your help, dineshasanka


    can you try delete the log before starting sqlserver?

    thanks again.

  6. dineshasanka Moderator

    sure. I will contact you soon
  7. dineshasanka Moderator

    Device activation error. The physical file name 'C:program FilesMicrosoft SQL ServerMSSQLDataGPLDT1.ldf' may be incorrect.
    New log file 'C:program FilesMicrosoft SQL ServerMSSQLDataRA_log.LDF' was created.

    Same thing

    as per BOL
    sp_attach_single_file_db
    Attaches a database having only one data file to the current server.

    so it should work. I don't think you have a permission issue. Do you?
  8. cemuney New Member


    Did you find a solution??????


    I have the same problem.
    my MDF file is not a detached MDF.
    I have only (.MDF) file and no log file in the directory.
    But it tries to find .LDF file.

    sp_attach_single_file_db 'UZMAN' , 'c:cddataUZMAN2005_DATA.mdf'

    Error:
    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'UZMAN'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'C:cddataUZMAN2005_Log.LDF' may be incorrect


    cemuney
  9. ranjitjain New Member

    hi cemuney,
    the solution dinesh has provided has to work even i have tested on my machine
    and its working perfectly after connecting it from sp_attach_single_file_db.[:I]
  10. satya Moderator

    The SP sp_attach_single_file_db will recreate the log file .LDF freshly and it is the best way to accomplish when .LDF file is missing.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. danan_xu New Member

    haven't found any solution,

    so have to create the data (import a 220million table) but still lucky
    which took me 1 more day.

    quote:Originally posted by cemuney


    Did you find a solution??????


    I have the same problem.
    my MDF file is not a detached MDF.
    I have only (.MDF) file and no log file in the directory.
    But it tries to find .LDF file.

    sp_attach_single_file_db 'UZMAN' , 'c:cddataUZMAN2005_DATA.mdf'

    Error:
    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'UZMAN'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'C:cddataUZMAN2005_Log.LDF' may be incorrect


    cemuney
  12. dineshasanka Moderator

  13. cemuney New Member


    Hi everyone.

    dineshasanka:I had only one mdF and LDF file.


    I couldnt find any solution without LOG file. I found my old LDF file and attach database.

    sp_attach_single_file_db was not enought for my problem with only MDF.
    Because database recovery model was not (SIMPLE) so SQL needs LOG file to ROLLBACK the transactions.

    I copy the LOG file in the same directory and use sp_attach_single_file_db
    and it attached the db. And said 107 transactions rolled back successfully.
    You can not use sp_attach_single_file_db only with MDF file If your database recovery model was (FULL) and you shut down the Computer suddenly and you lost the log file.

    If anyone want to see the problem i can put the MDF file anywhere on the internet or mail it(3.7MB)

    Thanks for everyone

  14. dineshasanka Moderator

    can you mail it to me to dineshasanka@gmail.com
  15. cemuney New Member


    ok dineshanka

    i will send it to you tommorrow. i dont have file now.

    thanks.
  16. dineshasanka Moderator

    Ok no problem,
    I am waiting for it. there is another post with same question.
    I was trying to create this in my pc which was failed.
    SO I am looking forward to your file
    Thankx


    quote:Originally posted by cemuney




    i will send it to you tommorrow. i dont have file now.



  17. cemuney New Member


    ok dineshanka
    i create the MDF file i am sending you the file MDF.

    i am sending u MDF .

    -----------------------------
    sp_attach_single_file_db 'cemuney',
    'C:program FilesMicrosoft SQL ServerMSSQLDATAcemuney_data.mdf'

    it gives
    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'cemuney'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'C:program FilesMicrosoft SQL ServerMSSQLdatacemuney_Log.LDF' may be incorrect.
    --------------------------------


  18. dineshasanka Moderator

    Yes you are correct
    u can use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.
    But the script that i gave you should work . I will try and let you know tommorow.
    Thankx

    quote:Originally posted by cemuney


    ok dineshanka
    i create the MDF file i am sending you the file MDF.

    i am sending u MDF .

    -----------------------------
    sp_attach_single_file_db 'cemuney',
    'C:program FilesMicrosoft SQL ServerMSSQLDATAcemuney_data.mdf'

    it gives
    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'cemuney'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'C:program FilesMicrosoft SQL ServerMSSQLdatacemuney_Log.LDF' may be incorrect.
    --------------------------------



  19. danan_xu New Member

  20. bj007 New Member

    dineshasanka,

    What is the script/solution you provided?

    I received a MDF from a client, and trying to restore it to my server.

    I did NOT have this db in my server before, nor did I detach it from my server.
    No idea whether the client's db had one or more log files, or their db is in simple or full recovery mode.

    I tried both methods of sp_attch, and also thru EM, it gives me the same error
    Device activation error. The physical file name 'D:Microsoft SQL ServerMSSQLdataxxx_log.ldf' may be incorrect. I even created this directory.

    I also created a new db with the same naming conventions in my server, and then I detached it. replaced the mdf file with the mdf file I got from my client. I tried attaching, same error. I tried this in both single and full recovery mode.

    Any other help greatly appreciated.
    bj
  21. sonnysingh Member

    Hi All

    guys I am facing same problem. I have only MDF file cos of power failure and when I started my PC I couldn't able to start Database even can't access to it. so what I did that format my PC but still have my MDF file in D drive. After reinstall every thing I try to attach this MDF file using 'sp_attach_single_file_db'.
    Unfortunately, I got message

    quote:Device activation error. The physical file name 'C:program FilesMicrosoft SQL ServerMSSQLdataMYDB_log.ldf' may be incorrect.

    How can get my database restore?? please help.

    Thanks in advance
  22. satya Moderator

    You need to have a good backup in order to restore the file.
    Also make sure the SQL services account has required privilege on the specified path, if mydb_log.ldf exists then drop it and try again.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  23. cemuney New Member

    Hi sonny
    1.Does your database marked as SUSPECT on E.Manager?
    2.What was your database recovery Model?
  24. sonnysingh Member

    Thanks satya...
    mydb_log.ldf is not exist like said format the 'C' drive. I had Data file on 'D' drive and Log file on 'C' drive. Therefore, only mdf file I have. I could get the latest backup cos of power failure.. I know it is not done but it is developement server.

    Thanks cemuney..no my database is not marked as suspect. database was in FULL recovery mode.

    suggest some solution it is urgent.....
  25. cemuney New Member

    hi sonnysingh

    In power failure.
    it is not possible to use 'sp_attach_single_file_db' if your database recovery model.
    Because database needs LOG file to recover database in FULL RECOVERY MODEL. Sometimes in suddden power failure it becomes a problem.

    That was also my big problem about my customers.
    These are the steps i recover my databases. i rescue the records with this method.
    I hope it works on your problem..

    1.Create an empty <<MYDB>> database in Enterprise Manager(EM). Empty MYDB_Data.mdf and MYDB_Data.ldf will be created.
    2.Stop SQL Server. That overwrite your original MYDB_Data.mdf file to this new empty file.
    3.Start SQL Server. Open You will see your database marked as <<Suspect>> in EM.
    4.Open Query Analyszer. In master database execute.

    sp_configure 'allow updates', 1
    reconfigure with override
    GO

    UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='MYDB'
    GO

    5.Now your database is in <<Read OnlyOfflineEmergency Mode>>
    6.Because of being READONLY. You have to export your records with DTS Import/Export wizard,
    Also you can execute <<SELECT>> queries in your database.

    Please inform us if it works.
    Cem Uney




  26. sonnysingh Member

    Thanks Cem Uney.. it is work and able to get the database on the stage as you have mentioned. I have script the database (structural as well as data) using ApexSQL Script and rebuild the database again.

    Tell me this is the only option we have to update the database

    quote:UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='MYDB'

    can not we set status to that put the database in normal state???

    Regards and thanks again Cem Uney
  27. zlatan24 New Member

    **REmoved as it stands advertisement
  28. rohit2900 Member

  29. Luis Martin Moderator

    Hi Rohit,
    Kindly check the post date before answer any question.
    Thank you,
  30. rohit2900 Member

    Hehehehe.....
    THx luis for reminding me......
  31. moh_hassan20 New Member

    I faced this problem yesterday in sql server 2000 sp4 , and sp_attach_single_file_db was not helpfull at all
    I had to restore database from backup.
    - that is the case:
    -The database is full recovery model
    -a long running transaction was stoped by power failure
    -when server start , database become in suspect mode
    -i deattached the database
    -the log file is not accessible
    -i could not reattch it ,and get same error
    i think that the log file is needed in such cases with the mdf , and no way except restore from backup

Share This Page