Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.
What is the error message you get at 3rd step?
"helpplease" wrote:

> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.
|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
|||Specify a report file for the maint plan and check the report file for the error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:

Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
> > I have setup my backup job to delete file older than 12 hours. I want to
> > keep atleast 2 backup files saved. The backup runs every night.
> > My backup job fails on alternate days.
> >
> > The day it fails, it completes just 2 steps 'backup database' and 'verify
> > database'.
> >
> > The day is completes sucessfully, it completes 4 steps 'backup database' and
> > 'verify database' and 'delete old backup files' and 'check data and index
> > linkage'.
> >
> > It started failing for last few months. The same setting used to work fine
> > before.
> >
> > Any kind of help is greatly appreciated.|||Specify a report file for the maint plan and check the report file for the error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>> What is the error message you get at 3rd step?
>>
>> "helpplease" wrote:
>> > I have setup my backup job to delete file older than 12 hours. I want to
>> > keep atleast 2 backup files saved. The backup runs every night.
>> > My backup job fails on alternate days.
>> >
>> > The day it fails, it completes just 2 steps 'backup database' and 'verify
>> > database'.
>> >
>> > The day is completes sucessfully, it completes 4 steps 'backup database' and
>> > 'verify database' and 'delete old backup files' and 'check data and index
>> > linkage'.
>> >
>> > It started failing for last few months. The same setting used to work fine
>> > before.
>> >
>> > Any kind of help is greatly appreciated.

Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:

> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' a
nd
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fin
e
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
>|||Specify a report file for the maint plan and check the report file for the e
rror messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on
:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>

Backup fails with A nonrecoverable I/O error occurred for large DB

Hi,
We have just installed an Windows 2003 x64 server with SQL 2005 to replace
our old server. Everything seems to be working fine but the backup.
When trying to backup databases larger than 16MB or so the backup fails with
a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file
"S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
(Microsoft.SqlServer.Smo) message:
Smaller DBs backup fine (master, model, etc). even if I create a new DB it
backups fine until it grows bigger than about 16MB, then it gives the same
error message.
In the eventlog I get 18210 events:
BackupIoRequest::WaitForIoCompletion: read failure on backup device
'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
Windows has latest hotfixes, latest disk drivers, tried disk caching
enabled/disabled, SQL 2005 without service pack as apps guy said no.
Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
Any suggestions what to try?
Thanks,
GergelySince you are getting a I/O error on drive S. Have you tried the same
backups on two or more different drives?
Ben Nevarez, MCDBA, OCP
Database Administrator
"G.Gardonyi" wrote:
> Hi,
> We have just installed an Windows 2003 x64 server with SQL 2005 to replace
> our old server. Everything seems to be working fine but the backup.
> When trying to backup databases larger than 16MB or so the backup fails with
> a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file
> "S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
> (Microsoft.SqlServer.Smo) message:
> Smaller DBs backup fine (master, model, etc). even if I create a new DB it
> backups fine until it grows bigger than about 16MB, then it gives the same
> error message.
> In the eventlog I get 18210 events:
> BackupIoRequest::WaitForIoCompletion: read failure on backup device
> 'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
> Windows has latest hotfixes, latest disk drivers, tried disk caching
> enabled/disabled, SQL 2005 without service pack as apps guy said no.
> Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
> Any suggestions what to try?
> Thanks,
> Gergely|||Yes, same problem. Mind you it's the same external storage unit so can't rule
out some kind of HW problem. It's just weird that 'normal' operations are
running fine but when starting a backup it throws an error instantly.
"Ben Nevarez" wrote:
> Since you are getting a I/O error on drive S. Have you tried the same
> backups on two or more different drives?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "G.Gardonyi" wrote:
> > Hi,
> >
> > We have just installed an Windows 2003 x64 server with SQL 2005 to replace
> > our old server. Everything seems to be working fine but the backup.
> > When trying to backup databases larger than 16MB or so the backup fails with
> > a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file
> > "S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
> > (Microsoft.SqlServer.Smo) message:
> >
> > Smaller DBs backup fine (master, model, etc). even if I create a new DB it
> > backups fine until it grows bigger than about 16MB, then it gives the same
> > error message.
> >
> > In the eventlog I get 18210 events:
> > BackupIoRequest::WaitForIoCompletion: read failure on backup device
> > 'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
> >
> > Windows has latest hotfixes, latest disk drivers, tried disk caching
> > enabled/disabled, SQL 2005 without service pack as apps guy said no.
> > Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
> >
> > Any suggestions what to try?
> >
> > Thanks,
> > Gergely

Backup fails with A nonrecoverable I/O error occurred for large DB

Hi,
We have just installed an Windows 2003 x64 server with SQL 2005 to replace
our old server. Everything seems to be working fine but the backup.
When trying to backup databases larger than 16MB or so the backup fails with
a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on fil
e
"S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
(Microsoft.SqlServer.Smo) message:
Smaller DBs backup fine (master, model, etc). even if I create a new DB it
backups fine until it grows bigger than about 16MB, then it gives the same
error message.
In the eventlog I get 18210 events:
BackupIoRequest::WaitForIoCompletion: read failure on backup device
'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
Windows has latest hotfixes, latest disk drivers, tried disk caching
enabled/disabled, SQL 2005 without service pack as apps guy said no.
Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
Any suggestions what to try?
Thanks,
GergelySince you are getting a I/O error on drive S. Have you tried the same
backups on two or more different drives?
Ben Nevarez, MCDBA, OCP
Database Administrator
"G.Gardonyi" wrote:

> Hi,
> We have just installed an Windows 2003 x64 server with SQL 2005 to replace
> our old server. Everything seems to be working fine but the backup.
> When trying to backup databases larger than 16MB or so the backup fails wi
th
> a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on f
ile
> "S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
> (Microsoft.SqlServer.Smo) message:
> Smaller DBs backup fine (master, model, etc). even if I create a new DB it
> backups fine until it grows bigger than about 16MB, then it gives the same
> error message.
> In the eventlog I get 18210 events:
> BackupIoRequest::WaitForIoCompletion: read failure on backup device
> 'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.
).
> Windows has latest hotfixes, latest disk drivers, tried disk caching
> enabled/disabled, SQL 2005 without service pack as apps guy said no.
> hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
> Any suggestions what to try?
> Thanks,
> Gergely

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 and restore question plz help!

If i have old full database back up of master and msdn and every other database that i had on a old sql server 2000 enterprise. can i use the back up file to recreate the same eviroment on a new installed fresh sql server 2000 enterprise boxYes you can restore to the original state.|||The instance must have the same Service-packs already applied however. For example, If the backups are from SQL with service pack 3. Master won't restore on an SQL without this service pack.

Backup and Move

SQL Nubie need to accomplish two things. 1. backup an SQL2000 db on old hardware. 2. Use backup to build same SQL2000 db onto new hardware, which will replace old hardware as main system. Running Win2000 server standard edition on old server and Win2003 server standard edition on new server. Old server does not have a tape backup, but it has a cd burner and access to a Novell corparate network -without any established Windows domains. Asking for much, hope someone can help. Some source material would go along way. ThanksDump the databases in a local file on your harddisk, using backup SQL statement.
Bcp out the main tables of the master DB.
Copy all these files into your CD and tranfer them in the new hardware.
Recreate a new MS-SQL server in the new machine

do you keep the original tree-architecture in your both machines ?