SQL Server Performance

Backup from one machine, can't restore to another

Discussion in 'ALL SQL SERVER QUESTIONS' started by Jonathan Trahair, Jul 11, 2013.

  1. Jonathan Trahair New Member

    Hi. I have 2 laptops, both with SQL v 10.50, one on xp 32bit, one on W8 64bit. I have backed up one database from the XP machine and tried to restore to the W8 machine.

    RESTORE DATABASE PeripheryTMk_database FROM DISK = 'C:\All Users\PeripheryTMk.bak' WITH REPLACE

    Error message below:
    The database exists on both machines, the bak file exists at the stated location.

    What do I need to do? Thank you in advance.
  2. davidfarr Member

    The WITH REPLACE option typically works quite well, in that the RESTORE command should correctly identify the correct file path to restore to.
    In your case, however, it is clearly attempting to restore the physical data files to an invalid folder location.

    On the destination server that you are restoring to; run this query on the [PeripheryTMk_database] database;
    SELECT [name],[filename] from [sysfiles]
    Observe in what path the data and log files are located and compare that to the path in your error message.
    If they do not match (which is likely in this case), then you should do as the error message suggests and include the WITH MOVE option to specify the file paths, by using the paths that you get from the aforementioned query.

    Of course; you dont have to restore to the same path if you prefer not to. It might be easier to just create a new folder (such as C:\SQLdata) and include that simpler path as the destination folder for the DB files in the WITH MOVE syntax.
  3. Jonathan Trahair New Member

    Thank you davidfarr, WITH MOVE and WITH REPLACE (and a lot of trial and error) did it.

Share This Page