Can I Restore to another db?

[jsteph]

Hi,
I'm hoping this is simple...I have several backup files created by sql 2000 Backup. I want to work with the data, but in an entirely separate db. How would I do that via the Enterprise manager? I can't find anything in DTS, and there is no 'Restore' command via EM that I can see. Thanks,
--Jsteph

 

[TitleistDBA]

Yes you can do this. Here is what you do.
From EM
Create a new DB
Right click on new db
select All Tasks
select Restore Database
click on from device
click on the add button
browse to the file you want to restore.
click ok till you get back to the restore db screen.
click on the options tab.
select force restrore and make sure that the physical path points to the .mdf and .ldf of your new database.
click ok and database will restore.

You can also do this from query analyzer by creating a new blank database and do a restore with Move.

RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = {undo_file_name|@undo_file_name_var} } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

http://www.acushnetcompany.com

 

[jsteph]

Thanks Titleist, that is a big help. Now...is there any way to just restore selected tables, ie, I guess what I'm looking for is to be able to 'browse' a backup, and pick and choose a single table or two to put into an existing database, though but a temp one like you suggest will do.
Thanks,
--jsteph

 

[TitleistDBA]

Sure, you can use DTS. If you right click on the database you want to import the data into and select all tasks then import data. This will bring you to a wizard. Select your source server then click next, by default it will select your destination as the one you right clicked on. When you get to the third step select copy objects and database between SQL servers. The next screen will give you the options to drop and recreate the destination table. Be very carefull what you select here. Make sure you go through all the options. When you are sure you have selected all the options you want continue to click next till you click on finish. This will execute your DTS.
I recommend that you backup the tables in question first. If you run the following
select * into TABLE_BAK from TABLE
you can create a backup table. And if things go bad you can use sp_rename 'new_name', 'old_name' to get your tables back.
Good Luck.

http://www.acushnetcompany.com

 

[Barnardos]

Hi it is posible restore or backup to a destination diferent that the sql server.ie other server in the network?
thanks

 

[jbenson001]

In EM, right click on the data base, choose all tasks, backup database, click the Add button under Destination and choose where you want the back to be stored.