We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQL Server 2005 Restore to Different Database Issue

ChiBella
ChiBella asked
on
Medium Priority
1,523 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)
Comment
Watch Question

SQL Server Architect
CERTIFIED EXPERT
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.