CODEWITHSUNDEEP
sql-serverbackuprestore
Aidan Ryan
Aidan Ryan

Reputation: 11609

Restore Database "with replace" - is "with move" necessary?

Database backup was created with files in location A Database files then moved to location B using ALTER DATABASE MODIFY FILE

Now restoring the backup using WITH REPLACE created when files were in location A. Should the WITH MOVE option be specified with the new location? Or does WITH REPLACE imply using the current locations regardless of where they were when the backup was created?

Upvotes: 5

Views: 13913

Answers (2)

DanT
DanT

Reputation: 11

It looks like the Microsoft changed the behavior in SQL Server 2012 vs 2008. If you back up a database with files on location A, and then files move to location B then, when you restore the database using "WITH REPLACE" and without "WITH MOVE", the restore succeeds in SQL 2005/2008 even if location A does not exist. After the restore, the database would still have the files on location B (as of before the restore). However, the exactly same scenario with SQL 2012, the restore fails with the error that you need to use the "WITH MOVE" option.

Not sure if that was intended or not, since I have not found the change being documented by Microsoft...

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

You don't have to specify WITH MOVE.

Upvotes: 4

Related Questions