Sql Server 2005 Restore Failing

Question

Running sql server 2005 I have database A. I am trying to restore from a backup of A to database B. I want to retain the database A and create a new testing database B from a previous set of data.

I tried to create B and restore from the .bak AND restore database to B from management studio.

The error is...

TITLE: Microsoft SQL Server Management

Studio

Restore failed for Server '195448-APP2'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.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 'B' database. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------ BUTTONS:

OK

I found this snippet which I am hesitant to use and want to ask if it would solve my problem of changing the location of the mdf and ldf during the process of restoring the database or does it replace database A's items altogether.

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = 'C\:BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
REPLACE

[http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/][1]

and for me I would make it...

RESTORE DATABASE B
FROM DISK = 'C:\backupofA.bak'
WITH    
MOVE 'B' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B.mdf',
MOVE 'B_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B_log.ldf',
REPLACE

What I don't know is if it will affect database A at all. I am hoping the replace refers files associated with B.

or if it should be

RESTORE DATABASE B
FROM DISK = 'C:\backupofA.bak'
WITH    
MOVE 'A' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B.mdf',
MOVE 'A_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\B_log.ldf',
REPLACE

If anyone could help me with the error and/or confirm this fix I would be very grateful as it is not my database I'm playing with.

Thanks.

Answer 1

You could simply use the Copy Database Wizard. If you wanna do it like pros and use T-SQL the RESTORE .. MOVE ... REPLACE will do what you expected: move the two files at the locations you intend and replace database B with content from the backup. A will be unaffected.

Answer 2

I would use the wizard if I were you: In Sql Server Management Studio right click on "Databases" and select "Restore Database...". This dialog / wizard will do exactly what you are asking - simply select the source .bak file(s) / Database that you want to restore from, enter the name of the database you want to restore to and hit "Ok".

Some notes - if you enter the name of a database that doesnt yet exist (it sounds like this is what you want to do), it will create that database for you. If you enter the name of an existing database it will attempt to restore to that database. If you attempt to restore to an existing database from a backup made of a different database it will fail, however you can force the Sql Server to overwrite the existing database by going to "Options" and checking the "Overwrite the existing database" checkbox.

Also, if you are restoring a backup of an existing database to create a new second copy of that database you may find that the wizard fails as its attempting to create a database using the same database file paths as the ones currently in use by the source database. To fix this you need to click on "Options" and change all of the "Restore As" file paths to files that dont yet exist.

You can also get this wizard to generate an SQL script instead of actually performing the actual restore (click on the "script" button at the top), which is handy if you want learn how to do this sort of thing in raw SQL instead.

Answer 3

drop database b first. then do the restore.

Answer 4

It is possible to restore a database from sql server 7, sql server 2000 to sql server 2005. It can be achieved by using restore and with replace command

use master 

restore database mydatabase from disk ='c:\mybackup.bak' with replace

It is easy to take a backup in sql server using this script without any external tools.

Check out Restore with Example.