Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy Database to Other New Database

Posted on 2008-10-08
4
Medium Priority
?
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
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…

704 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