Symptoms
When you restore a database from a backup by using the WITH REPLACE option in Microsoft SQL Server 2012, the physical file names of the restored database that comes from the backup database may overwrite the physical file names of the existing database.
For example, consider the following scenario:
-
You have two instances of SQL Server, InstanceA and InstanceB.
-
You have a database that has the same name, SQLDB, on each instance of SQL Server.
-
On InstanceA, the SQLDB database data file name is DB1_InstanceA.mdf and the log file name is DB1_InstanceA.ldf.
-
On InstanceB, the SQLDB database data file name is DB1_InstanceB.mdf and the log file name is DB1_InstanceB.ldf.
-
You try to restore a backup of the SQLDB database from InstanceA over the SQLDB database from InstanceB by using the WITH REPLACE option of the RESTORE command.
In this scenario, the physical file names on SQLDB on InstanceB are changed to DB1_InstanceA.mdf and DB1_InstanceA.ldf.
Cause
This behavior is by design in SQL Server 2012.
Workaround
To work around this issue, do not use the WITH REPLACE option of the RESTORE command. Instead, use the WITH MOVE option.
Note The WITH MOVE option provides an option to change the physical file name.
More Information
The WITH MOVE option is necessary only the first time that you use the RESTORE command. When you try to restore a database from the backup, SQL Server compares the recovery fork values of the database. Then, the backup set takes the following actions, depending on whether those values match:
-
If there is a match, the backup set continues to use the file names of the target database.
-
If the values are different, the backup set replaces the physical file names of the target database by using the names from the backup set.
When you use the WITH MOVE option together with the RESTORE command, the following actions are taken:
-
A new recovery fork is created on the target server.
-
On the next restore operation, the recovery fork IDs from the backup set are matched with the IDs of the target server.
-
A RESTORE command that is used together with the WITH REPLACE option keeps the physical names on the target server.
For more information about the RESTORE command, see RESTORE (Transact-SQL)
