Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Exclamation Unanswered: Urgent - Unable to restore/attach a MDF file

    Hi,

    I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).

    I have tried restoring and attaching options but nothing works

    I also get the error saying

    "Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".

    Please this is really urgent. Will be VERY grateful for your urgent reply.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    how did u bakup the files? had u copied the mdf files?
    or had u taken a backup and u have .bak files?

  3. #3
    Join Date
    Jun 2004
    Posts
    3
    Quote Originally Posted by Joozh
    I have tried restoring and attaching options but nothing works
    Please post here your RESTORE command ...

  4. #4
    Join Date
    Oct 2003
    Posts
    163
    No I did not copy/paste the MDF file and had taken the backup via the backup option. When I ran into this problem I have changedthe extension of the file to MDF but still was not able to restore.

    Should I change the extension of the backup file back to .bak or something and then try

    Will appreciate if you can guide me.

    Thanks.

  5. #5
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    create DB with the same name,
    in EM restore db > option 'from device' > select device > add > your directory
    where you have .bak file not MDF

    you need the bak file you have created as beckup

  6. #6
    Join Date
    Oct 2003
    Posts
    163

    Arrow

    Hi everyone,

    Like i said earlier I had backed up the file using the BACKup and did not do a copy paste...unfortunately I do not have a log file

    Also, I have also tried attaching the database via EM but that too did not work....


    This is what I have tried:

    1) Changed the extension of the backfile (which i had backed up using the backup option) to .bak. Should I change the extension to .MDF or just take out the extension? ( i know this may sounds silly)

    2) Tried restoring from EM but nothing worked and that's why I posted this query.

    3) Got some clue and started QA and typed the following (note the name of my database was 'My DB' and the backup file is located in c:\tmp)

    RESTORE DATABASE "My DB.bak" From "C:\tmp"

    4) QA does not give any error when I check the syntax


    5) When I execute the above statemen, I get the following error:

    Server: Msg 3206, Level 16, State 1, Line 1
    No entry in sysdevices for backup device 'C:\tmp'. Update sysdevices and rerun statement.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.


    The only thing that I can think of is that the sa password before my system crash was different than the sa password that I now have (after doing a fresh installation of everything on my PC).

    I hope this clearly explains everything from my side.

    Eagerly waiting for the next steps to solve the problem

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    this is what I get from EM when using:
    EM -> restore db > option 'from device' > select device > add > your directory -> bak-file.

    I copied a bak-file from one sqlserver to another which has no previous history of the db I want to restore.

    It's an error message titled: 'Microsoft SQL-DMO (ODBC SQLState: 42000)'
    <> and [] are my comments
    "Device activation error. The physical file name <original db filename> may be incorrect. [in fact there's none] File <filename> cannot be restored to <original db filename>. Use WITH MOVE to identity a valid lcoation for the file."
    Same goes for the log.

    Joozh: did you see something like the above when you tried from the EM as above?

  8. #8
    Join Date
    Oct 2003
    Posts
    163
    Hi,

    please let me try this and get back to you. Many thansk for the help and post

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Also check what service pack you have now with the fresh install. I might have missed it, but what service pack level were you on before deciding to do a reinstall?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    syntax is a bit off on your restore statement

    restore MYDB from disk = 'c:\tmp\MYDB.bak'
    or if you create a permenent backup device called MYDBFULL (using sp_adddumpdevice)
    then it would be restore MYDB from MYDBFULL

    RESTORE [BOL]


    Quote Originally Posted by

    2) Tried restoring from EM but nothing worked and that's why I posted this query.

    3) Got some clue and started QA and typed the following (note the name of my database was 'My DB' and the backup file is located in c:\tmp)

    [COLOR=Blue
    RESTORE DATABASE "My DB.bak" From "C:\tmp"[/COLOR]

    4) QA does not give any error when I check the syntax


    5) When I execute the above statemen, I get the following error:

    Server: Msg 3206, Level 16, State 1, Line 1
    No entry in sysdevices for backup device 'C:\tmp'. Update sysdevices and rerun statement.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  11. #11
    Join Date
    Oct 2003
    Posts
    163
    Everyoneand especially Ruprect,

    Many many thanks for your contributions and help. Really appreciate it

    The problem is history and was solved.

    Regards.

  12. #12
    Join Date
    Jul 2004
    Posts
    2
    So what fixed the problem?

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    he rubbed a bit of bacon on it and smothered it in gravy
    is there any other solution?

    mmm mmm that's good eats...!

  14. #14
    Join Date
    Oct 2003
    Posts
    163
    Quote Originally Posted by jerryol
    So what fixed the problem?
    Hi,

    My apologies for the late reply.... for some reason I missed the mail alert.

    No it was none of the tempting stuff that Ruprect has mentioned. The solution was in Ruprect's help.... please see the complete post for details. Let me know and I'll be glad to help if required.

    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •