Solved

Copy Database to Other New Database

Posted on 2008-10-08
4
161 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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use TOP 1 in a T-SQL sub-query? 14 48
SQL Server: SNAPSHOT replication to include a newly added table. 2 33
Addition to SQL for dynamic fields 6 56
SQL Error - Query 6 50
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…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

756 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