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
> >>
> >
> >
>
>

Multiple backups happening

I had some problems configuring a backup of a sql server. The problem
was access rights to the network share. Now when I resolved it, my SQL
server is backing itself twice on the same share with two different
file names. There is a great possibility of my trying to configure the
SQL backup more than once since it failed the first few times.
Now I suspect that I did configured the backup more than once and as a
result it is backing itself twice.
Now is there a way I can view and delete all the backups configured for
a single database.
The problem may be something very different from what I feel becuase I
am very very new to SQL and its properties.
Thanks in advance
GauravYou can Enterprise Manager and Go to Management -> Sql Server Agent
->Jobs. You will find a list of all the jobs(includes backup). you
can delete the job from there. Hope your problem gets resolved|||Check out your maintenance plans and your SQL Server agent jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gaurav" <gaurav081@.gmail.com> wrote in message
news:1146664173.026249.251370@.y43g2000cwc.googlegroups.com...
>I had some problems configuring a backup of a sql server. The problem
> was access rights to the network share. Now when I resolved it, my SQL
> server is backing itself twice on the same share with two different
> file names. There is a great possibility of my trying to configure the
> SQL backup more than once since it failed the first few times.
> Now I suspect that I did configured the backup more than once and as a
> result it is backing itself twice.
> Now is there a way I can view and delete all the backups configured for
> a single database.
> The problem may be something very different from what I feel becuase I
> am very very new to SQL and its properties.
> Thanks in advance
> Gaurav
>

Multiple backups happening

I had some problems configuring a backup of a sql server. The problem
was access rights to the network share. Now when I resolved it, my SQL
server is backing itself twice on the same share with two different
file names. There is a great possibility of my trying to configure the
SQL backup more than once since it failed the first few times.
Now I suspect that I did configured the backup more than once and as a
result it is backing itself twice.
Now is there a way I can view and delete all the backups configured for
a single database.
The problem may be something very different from what I feel becuase I
am very very new to SQL and its properties.
Thanks in advance
GauravYou can Enterprise Manager and Go to Management -> Sql Server Agent
->Jobs. You will find a list of all the jobs(includes backup). you
can delete the job from there. Hope your problem gets resolved|||Check out your maintenance plans and your SQL Server agent jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gaurav" <gaurav081@.gmail.com> wrote in message
news:1146664173.026249.251370@.y43g2000cwc.googlegroups.com...
>I had some problems configuring a backup of a sql server. The problem
> was access rights to the network share. Now when I resolved it, my SQL
> server is backing itself twice on the same share with two different
> file names. There is a great possibility of my trying to configure the
> SQL backup more than once since it failed the first few times.
> Now I suspect that I did configured the backup more than once and as a
> result it is backing itself twice.
> Now is there a way I can view and delete all the backups configured for
> a single database.
> The problem may be something very different from what I feel becuase I
> am very very new to SQL and its properties.
> Thanks in advance
> Gaurav
>

multiple backups and log shipping question

