SQL Server Performance

Restore WITH MOVE

Discussion in 'General DBA Questions' started by CanadaDBA, Apr 14, 2005.

  1. CanadaDBA New Member

    I want to restore a database.


    RESTORE DATABASE Test
    FROM DISK = 'E:Test_db_BASELINE.BAK'
    WITH MOVE 'Test_dat' TO 'C:MSSQLSRVR2_DataTest_Data.MDF',
    MOVE 'Test_log' TO 'C:MSSQLSRVR2_DataTest_Log.LDF'

    The above code returnes the following error:

    Server: Msg 3156, Level 16, State 1, Line 1
    File 'Test_dat' cannot be restored to 'C:MSSQLSRVR2_DataTest_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
    Server: Msg 3156, Level 16, State 1, Line 1
    File 'Test_log' cannot be restored to 'C:MSSQLSRVR2_DataTest_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    The RESTORE FILELISTONLY FROM DISK... shows:


    Test_datC:MSSQLDataTest_Data.MDF
    Test_logC:MSSQLDataTest_Log.LDF

    I have used "WITH MOVE", as you see in the code. Where is the problem?
    Thanks,

    CanadaDBA
  2. simondm New Member

    I know this sounds a stupid question - but does C:MSSQLSRVR2_Data exist? Or is there a file there with the same name already?

  3. CanadaDBA New Member

    Yes, C:MSSQLSRVR2_Data exists and Test_Data.MDF and Test_Log.LDF are in there. And of course E:Test_db_BASELINE.BAK exists as well.

    quote:Originally posted by simondm

    I know this sounds a stupid question - but does C:MSSQLSRVR2_Data exist? Or is there a file there with the same name already?



    CanadaDBA
  4. CanadaDBA New Member

    I ran the same script and were able to restore the file into C:MSSQLData on the server and it worked dramatically! Why it doesn't work with the other path?

    CanadaDBA
  5. Luis Martin Moderator

    Since one hour I'm thinking and searching about it. No clue by now.


    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.



  6. CanadaDBA New Member

    I droped the Test database which its files were Test_Data.MDF and Test_Log.LDF located at C:MSSQLSRVR2_Data. Then ran the script and it worked successfully!
    Knowing that the database was not in use at the first time, what was causing this problem?

    CanadaDBA
  7. Luis Martin Moderator

    May be some scheduled job?


    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.



  8. CanadaDBA New Member

    Scheduled job? There is no scheduled job. I double checked and noticed that the SQL Agent is stopped. So, it couldn't be.

    Interesting is that I ran the script for the second time and it worked again successfully.

    CanadaDBA
  9. CanadaDBA New Member

    Hey Luis,<br />I think I found the problem. I have two instances of SQL server on my machine. The QA was connected to one of them and I was tring to restore the Test database on the other instance (C:MSSQLSRVR2_Data). <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />CanadaDBA
  10. derrickleggett New Member

    That would be a problem. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Just an fyi, process explorer is a great troubleshooting tool for things like this. It can tell you ever process holding a file, process, etc lock. It's free also, which is nice.<br /><br /<a target="_blank" href=http://www.sysinternals.com/ntw2k/freeware/procexp.shtml>http://www.sysinternals.com/ntw2k/freeware/procexp.shtml</a><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  11. CanadaDBA New Member

    Thank you Derrick for the useful link.

    CanadaDBA
  12. Irina Richardson New Member

    I would verify that the new location exists and is writable:
    C:MSSQLSRVR2_Data
    Your command looks fine
    regards
    Irina
  13. satya Moderator

    Did you see the date of the question and this is not a recent topic to discuss, though appreciate yoru registration here in sharing your experinence.
  14. gpav New Member

    may be the instance where you are trying to restore the db might already have the testdb so u might need to drop it or do a cyclinc renaming of existing test to test 1 and then restore with different filenames i.e testdb1.
  15. Luis Martin Moderator

    [quote user="gpav"]may be the instance where you are trying to restore the db might already have the testdb so u might need to drop it or do a cyclinc renaming of existing test to test 1 and then restore with different filenames i.e testdb1.[/quote]
    In Satya words:
    "Did you see the date of the question and this is not a recent topic to discuss, though appreciate yoru registration here in sharing your experinence. "

Share This Page