backup devices export

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
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/de...f_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
>
|||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
>
|||Thank you very much.
"Ami Levin" <XXX__NO_SPAM__XXX__Levin_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/de...f_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...
>

backup devices export

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 advanceHi 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...r />
_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
>|||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
>|||Thank you very much.
"Ami Levin" <XXX__NO_SPAM__XXX__Levin_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[
vbcol=seagreen]
> 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[/vbcol]
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...
>

backup devices export

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 advanceHi 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/default.asp?url=/library/en-us/sqldmo/dmoref_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
>|||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
>|||Thank you very much.
"Ami Levin" <XXX__NO_SPAM__XXX__Levin_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/default.asp?url=/library/en-us/sqldmo/dmoref_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
> >
> >
>

Backup database design

How do I export/create a script into a file with create table...index...
views etc for an existing database ?
Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Backup database design

How do I export/create a script into a file with create table...index...
views etc for an existing database ?Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Backup and View question

Hi,
I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export
Wizard.
I have some Views in the DatabaseA.
However, those Views in DatabaseA became Tables in DatabaseB, not Views.
But what I want is View, not Table.
Would someone tell me how to fix this problem?
Thanks for help.
Jason
This is how the type of DTS wizard task you selected work. If you want the views to be exported as
views, you need to use "Copy objects and data to SQL Server" task type in the Wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export Wizard.
> I have some Views in the DatabaseA.
> However, those Views in DatabaseA became Tables in DatabaseB, not Views.
> But what I want is View, not Table.
> Would someone tell me how to fix this problem?
> Thanks for help.
>
> Jason
>
|||Also you would avoid select them (within import-export wizard) and then
generate them scripts separately.
current location: alicante (es)
"Tibor Karaszi" wrote:

> This is how the type of DTS wizard task you selected work. If you want the views to be exported as
> views, you need to use "Copy objects and data to SQL Server" task type in the Wizard.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
>
>

Backup and View question

Hi,
I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export
Wizard.
I have some Views in the DatabaseA.
However, those Views in DatabaseA became Tables in DatabaseB, not Views.
But what I want is View, not Table.
Would someone tell me how to fix this problem?
Thanks for help.
JasonThis is how the type of DTS wizard task you selected work. If you want the v
iews to be exported as
views, you need to use "Copy objects and data to SQL Server" task type in th
e Wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export
Wizard.
> I have some Views in the DatabaseA.
> However, those Views in DatabaseA became Tables in DatabaseB, not Views.
> But what I want is View, not Table.
> Would someone tell me how to fix this problem?
> Thanks for help.
>
> Jason
>|||Also you would avoid select them (within import-export wizard) and then
generate them scripts separately.
--
current location: alicante (es)
"Tibor Karaszi" wrote:

> This is how the type of DTS wizard task you selected work. If you want the
views to be exported as
> views, you need to use "Copy objects and data to SQL Server" task type in
the Wizard.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
>
>

Backup and View question

Hi,
I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export
Wizard.
I have some Views in the DatabaseA.
However, those Views in DatabaseA became Tables in DatabaseB, not Views.
But what I want is View, not Table.
Would someone tell me how to fix this problem?
Thanks for help.
JasonThis is how the type of DTS wizard task you selected work. If you want the views to be exported as
views, you need to use "Copy objects and data to SQL Server" task type in the Wizard.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export Wizard.
> I have some Views in the DatabaseA.
> However, those Views in DatabaseA became Tables in DatabaseB, not Views.
> But what I want is View, not Table.
> Would someone tell me how to fix this problem?
> Thanks for help.
>
> Jason
>|||Also you would avoid select them (within import-export wizard) and then
generate them scripts separately.
--
current location: alicante (es)
"Tibor Karaszi" wrote:
> This is how the type of DTS wizard task you selected work. If you want the views to be exported as
> views, you need to use "Copy objects and data to SQL Server" task type in the Wizard.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export Wizard.
> > I have some Views in the DatabaseA.
> > However, those Views in DatabaseA became Tables in DatabaseB, not Views.
> > But what I want is View, not Table.
> > Would someone tell me how to fix this problem?
> > Thanks for help.
> >
> >
> > Jason
> >
>
>