SQL Server Performance Forum – Threads Archive
Restore WITH MOVE
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
I know this sounds a stupid question – but does C:MSSQLSRVR2_Data exist? Or is there a file there with the same name already?
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
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
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.
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
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.
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
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
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 />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Thank you Derrick for the useful link. CanadaDBA
I would verify that the new location exists and is writable:
C:MSSQLSRVR2_Data
Your command looks fine
regards
Irina
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.
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 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. "
]]>