Database file names change when you restore a database by using the WITH REPLACE option in SQL Server 2012

Article translations Article translations
Article ID: 2839722 - View products that this article applies to.
Expand all | Collapse all

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:
  1. A new recovery fork is created on the target server.
  2. On the next restore operation, the recovery fork IDs from the backup set are matched with the IDs of the target server.
  3. 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)

Properties

Article ID: 2839722 - Last Review: April 22, 2013 - Revision: 1.0
Applies to
  • 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
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2839722

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com