92

I am trying to create a new database from an old backup of database on the same server. When using SQL server management studio and trying to restore to the new DB from the backup I get this error

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'test' database. (Microsoft.SqlServer.Smo)

after googling around I found this piece of code

    RESTORE DATABASE myDB

 FROM DISK = 'C:\myDB.bak'

 WITH MOVE 'myDB_Data' TO 'C:\DATA\myDB.mdf',

MOVE 'myDB_Log' TO 'C:\DATA\myDB_log.mdf'
GO

I was wondering will the move statements mess with the database that the backup came from on that server?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Flywheel
  • 923
  • 1
  • 6
  • 4

5 Answers5

149

What I should to do:

  • Click on 'Restore Database ...' float menu that appears right clicking the "Databases" node on SQL Server Management Studio.
  • Fill wizard with the database to restore and the new name.
  • Important If database still exists change the "Restore As" file names in the "Files" tab to avoid "files already in use, cannot overwrite" error message.

What I do

IDk why I prefer to do this:

  • I create a blank target database with my favorite params.
  • Then, in "SQL Server Management Studio" restore wizard, I look for the option to overwrite target database. It is in the 'Options' tab and is called 'Overwrite the existing database (WITH REPLACE)'. Check it.
  • Remember to select target files in 'Files' page.

You can change 'tabs' at left side of the wizard (General, Files, Options)

dani herrera
  • 48,760
  • 8
  • 117
  • 177
  • 2
    Thank you very much, I had tried using the with Replace option in the Management Studio before But didnt do the Files part. Did what you suggested and the databse is being created now. – Flywheel Apr 24 '12 at 14:39
  • I ran into this issue when trying solution above --> "The backup set holds a backup of a database other than the existing" what worked brilliantly for me was solution found here: https://stackoverflow.com/questions/10204480/sql-server-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing – user7023213 Oct 02 '20 at 12:27
97

It's even possible to restore without creating a blank database at all.

In Sql Server Management Studio, right click on Databases and select Restore Database... enter image description here

In the Restore Database dialog, select the Source Database or Device as normal. Once the source database is selected, SSMS will populate the destination database name based on the original name of the database.

It's then possible to change the name of the database and enter a new destination database name.

enter image description here

With this approach, you don't even need to go to the Options tab and click the "Overwrite the existing database" option.

Also, the database files will be named consistently with your new database name and you still have the option to change file names if you want.

Catch22
  • 3,261
  • 28
  • 34
  • 3
    This solution worked for me but only if I specifically change the "Restore As" file names in the "Files" tab, to something that did not exist already. Otherwise I get a "files already in use, cannot overwrite" failed restore error message. – Jeremy Apr 05 '17 at 14:51
  • 2
    This worked for me as well! :) I had to disable `Take tail-log backup before restore` since I was getting an error as described here https://stackoverflow.com/a/29497004/5588197 – Jan Paolo Go Aug 20 '19 at 14:29
  • every time I do this it puts the source database (say we restore `X` to `new_X`, then the source database is `X`), into an unusable "Restoring..." state where it is not queryable – DWR Dec 22 '20 at 17:10
  • worked in mssql 2019 – coder kemp Aug 24 '23 at 14:55
10

Checking the Option "Overwrite the existing database" worked for me:

enter image description here

TylerH
  • 20,799
  • 66
  • 75
  • 101
Kbdavis07
  • 1,012
  • 13
  • 24
  • This answer needs more information; what is the name of the window that shows these menu options and how does one access it? – TylerH Mar 22 '23 at 15:35
1

Think of it like an archive. MyDB.Bak contains MyDB.mdf and MyDB.ldf.

Restore with Move to say HerDB basically grabs MyDB.mdf (and ldf) from the back up, and copies them as HerDB.mdf and ldf.

So if you already had a MyDb on the server instance you are restoring to it wouldn't be touched.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
0

The script in the question is just missing the replace statement so the restore script will be

RESTORE DATABASE myDB

 FROM DISK = 'C:\myDB.bak' ,

 WITH MOVE 'myDB_Data' TO 'C:\DATA\myDB.mdf',
,
MOVE 'myDB_Log' TO 'C:\DATA\myDB_log.mdf' ,  NOUNLOAD,  REPLACE,  STATS = 5
Tareq
  • 1,397
  • 27
  • 28