Solved

Copy Database to Other New Database

Posted on 2008-10-08
4
158 Views
Last Modified: 2010-03-19
I need to copy my existing database called Tribase to a new one called Tribase2 in SQL 2005 Express. I created a new Database Called Tribase2 and made a backup of Tribase then used the following to restore to Tribase2: restore database Tribase2  from disk = 'C:\Inetpub\ftproot\Backups\SQLBackups\TribaseBU.bak'   But get the following error:  "The backup set holds a backup of a database other than the existing 'Tribase2' database."   I am not an expert so Im probably missing something. How can I effectively make this work.
0
Comment
Question by:waf771
  • 2
4 Comments
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 200 total points
ID: 22672918
When you restore over the top of the database taken from the same server, you need to use the WITH MOVE clause to move the files.

You need to find out the names of the files with the RESTORE FILELISTONLY and then move them to another place.
RESTORE FILELISTONLY 
   FROM disk = 'C:\Inetpub\ftproot\Backups\SQLBackups\TribaseBU.bak'  ;
 
RESTORE DATABASE Tribase2 
   FROM disk = 'C:\Inetpub\ftproot\Backups\SQLBackups\TribaseBU.bak'  
   WITH REPLACE,
   MOVE 'DataFileName' TO 'C:\MySQLServer\testdb.mdf',
   MOVE 'DataFileName_Log' TO 'C:\MySQLServer\testdb.ldf';

Open in new window

0
 

Author Comment

by:waf771
ID: 22673557
I want to copy the files not move them. Can you explain further?
0
 
LVL 4

Expert Comment

by:randy_knight
ID: 22675581
WITH MOVE just means you are chaing the location of the files in the database being restored to.  You are "moving" the files to a different location than the catalog had them.
0
 
LVL 4

Accepted Solution

by:
randy_knight earned 300 total points
ID: 22683884
one more thing.  When you Restore WITH MOVE, it does not update the logical name of the devices in sysfiles.  If you want to clean that up, you can exectue the following:

ALTER DATABASE <dbnmae> MODIFY FILE (NAME = <oldname>, NEWNAME = <newname>)
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question