Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brin
bin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen
|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedu
le
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 database
s
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>sql

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I am wondering if it's possible to run multiple backup jobs from one
>> shedule in SQL Server 2000, so all backups are triggered simultaneously
>> at a given time.
>> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
>> they have a 1 to 1 relationship and this leads me to believe this is not
>> possible. Has anyone else ever achieved this, and if so, how?
>> Thanks in advance
>> Brin
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:
> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> > bin
> >
> > The second backup will wait till the first one completed.
> > What is the purpose?
> >
> >
> >
> >
> > "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> > news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I am wondering if it's possible to run multiple backup jobs from one
> >> shedule in SQL Server 2000, so all backups are triggered simultaneously
> >> at a given time.
> >>
> >> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> >> they have a 1 to 1 relationship and this leads me to believe this is not
> >> possible. Has anyone else ever achieved this, and if so, how?
> >>
> >> Thanks in advance
> >>
> >> Brin
> >>
> >
> >
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPM
As always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM
|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPMAs always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] ='DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] ='DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:
> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going to
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick them
> off after the previous completes you never have to worry about adjusting the
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] => 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] => 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> > is it best to kick off the backups at the same time, i.e. 1am, or to space
> > them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> >
> > Any links or articles welcome.
> >
> > Thanks,
> > MPM
>
>

Multipe DBs on 1 Server backup schedule

is it best to kick off the backups at the same time, i.e. 1am, or to space
them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
Any links or articles welcome.
Thanks,
MPMAs always it depends. But in general if you can issue the backups at
different times you will most likely get better performance for each and
less impact on the general users. This is due to the large amounts of I/O
(and to some extent CPU) that backups can induce. If you run multiple
backups at the same time you run the risk of maximizing your I/O bandwidth
and even causing network bottlenecks if done remotely. If you are going to
backup multiple dbs one right after the other it is best to do them in a
loop vs. setting specific times. That is because you never know if the
first is finished before the second one starts and so on. If you kick them
off after the previous completes you never have to worry about adjusting the
times as DB sizes change. Here is a sample of that:
-- Backup all user Databases --
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Pubs_Full')
EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
'C:\Data\Backups\DD_Pubs_Full.bak'
IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
'DD_Northwind_Full')
EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
'C:\Data\Backups\DD_Northwind_Full.bak'
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'DD_' + @.DBName + '_Full'
SET @.Name = @.DBName + N' Full Backup'
SET @.Descript = @.DBName + N' Full Backup - ' +
CONVERT(NVARCHAR(16),GETDATE(),112)
BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name,
DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
> is it best to kick off the backups at the same time, i.e. 1am, or to space
> them out in 20 minute slots, 12.20, 12.40, 1am, 1.20 and 1.40?
> Any links or articles welcome.
> Thanks,
> MPM|||Andrew,
Thanks a lot for that excellent reply.
MPM
"Andrew J. Kelly" wrote:

> As always it depends. But in general if you can issue the backups at
> different times you will most likely get better performance for each and
> less impact on the general users. This is due to the large amounts of I/O
> (and to some extent CPU) that backups can induce. If you run multiple
> backups at the same time you run the risk of maximizing your I/O bandwidth
> and even causing network bottlenecks if done remotely. If you are going t
o
> backup multiple dbs one right after the other it is best to do them in a
> loop vs. setting specific times. That is because you never know if the
> first is finished before the second one starts and so on. If you kick the
m
> off after the previous completes you never have to worry about adjusting t
he
> times as DB sizes change. Here is a sample of that:
>
> -- Backup all user Databases --
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Pubs_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Pubs_Full',
> 'C:\Data\Backups\DD_Pubs_Full.bak'
> IF NOT EXISTS (SELECT * FROM master..sysdevices WHERE [Name] =
> 'DD_Northwind_Full')
> EXEC sp_addumpdevice 'disk', 'DD_Northwind_Full',
> 'C:\Data\Backups\DD_Northwind_Full.bak'
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100)
> DECLARE @.Name NVARCHAR(150), @.Descript NVARCHAR(255)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMP
DB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'DD_' + @.DBName + '_Full'
> SET @.Name = @.DBName + N' Full Backup'
> SET @.Descript = @.DBName + N' Full Backup - ' +
> CONVERT(NVARCHAR(16),GETDATE(),112)
> BACKUP DATABASE @.DBName TO @.Device WITH INIT, NOUNLOAD, NAME = @.Name
,
> DESCRIPTION = @.Descript, NOSKIP, STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM @.Device WITH FILE = 1
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:904F98D8-D08B-4B33-8561-65355775CE0F@.microsoft.com...
>
>