asked on

SQL database restore WITH REPLACE fails because it is trying to use original file locations

I am trying to restore backups of 4 production databases to a development server. The databases files on the production server are on D: but the development server files are all on C:

USE MASTER  
RESTORE DATABASE [Jakeda.TRU] FROM 
DISK = '\\TS-1.jakeda.int\Backups\BSP-PROD\SQL\Weekly\Jakeda.TRU_backup_2012_08_19_080104_9987616.bak' 
WITH REPLACE 

RESTORE DATABASE [Jakeda.Builtspace] FROM 
DISK = '\\TS-1.jakeda.int\Backups\BSP-PROD\SQL\Weekly\Jakeda.BuiltSpace_backup_2012_08_19_080104_9938786.bak' 
WITH REPLACE 

RESTORE DATABASE [Dev-2_SP_Metadata] FROM 
DISK = '\\TS-1.jakeda.int\Backups\BSP-PROD\SQL\Weekly\Managed Metadata Service_2bc367652397420792d407ddec7d48e0_backup_2012_08_19_080104_9596976.bak'
WITH REPLACE 

RESTORE DATABASE [DEV-2_SP_WSS_Content] FROM 
DISK = '\\TS-1.jakeda.int\Backups\BSP-PROD\SQL\Weekly\WSS_Content_backup_2012_08_19_080104_9352826.bak' 
WITH REPLACE

Open in new window

I ran the four RESTORE statements, specifing "WITH REPLACE" in every case. The first two completed successfully.

The other two failed with messages like this:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\Databases\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content.mdf" failed with the operating system error 21(The device is not ready.).
Msg 3156, Level 16, State 3, Line 1
File 'WSS_Content' cannot be restored to 'D:\Databases\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\Databases\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content_log.LDF" failed with the operating system error 21(The device is not ready.).
Msg 3156, Level 16, State 3, Line 1
File 'WSS_Content_log' cannot be restored to 'D:\Databases\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Open in new window

From everything I have read, I should not need to specify WITH MOVE when I am using WITH REPLACE. And it worked for the first two databases.

Can anybody explain why I am getting this error on the other two?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlo-Giuliani

ASKER

Everything I have read says that WITH MOVE is not needed when using WITH REPLACE. It should restore to the disk files of the existing database. For example: http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/cb9509cc-5724-462f-b865-e6c4a94caf12

Also, all four of my database restores are "scenario 3" according to angelII, and two of them were successful.

I also tried dropping and recreating the two databases which I failed to restore over, then tried the restore again. Same result.

Carlo-Giuliani

ASKER

Actually, I was wrong. The in the first two (successful) cases, the database name is the same. In the two failing cases, the database name is different. So looks like angelIII is correct.

I ended up doing the restore for the last two with "WITH MOVE" specified.