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.
This behavior is by design in SQL Server 2012.
To work around this issue, do not use the WITH REPLACE
option of the RESTORE
command. Instead, use the WITH MOVE
The WITH MOVE
option provides an option to change the physical file name.
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)
Article ID: 2839722 - Last Review: April 22, 2013 - Revision: 1.0
- Microsoft SQL Server 2012 Developer
- Microsoft SQL Server 2012 Enterprise
- Microsoft SQL Server 2012 Express
- Microsoft SQL Server 2012 Standard
- Microsoft SQL Server 2012 Web
- SQL Server 2012 Enterprise Core
|kbsurveynew kbtshoot kbexpertiseadvanced KB2839722|