mdf file cannot be overwritten when restoring a database in SQL Server

I got a databse A. It has some data in it. I created a backup for A as A.bak file. Then I create a new empty database B. And then I try to restore B from A.bak. But the SQL Serve tell me the following error:

The file 'C:\SQL Directory\DATA\A.mdf' cannot be overwritten. It is being used by database 'A'.

But if I delete A from SQL Server, the retore is ok.

I don't understand why the SQL needs to write to the original database file while restoring from a seperate backup file?

Thanks~

If you restore a database, SQL Server will, by default, attempt to restore all the data and log files to their original locations. Since those original locations are still in use by the original database ("A"), the restore fails. You need to use the WITH MOVE clause to specify new locations for all the files in the database.

RESTORE DATABASE B FROM DISK = 'A.bak'
WITH MOVE 'DataFileLogicalName' TO 'C:\SQL Directory\DATA\B.mdf',
MOVE 'LogFileLogicalName' TO 'C:\SQL Directory\DATA\B.ldf',
REPLACE --Needed if database B already exists

Something like that anyway. Use RESTORE FILELISTONLY FROM DISK... to see the logical filenames in the backup if necessary.

WITH MOVE / MOVE is the right solution in T-SQL.

By the way, if you want to use the GUI you can go to Files and rename:

• a.MDF
• a.NDF
• a.LDF

to

• b.MDF
• b.NDF
• b.LDF

If somebody searches a solution in the GUI of Management Studio after already using the Options page and activating Overwrite the existing database (WITH REPLACE) option:

Simply click in the Restore As column and change the filenames of the *.mdf file and the *.ldf file.