Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45

    Unanswered: DB restore error

    Hi all, I'm getting this error when trying to restore a database:

    "Device activation error. The physical file name 'E:\program files\MS SQL server\MSSQL\data\DBname.mdf' may be incorrect. File 'DBname_dat' cannot be restored to 'E:\program files\MS SQL server\MSSQL\data\DBname.mdf'. Use WITH MOVE to identify a valid location for the file. Device activation error. The physical file name 'E:\program files\MS SQL server\MSSQL\data\DBname.ldf' may be incorrect. File 'DBname_log' cannot be restored to 'E:\program files\MS SQL server\MSSQL\data\DBname.ldf'. Use WITH MOVE to identify a valid location for the file. RESTORE DATABASE is terminating abnormally."

    What I'm doing is, on another personal sql server a backup was created of this DB, then sent to me. Now I created a new DB named after the one that was backed up. Then I go to restore the DB into the one I created. But i get this error. How can I trouble-shoot this?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    From the BOL:
    "Re-creating Database Files
    Restoring a database automatically creates the files needed by the database backup to restore the backup into. The database files (hence the database) do not need to be created before restoring a backup. By default, the files created by SQL Server during the restoration process use the same name and path as the backup files from the original database on the source computer. Therefore, it is useful to know in advance the files that are created automatically by the restore operation, because:

    The file names may already exist on the computer, causing an error.


    The directory structure or drive mapping may not exist on the computer.
    For example, the backup contains a file that it needs to restore to drive E, but the destination computer does not have a drive E.

    If the database files are allowed to be replaced, any existing database and files with the same names as those in the backup are overwritten, unless those files belong to a different database."
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45
    Ahhh, thank you. I found where I could change the path and successfully restore the DB.

  4. #4
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    Your problem probably is that you don't have the path specified on your computer ('E:\program files\MS SQL server\MSSQL\data\') and sql server tries to restore db to its original location recorder in backup file. First, run this command
    RESTORE FILELISTONLY FROM DISK='path to your backup_file' It shows you logical and physical file names present in your backup. Now you have two options: Either create on your server database of the same name and same logical file names, but different physical filename (on location that is valid on your computer) and perform restore or preferrably you can use RESTORE DATABASE with MOVE clause. Look up RESTORE DATABASE topic in Books Online for exact syntax but from your error message I assume something like

    RESTORE DATABASE your_db_name --doesn't have to be the same as original
    from DISK='path to your backup_file'
    with move 'DBname_dat' to 'location_valid_on_your_computer\DBname.mdf',
    move 'DBname_log' to 'location_valid_on_your_computer\DBname.ldf'

    mojza

Posting Permissions

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