job.answiz.com
  • 4
Votes
name

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 DirectoryDATAA.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 somebody searches a solution in the GUI of Management Studio after already using the Optionspage 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 *.ldffile.

  • 0
Reply Report

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
Reply Report
name
  • 0

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 DirectoryDATAB.mdf',
MOVE 'LogFileLogicalName' TO 'C:SQL DirectoryDATAB.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.

  • 0
Reply Report