Improve company productivity with a Business Account.Sign Up

x
?
Solved

Copy Database to Other New Database

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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