Hi,
I want to setup log shipping to a remote site for disaster
recovery purpouses for a SQL 7 database.
The question is how log shipping will intefere with my
current backups on local site which I need to keep?
I am doing a full db backup each night daily and one
transaction log backup done at noon.
For log shipping I would like to have a full restore done
at the end of the month and transaction logs every 15
minutes.
Is there any way of implementing this log shipping and
keeping also the daily local full backup plan?
Thank you,
Zorba
Zorba,
After you have the log-shipping process running, you can continue to perform
full and differential database backups of your production database, in
accordance with your usual backup schedule, without interfering with log
shipping. See
(http://www.winnetmag.com/SQLServer/A...804/15804.html).
hth,
Paul Ibison

Multiple backup streams in SQL Server 2000

We have a 2TB Database on SQL Server 2000.
We make online backups every night of this database with the backup tool HP
Dataprotector 5.5
Does anyone know if it's possible to backup with more than one stream?
Backup windows are running out of time...
In dataprotector is an option "Concurrent streams" but this option is grade
out.
Thanks for your answers!!
Kind regards,
Rob van Acquoy
CTAC
Rob
I'm not familiar with that tool. Did you run BACKUP DATABASE command ....?
Do you have filegroup for the database?
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7EB9CEEA-5B49-4D90-9FE5-F67D05E267BC@.microsoft.com...
> We have a 2TB Database on SQL Server 2000.
> We make online backups every night of this database with the backup tool
> HP
> Dataprotector 5.5
> Does anyone know if it's possible to backup with more than one stream?
> Backup windows are running out of time...
> In dataprotector is an option "Concurrent streams" but this option is
> grade
> out.
> Thanks for your answers!!
> Kind regards,
> Rob van Acquoy
> CTAC

Multiple backup streams in SQL Server 2000

We have a 2TB Database on SQL Server 2000.
We make online backups every night of this database with the backup tool HP
Dataprotector 5.5
Does anyone know if it's possible to backup with more than one stream'
Backup windows are running out of time...
In dataprotector is an option "Concurrent streams" but this option is grade
out.
Thanks for your answers!!
Kind regards,
Rob van Acquoy
CTACRob
I'm not familiar with that tool. Did you run BACKUP DATABASE command ....?
Do you have filegroup for the database?
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7EB9CEEA-5B49-4D90-9FE5-F67D05E267BC@.microsoft.com...
> We have a 2TB Database on SQL Server 2000.
> We make online backups every night of this database with the backup tool
> HP
> Dataprotector 5.5
> Does anyone know if it's possible to backup with more than one stream'
> Backup windows are running out of time...
> In dataprotector is an option "Concurrent streams" but this option is
> grade
> out.
> Thanks for your answers!!
> Kind regards,
> Rob van Acquoy
> CTAC

Multiple backup streams in SQL Server 2000

We have a 2TB Database on SQL Server 2000.
We make online backups every night of this database with the backup tool HP
Dataprotector 5.5
Does anyone know if it's possible to backup with more than one stream'
Backup windows are running out of time...
In dataprotector is an option "Concurrent streams" but this option is grade
out.
Thanks for your answers!!
Kind regards,
Rob van Acquoy
CTACRob
I'm not familiar with that tool. Did you run BACKUP DATABASE command ....?
Do you have filegroup for the database?
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7EB9CEEA-5B49-4D90-9FE5-F67D05E267BC@.microsoft.com...
> We have a 2TB Database on SQL Server 2000.
> We make online backups every night of this database with the backup tool
> HP
> Dataprotector 5.5
> Does anyone know if it's possible to backup with more than one stream'
> Backup windows are running out of time...
> In dataprotector is an option "Concurrent streams" but this option is
> grade
> out.
> Thanks for your answers!!
> Kind regards,
> Rob van Acquoy
> CTAC

Multiple backup job schedules for one database to different devices

I want to know if it is possible to create backups on differnet
schedules of the same database? And if not, if there some solution
that can fit what I am trying to do?
I want to backup more aggressively to a locally attached disk, and less
aggressively to a network device. Specifically a virtual device in SQL
server using NetBackup SQL Connectors. For example, can we have a
backup schedule that creates a dull backup of a database everyday, and
have TLog backups every 30 minutes both to local disk. But we also
want to have a seperate schedule for the network device that does a
weekly fully, and say hourly differentials or TLog backups thru the
week. Would the checkpointing done by the BACKUP commands somehow
clobber one another?
It would be like:
Monday:
3:00AM - Full Backup -> Local Disk (daily)
3:30AM - TLog Backup -> Local Disk
4:00AM - Full Backup -> Network Disk (weekly)
4:00AM - TLog Backup -> Local Disk
4:30AM - TLog Backup -> Local Disk
5:00AM - TLog Backup -> Network Disk
5:00AM - TLog Backup -> Local Disk
5:30AM - TLog Backup -> Local Disk
6:00AM - TLog Backup -> Network Disk
6:00AM - TLog Backup -> Local Disk
....
Tuesday:
3:00AM - Full Backup -> Local Disk (daily)
3:30AM - TLog Backup -> Local Disk
4:00AM - TLog Backup -> Network Disk
4:00AM - TLog Backup -> Local Disk
4:30AM - TLog Backup -> Local Disk
5:00AM - TLog Backup -> Network Disk
...
I don't think this will work, but could it? And if not, would a
differential backup work for the Network Disk? The goal is to minimize
bandwidth because we can't backup several hundred gigabytes every
morning to a remote site. But we need near live backups incase we had
a total meltdown at the other site.
Regards,
--
Mike BrancatoHi Mike
You probably don't want to do this, as it would make finding the necessary
files when rolling forward the transaction log more difficult. You may want
to consider the alternative of always backing up to local disc and then
selectively copy to the network drive (all log backups) when required.
To get uneven intervals you can schedule your backup jobs to have multiple
schedules one for each period, although this would not overcome the change of
backup device or backup type.
John
"mike.brancato@.acs-inc.com" wrote:
> I want to know if it is possible to create backups on differnet
> schedules of the same database? And if not, if there some solution
> that can fit what I am trying to do?
> I want to backup more aggressively to a locally attached disk, and less
> aggressively to a network device. Specifically a virtual device in SQL
> server using NetBackup SQL Connectors. For example, can we have a
> backup schedule that creates a dull backup of a database everyday, and
> have TLog backups every 30 minutes both to local disk. But we also
> want to have a seperate schedule for the network device that does a
> weekly fully, and say hourly differentials or TLog backups thru the
> week. Would the checkpointing done by the BACKUP commands somehow
> clobber one another?
> It would be like:
> Monday:
> 3:00AM - Full Backup -> Local Disk (daily)
> 3:30AM - TLog Backup -> Local Disk
> 4:00AM - Full Backup -> Network Disk (weekly)
> 4:00AM - TLog Backup -> Local Disk
> 4:30AM - TLog Backup -> Local Disk
> 5:00AM - TLog Backup -> Network Disk
> 5:00AM - TLog Backup -> Local Disk
> 5:30AM - TLog Backup -> Local Disk
> 6:00AM - TLog Backup -> Network Disk
> 6:00AM - TLog Backup -> Local Disk
> .....
> Tuesday:
> 3:00AM - Full Backup -> Local Disk (daily)
> 3:30AM - TLog Backup -> Local Disk
> 4:00AM - TLog Backup -> Network Disk
> 4:00AM - TLog Backup -> Local Disk
> 4:30AM - TLog Backup -> Local Disk
> 5:00AM - TLog Backup -> Network Disk
> ...
> I don't think this will work, but could it? And if not, would a
> differential backup work for the Network Disk? The goal is to minimize
> bandwidth because we can't backup several hundred gigabytes every
> morning to a remote site. But we need near live backups incase we had
> a total meltdown at the other site.
> Regards,
> --
> Mike Brancato
>|||The problem with copying the local files is that the SQL backup process
may take a while and hold a lock on the large databse files. also, the
local backups are several hundred gigs every day, but we would like to
backup remotely only Tlogs, etc thru the week becasue we cannot handle
that much bacndwidth from our servers every day.
Is there any way of doing this?
John Bell wrote:
> Hi Mike
> You probably don't want to do this, as it would make finding the necessary
> files when rolling forward the transaction log more difficult. You may want
> to consider the alternative of always backing up to local disc and then
> selectively copy to the network drive (all log backups) when required.
> To get uneven intervals you can schedule your backup jobs to have multiple
> schedules one for each period, although this would not overcome the change of
> backup device or backup type.
> John
> "mike.brancato@.acs-inc.com" wrote:
> > I want to know if it is possible to create backups on differnet
> > schedules of the same database? And if not, if there some solution
> > that can fit what I am trying to do?
> >
> > I want to backup more aggressively to a locally attached disk, and less
> > aggressively to a network device. Specifically a virtual device in SQL
> > server using NetBackup SQL Connectors. For example, can we have a
> > backup schedule that creates a dull backup of a database everyday, and
> > have TLog backups every 30 minutes both to local disk. But we also
> > want to have a seperate schedule for the network device that does a
> > weekly fully, and say hourly differentials or TLog backups thru the
> > week. Would the checkpointing done by the BACKUP commands somehow
> > clobber one another?
> >
> > It would be like:
> >
> > Monday:
> > 3:00AM - Full Backup -> Local Disk (daily)
> > 3:30AM - TLog Backup -> Local Disk
> > 4:00AM - Full Backup -> Network Disk (weekly)
> > 4:00AM - TLog Backup -> Local Disk
> > 4:30AM - TLog Backup -> Local Disk
> > 5:00AM - TLog Backup -> Network Disk
> > 5:00AM - TLog Backup -> Local Disk
> > 5:30AM - TLog Backup -> Local Disk
> > 6:00AM - TLog Backup -> Network Disk
> > 6:00AM - TLog Backup -> Local Disk
> > .....
> > Tuesday:
> > 3:00AM - Full Backup -> Local Disk (daily)
> > 3:30AM - TLog Backup -> Local Disk
> > 4:00AM - TLog Backup -> Network Disk
> > 4:00AM - TLog Backup -> Local Disk
> > 4:30AM - TLog Backup -> Local Disk
> > 5:00AM - TLog Backup -> Network Disk
> > ...
> >
> > I don't think this will work, but could it? And if not, would a
> > differential backup work for the Network Disk? The goal is to minimize
> > bandwidth because we can't backup several hundred gigabytes every
> > morning to a remote site. But we need near live backups incase we had
> > a total meltdown at the other site.
> >
> > Regards,
> >
> > --
> > Mike Brancato
> >
> >|||Hi
With multiple jobs and schedules you can do just about anything. If you have
a highspeed dedicated lan between the backup server and your database server
it should not cause too much of a problem. Without trying it you will not
know!
If you have a dedicated backup server with high speed dedicated lan it may
be better to do everything to the backup server rather than the local server!
You database will not be "locked" while you are backing up.
SAN technologies have methods that can move large volumes of data instantly,
if you are looking at significant amounts of data it would probably worth
investigating this option.
John
"mike.brancato@.acs-inc.com" wrote:
> The problem with copying the local files is that the SQL backup process
> may take a while and hold a lock on the large databse files. also, the
> local backups are several hundred gigs every day, but we would like to
> backup remotely only Tlogs, etc thru the week becasue we cannot handle
> that much bacndwidth from our servers every day.
> Is there any way of doing this?
> John Bell wrote:
> > Hi Mike
> >
> > You probably don't want to do this, as it would make finding the necessary
> > files when rolling forward the transaction log more difficult. You may want
> > to consider the alternative of always backing up to local disc and then
> > selectively copy to the network drive (all log backups) when required.
> >
> > To get uneven intervals you can schedule your backup jobs to have multiple
> > schedules one for each period, although this would not overcome the change of
> > backup device or backup type.
> >
> > John
> >
> > "mike.brancato@.acs-inc.com" wrote:
> >
> > > I want to know if it is possible to create backups on differnet
> > > schedules of the same database? And if not, if there some solution
> > > that can fit what I am trying to do?
> > >
> > > I want to backup more aggressively to a locally attached disk, and less
> > > aggressively to a network device. Specifically a virtual device in SQL
> > > server using NetBackup SQL Connectors. For example, can we have a
> > > backup schedule that creates a dull backup of a database everyday, and
> > > have TLog backups every 30 minutes both to local disk. But we also
> > > want to have a seperate schedule for the network device that does a
> > > weekly fully, and say hourly differentials or TLog backups thru the
> > > week. Would the checkpointing done by the BACKUP commands somehow
> > > clobber one another?
> > >
> > > It would be like:
> > >
> > > Monday:
> > > 3:00AM - Full Backup -> Local Disk (daily)
> > > 3:30AM - TLog Backup -> Local Disk
> > > 4:00AM - Full Backup -> Network Disk (weekly)
> > > 4:00AM - TLog Backup -> Local Disk
> > > 4:30AM - TLog Backup -> Local Disk
> > > 5:00AM - TLog Backup -> Network Disk
> > > 5:00AM - TLog Backup -> Local Disk
> > > 5:30AM - TLog Backup -> Local Disk
> > > 6:00AM - TLog Backup -> Network Disk
> > > 6:00AM - TLog Backup -> Local Disk
> > > .....
> > > Tuesday:
> > > 3:00AM - Full Backup -> Local Disk (daily)
> > > 3:30AM - TLog Backup -> Local Disk
> > > 4:00AM - TLog Backup -> Network Disk
> > > 4:00AM - TLog Backup -> Local Disk
> > > 4:30AM - TLog Backup -> Local Disk
> > > 5:00AM - TLog Backup -> Network Disk
> > > ...
> > >
> > > I don't think this will work, but could it? And if not, would a
> > > differential backup work for the Network Disk? The goal is to minimize
> > > bandwidth because we can't backup several hundred gigabytes every
> > > morning to a remote site. But we need near live backups incase we had
> > > a total meltdown at the other site.
> > >
> > > Regards,
> > >
> > > --
> > > Mike Brancato
> > >
> > >
>

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...
>
>