Re-creating backup devices

[icemel]

Hi,

I have a question about backup devices.

We are moving our databases to another server (2000 to 2005). We have several backup devices defined on the 2000 server that I would like to "transfer" over to the new 2005 server.

Is there an easy way to do this?

Thanks

 

[Catadmin]

If you have any backup devices listed under Management -> Backup, see if you can right click & script the device.

Other than that possibility, I can see no way to script out backup devices for moving. Maybe if you copy over your MSDB, they might be there. Then again, maybe not. I can't find the specific table which lists backup devices.

Sorry I couldn't be more help.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[icemel]

yes, you think the devices would be defined somewhere - but where is the question... no option for scripting as far as i can tell... yet another mystery that i will just have to work around, b/c it will be faster at this point

 

[mrdenny]

You can't copy msdb from SQL 2000 to SQL 2005.

The info for the backup devices are in master.dbo.sysdevices. (So moving msdb wouldn't help any way.)

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

Ah yes, thanks for that table name, mrdenny. I should have thought of that.

Here's my script for recreating devices:

SELECT 'USE master EXEC sp_adddumpdevice ''disk'', ' + '''' + name + '''' + ', ' + '''' + phyname + ''''
FROM sysdevices

 

[mrdenny]

That look correct to me.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com