Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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~

share|improve this question
add comment

migrated from stackoverflow.com Mar 8 '11 at 13:36

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
That's it. Thanks. –  smwikipedia Mar 8 '11 at 14:50
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.