Restore Database Different Name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Restore Database Different Name

Author

Topic

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 15:22:51

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)

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

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

OK
------------------------------

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 15:43:13

Restore under different name with MOVE option.

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 16:41:48

where is the Move option?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-18 : 16:55:57

In the GUI, it is on the Options page.

Here is an example using the T-SQL command though:

RESTORE DATABASE db1
FROM DISK = 'F:\Backup\db1.bak'
WITH REPLACE, MOVE 'db1_Data' TO 'F:\Data\db1.mdf', MOVE 'db1_Log' TO 'F:\Log\db1.ldf'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 17:00:19

I do not see Move option on Options page only restore options...

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 17:09:03

I don't understand what the move does? I'm trying to restore a .bak file to a SQL Server 2005 existing database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-18 : 17:19:49

The move option is on the GUI screen is the "Restore As" field on the Options page.

You need to use this option as the other database is already using that path and files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 17:30:04

The "Restore As" path contains an mdf file that is different from the "Original File Name" mdf file name. I must be missing some information. The "Original" and "Restore as" do not contain the same file names. I still get the error message.

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 17:33:15

This is the script:
RESTORE DATABASE [METS_DEV_V1] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\METS_DEV.bak' WITH FILE = 6,
MOVE N'METS_DEV' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\METS_DEV_V1.mdf',
MOVE N'METS_DEV_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\METS_DEV_V1.ldf', NOUNLOAD, STATS = 10
GO

and here is the error:
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'METS_DEV_V1' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 17:38:48

Restore with different name and change the location of MDF and LDF file.

BellaScout
Starting Member

25 Posts

Posted - 2009-02-18 : 17:47:35

I changed the name of the RESTORE DATABASE to a non-existent database and it did work, but can't I restore into an existing "different" database?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-18 : 18:06:22

Yes you can do that. But you aren't filling out the restore screens correctly.

Make sure you modify the "restore as" fields as well as check the overwrite checkbox.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources