MS SQL Server - backup devices export

Author

backup devices export

DKRReddy

2005-06-24, 3:23 am

Hi, I am updagrading database server to new datbase server.
I want to transfer the backup devices info from old server to new
server.
is there any way so that I can script backup devices on old server
and deploy them to new server.
This will save me lot of time.

Thanks in advance

Ami Levin

2005-06-24, 3:23 am

Hi DKR,

I don't think you can script the backup devices from any of the management
tools.
SQL DMO has a Backup Device object with a Script method but that means you
will need to write procedural code for that.
See
http://msdn.microsoft.com/library/d...ef_m_s_8wz6.asp
The backup devices are stored in the "sysdevices" table in the master
database.
Restoring the master DB will restore all backup devices but it will have
many more implications that you usually don't want to mess with.
Since each device is stored as a simple single row in sysdevices, Perhaps
the easiest way will be to simply export the data from the table and import
it back on your new installation.
The sysdevices table is a "stand alone" table with no reference to any other
tables so it should be pretty straight forward.
You will need to configure the server to allow updates to system tables
using sp_configure:

EXEC sp_configure 'Show Advnaced Options',1
RECONFIGURE
EXEC sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE

INSERT INTO master..sysdevices
SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
-- 0 is used for the system DB files

EXEC sp_configure 'Allow Updates',0
RECONFIGURE

* WARNING - Messing with system tables is not recommended and not supported
by MS.

HTH

Ami

"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
>
> Thanks in advance
>
>

Hari Prasad

2005-06-24, 3:23 am

Hi,

You could write a script with system stored procedure sp_addumpdevice based
on MASTER..SYSDEVICES table.

Thanks
Hari
SQL Server MVP

"DKRReddy" <dkrreddy@hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
>
> Thanks in advance
>
>

DKRReddy

2005-06-24, 1:23 pm

Thank you very much.

"Ami Levin" < XXX__NO_SPAM__XXX__L
evin_Ami@Yahoo.com> wrote in message
news:OL$SDEJeFHA.2420@TK2MSFTNGP15.phx.gbl...
> Hi DKR,
>
> I don't think you can script the backup devices from any of the management
> tools.
> SQL DMO has a Backup Device object with a Script method but that means you
> will need to write procedural code for that.
> See
>
http://msdn.microsoft.com/library/d...ef_m_s_8wz6.asp
> The backup devices are stored in the "sysdevices" table in the master
> database.
> Restoring the master DB will restore all backup devices but it will have
> many more implications that you usually don't want to mess with.
> Since each device is stored as a simple single row in sysdevices, Perhaps
> the easiest way will be to simply export the data from the table and
import
> it back on your new installation.
> The sysdevices table is a "stand alone" table with no reference to any
other

> tables so it should be pretty straight forward.
> You will need to configure the server to allow updates to system tables
> using sp_configure:
>
> EXEC sp_configure 'Show Advnaced Options',1
> RECONFIGURE
> EXEC sp_configure 'Allow Updates',1
> RECONFIGURE WITH OVERRIDE
>
> INSERT INTO master..sysdevices
> SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
> -- 0 is used for the system DB files
>
> EXEC sp_configure 'Allow Updates',0
> RECONFIGURE
>
>
> * WARNING - Messing with system tables is not recommended and not
supported
> by MS.
>
> HTH
>
> Ami
>
> "DKRReddy" <dkrreddy@hotmail.com> wrote in message
> news:%23RoyzSIeFHA.1400@TK2MSFTNGP15.phx.gbl...
>
>

Sponsored Links