Restore database to different database? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Restore database to different database?

Author

Topic

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-10-08 : 14:09:27

hello everyone.
Have a question on how to restore a database to a different database.

I have a full backup of a test database in my environment.
I want to take that backup and basically, restore it to a different database instance on the same server? Make sense?

So if the first DB is called "Mickey", I want to create a new database instance on the same server as "Mickey" and call that new instance "Daffy".

Then, i want to take the full backup of "Mickey" and restore it into "Daffy".

Is it possible to do this?
Thanks.

Jason

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-08 : 14:21:22

RESTORE DATABASE Daffy
FROM DISK = 'E:\Backup\Mickey.bak'
WITH REPLACE, MOVE 'Mickey_Data' TO 'E:\Data\Daffy_Data.mdf', MOVE 'Mickey_Log' TO 'E:\Data\Daffy_Log.ldf'

You'll need to make the appropriate changes to the above code. You can also do this through the restore GUI by modifying the option page.

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

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-08 : 14:23:01

What is your requirement? Didn't get what you meant?

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2008-10-08 : 14:23:21

quote:
Originally posted by tkizer

RESTORE DATABASE Daffy
FROM DISK = 'E:\Backup\Mickey.bak'
WITH REPLACE, MOVE 'Mickey_Data' TO 'E:\Data\Daffy_Data.mdf', MOVE 'Mickey_Log' TO 'E:\Data\Daffy_Log.ldf'

You'll need to make the appropriate changes to the above code. You can also do this through the restore GUI by modifying the option page.

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

Subscribe to my blog

Thanks Tara. I will give that a shot and report my results.
Cheers,

Jason

systemaddictshock
Starting Member

1 Post

Posted - 2010-02-04 : 16:49:49

The GUI method worked for me! It was pretty easy too!

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 03:50:59

I also like to change the (internal) logical names of the database - otherwise they keep the name of the original


ALTER DATABASE MyDatabaseName 
		MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabaseName_data')
GO
ALTER DATABASE MyDatabaseName 
		MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabaseName_log')
GO

(but you will have to find out what the original names were)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources