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 datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?
Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>
|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]
|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > Is there an advantage (besides backup and restore) of creating multiple
> > datafiles on multiple disks on one physical raid set for a large database
> (>
> > 200GB) ?
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
> > Geert
> > Performance for example if you query a huge table /s that are on
separate
> > physical discks SQL Server will create two threads to retrieve the data
> > which means less I/O .
> >
> >
> > "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> > news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > > Is there an advantage (besides backup and restore) of creating
multiple
> > > datafiles on multiple disks on one physical raid set for a large
database
> > (>
> > > 200GB) ?
> >
> >
> >|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
--
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
>
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

Multiple Database or Full Backup

I can't see a way of backing up all databases at once, or
the ability to choose multiple databases to back up. I am
in the process of moving a bunch of databases from one
laptop to another and I would rather not do it one at a
time.
Ideas?
Thanks in advance!!can you take SQL off line, copy database files to new server and attach
databases?
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
> I can't see a way of backing up all databases at once, or
> the ability to choose multiple databases to back up. I am
> in the process of moving a bunch of databases from one
> laptop to another and I would rather not do it one at a
> time.
> Ideas?
> Thanks in advance!!|||I can! In fact I am doing just that as I write this. I
had hoped there would be a way of doing a full backup and
a full restore bring over security intact, etc., but I
hearing there isn't. Seems rather manual this way!
Thanks!!

>--Original Message--
>can you take SQL off line, copy database files to new
server and attach
>databases?
>"Top Cat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
or
am
>
>.
>|||Hi,
Using a automated script you can
1. Backup all the user databases remotely to Laptop
2. After the backup restore it in Laptop ( You might need to start all the
databases in sinle_user using Alter database statement)
I have a script whckh will Backup remotely, please add the restore portion
in the script
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am|||I had to restore from a bunch of backup files in a directory a while ago, so
I write a stored proc which loops
the files and do the restore for each file. You can have a similar loop to p
erform the backup as well. But you
really need some TSQL programming skill to write the loop to perform the bac
kups, and you probably want to
verify my code which does the restore:
http://www.karaszi.com/sqlserver/ut...all_in_file.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am

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 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 sets restore?

Hi,
I have multiple SQL data backup sets.
Is it possible to take all backup sets and restore them to one Database?
Thanks in advanced,
AsafCan you be a bit more specific? Did you do like a db backup and then a
number of log backups and now want to restore them all? Or did you stripe
the backup?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Asaf" <asafgo@.hotmail.com> wrote in message
news:ehozBm$7DHA.2832@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have multiple SQL data backup sets.
> Is it possible to take all backup sets and restore them to one Database?
>
> Thanks in advanced,
> Asaf
>

Multiple backup sets restore?

Hi,
I have multiple SQL data backup sets.
Is it possible to take all backup sets and restore them to one Database?
Thanks in advanced,
AsafCan you be a bit more specific? Did you do like a db backup and then a
number of log backups and now want to restore them all? Or did you stripe
the backup?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Asaf" <asafgo@.hotmail.com> wrote in message
news:ehozBm$7DHA.2832@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have multiple SQL data backup sets.
> Is it possible to take all backup sets and restore them to one Database?
>
> Thanks in advanced,
> Asaf
>

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