Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy Database to Other New Database

Posted on 2008-10-08
4
Medium Priority
?
169 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 800 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 1200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

971 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