Creating new database from a backup of another Database on the same server

Question

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

User · Answer

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

User · Answer

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

User · Answer

Checking the Options Over Write Database worked for me

User · Answer

I think that is easier than this     First  create a blank target database  Then  in  SQL Server Management Studio  restore wizard  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

[sql] Creating new database from a backup of another Database on the same server?

Examples related to sql

Examples related to database

Examples related to sql-server-2008