Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2005 Restore to Different Database Issue

Posted on 2009-02-18
1
Medium Priority
?
1,507 Views
Last Modified: 2012-05-06
Hi, I have a database backup that I am trying to restore to another database on the same server (different name). I keep getting the the error below...

Is there a way to restore a database to a different database with different name? I went to options and selected "Overwrite the existing database" but the restore options seem wrong (wrong names).
What am I doing wrong?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server xxxxxxxxxx. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'xxx_DEV_V1' database. (Microsoft.SqlServer.Smo)
0
Comment
Question by:ChiBella
1 Comment
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 23680936
I believe this is telling you that you have multiple database backups in this set or that the existing one that you tried to overwrite is not the same database as the backup is.

You need to verify what you have in the backup set.  I would do a
RESTORE FILELISTONLY FROM DISK='path to file'

and see what is in the backup set.  Also, you do not need to use the Overwrite option if the database does not exist yet.

I gather that you want to keep the current database and restore the database backup to a new database name.  You must move the files to a new location because it will want to restore the files to the same location as they were backed up from.  You cannot do this because there is already an active database existing with those files.

If you are comfortable with TSQL and restore then use
RESTORE DATABASE newname FROM DISK='path to file'
WITH MOVE 'first file logical name' TO 'new path for this file',
          MOVE 'second file logical name' TO 'new path for this file'

and if the database exists (newname) then use the ", REPLACE" after the last statement above.  

If you want to use SSMS then you will want to Right click on Databases and choose Tasks, Restore Database, then type in the database name that you want to restore to and choose from Device.
Click Add and get to your file.  Then you should see a list of database backups in the box below where you chose the file.  You have to choose one of the checkboxes to restore which database backup.  Make sure that the name in the Database Name is your 'newname' of your database.
Then you will need to go to the Options tab and look at the files listing, and then go to the paths of the files and change them to be what they should be to be in a new place and have a new name that does not conflict with already existing databases.

That is a lot, let me know if you have any questions or need more information on this.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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 …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

810 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