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?
Thanks, all help appreciated.
This question is related to
sql
database
sql-server-2008
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...
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.
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.
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.
Source: Stackoverflow.com