SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 How can I restore a backup of a db into another db		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anupalavila
Yak Posting Veteran

54 Posts
Posted - 07/05/2012 :  08:08:24  Show Profile  Reply with Quote
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
Edited by - anupalavila on 07/05/2012 08:39:13

webfred
Flowing Fount of Yak Knowledge

Germany
8513 Posts
Posted - 07/05/2012 :  08:30:32  Show Profile  Visit webfred's Homepage  Reply with Quote
and the error is?

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

anupalavila
Yak Posting Veteran

54 Posts
Posted - 07/05/2012 :  08:36:20  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 07/05/2012 :  09:11:54  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

anupalavila
Yak Posting Veteran

54 Posts
Posted - 07/06/2012 :  01:45:48  Show Profile  Reply with Quote
restore to DB3 will also results in the same error nigelrivett

Thanks and Regards
Anu Palavila
Go to Top of Page

anupalavila
Yak Posting Veteran

54 Posts
Posted - 07/06/2012 :  02:28:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000