?
Solved

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

Posted on 2012-08-24
3
Medium Priority
?
1,655 Views
Last Modified: 2012-08-24
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?
0
Comment
Question by:Carlo-Giuliani
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38330515
you need the WITH MOVE if you restore the db on the same server and to another database than the one that was backed up.

so, consider this scenario (pseudocode):
BACKUP database1 ON server1 TO DISK 'db1.bak'

scenario 1:
RESTORE database1 ON server1  => WITH MOVE is not needed
scenario 2:
RESTORE database1 ON server2  => WITH MOVE is not needed (*)
scenario 3:
RESTORE database2 ON server2  => WITH MOVE is needed unless database1 was dropped/moved meanwhile

in short: if the files to be restored are used by some database already (except the one you want to restore) then you need to specify WITH MOVE to make sure you don't try to create/replace files that are already locked by some process
0
 
LVL 12

Author Comment

by:Carlo-Giuliani
ID: 38331267
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.
0
 
LVL 12

Author Closing Comment

by:Carlo-Giuliani
ID: 38331315
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question