Home / server / Questions / 244665
Accepted
smwikipedia
Asked: 2011-03-08 23:34:26 +0800 CST

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

  • 772

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~

sql-server sql

5 Answers

  1. Best Answer

    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.

    • 23

  2. 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

    enter image description here

    • 13

  3. 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.

    • 1

  4. When you restore backup you can specify datafiles to restore.

    Look here and here. You can use "Restore the database files as" option and "Overwrite the existing database" flag.

    • 0

  5. Are you using the REPLACE option, either in the TSQL command, or as a selected checkbox? Alternatively, you can rename the files, and call the database something else.

    You'll also find it slightly difficult to restore over a database that is being used..... you'll need to kill the processes using the database; OR drop/delete the database first, closing the connections (this is probably the easiest); OR set the database you want to overwrite into something like restricted user mode with rollback immediate so hopefully only DBAs can use; OR even stop SQL Server, and restart it - hopefully getting the restore rolling before anyone/anything uses that database.

    PS take a backup of the database you are about to overwrite, just in case.

    • 0