How can I restore a backup of a db into another db

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

SQL Server 2005 Forums

SQL Server Administration (2005)

How can I restore a backup of a db into another db

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2012-07-05 : 08:08:24

I have two databases db1 and db2 on the same SQL Server
I took the backup of db1 using the query
BACKUP DATABASE [db1] TO DISK = 'C:\\db\\db1.bak'

Now I tried to restore the backup in db2 using the query

RESTORE DATABASE db2
FROM DISK = 'C:\\db\\db1.bak'
WITH MOVE 'db2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\db2.mdf',
MOVE 'db2_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\db2_log.ldf'

but it resulted in error
How can I restore a backup of a db into another db in the same server

Thanks and Regards
Anu Palavila

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-05 : 08:30:32

and the error is?

No, you're never too old to Yak'n'Roll if you're too young to die.

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2012-07-05 : 08:36:20

sorry webfred in the above I had used WITH MOVE not WITH REPLACE
and the error I am getting is

The backup set holds a backup of a database other than the existing 'db2' database.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Thanks and Regards
Anu Palavila

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-05 : 09:11:54

Drop DB2 first or restore to DB3.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2012-07-06 : 01:45:48

restore to DB3 will also results in the same error nigelrivett

Thanks and Regards
Anu Palavila

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2012-07-06 : 02:28:19

Thankyou for all valuable replays finally it worked with the following query I missed WITH MOVE REPLACE

RESTORE DATABASE db3
FROM DISK = 'C:\\db\\db1.bak'
WITH MOVE 'db1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\db3.mdf',
MOVE 'db1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\db3_log.ldf',REPLACE

Thanks and Regards
Anu Palavila

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources