Restore WITH MOVE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore WITH MOVE

Author

Topic

CanadaDBA

583 Posts

Posted - 2005-04-14 : 09:54:07

I want to restore a database.


RESTORE DATABASE Test
   FROM DISK = 'E:\Test_db_BASELINE.BAK'
   WITH MOVE 'Test_dat' TO 'C:\MSSQL\SRVR2_Data\Test_Data.MDF',
        MOVE 'Test_log' TO 'C:\MSSQL\SRVR2_Data\Test_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:\MSSQL\SRVR2_Data\Test_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:\MSSQL\SRVR2_Data\Test_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_dat	C:\MSSQL\Data\Test_Data.MDF
Test_log	C:\MSSQL\Data\Test_Log.LDF

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

Canada DBA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-14 : 10:09:28

A couple of items:
1. Did you verify that directory C:\MSSQL\SRVR2_Data exists, the SQL Server service account has full access to it, and there is enough space to hold the files?
2. I usually include the REPLACE option on the restore when I use MOVE.


RESTORE DATABASE Test
   FROM DISK = 'E:\Test_db_BASELINE.BAK'
   WITH MOVE 'Test_dat' TO 'C:\MSSQL\SRVR2_Data\Test_Data.MDF',
        MOVE 'Test_log' TO 'C:\MSSQL\SRVR2_Data\Test_Log.LDF', REPLACE

CODO ERGO SUM

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-04-14 : 10:16:46

how about using the RESTORE LOG command fo your log file

Sean Roussy

CanadaDBA

583 Posts

Posted - 2005-04-14 : 10:57:38

Thanks for your replies.

1. C:\MSSQL\SRVR2_Data exists and Test_Data.MDF and Test_Log.LDF are in there. And of course E:\Test_db_BASELINE.BAK exists as well.

2. There is plenty space available on C:.

3. I used different IDs including SA but the problem exists.

4. I used REPLACE but got the same error.

5. I tried the RESTORE DATABASE only for data part and exclude the log part but got the same error.

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

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-04-14 : 11:32:43

I droped the Test database which its files were Test_Data.MDF and Test_Log.LDF located at C:\MSSQL\SRVR2_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?

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-04-14 : 11:39:39

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

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-04-14 : 13:07:17

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:\MSSQL\SRVR2_Data). :)

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-14 : 15:54:12

The problem was this:

quote:

C:\MSSQL\SRVR2_Data exists and Test_Data.MDF and Test_Log.LDF are in there.

The MDF and LDF files were in the directory already, so that's what was giving you the error. Don't forget to use WITH REPLACE in your RESTORE commands.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources