One more question on best-practice for Backup Plan

I am not a SQL Server DBA, but have a temporary need to play that role. I am
new to the Maintenance Plan Wizard, but have thus far implemented the
following:
1. Daily System Full Backup Job (Selected System Databases)
1.1. Check Database Integrity
1.2. Rebuild Index
1.3. Update Statistics
1.4. Clean Up History
1.5. Backup Database (Full)
1.6. Runs daily at 6:15AM
2. Weekly User Full Backup Job (Selected All User Databases)
2.1. Check Database Integrity
2.2. Rebuild Index
3.3. Update Statistics
3.4. Clean Up History
3.5. Backup Database (Full)
3.6. Runs every Saturday at 6:15AM
3. Daily User Differential Backup Job (Selected All User Databases)
3.1. Check Database Integrity
3.2. Backup Database (Differential)
3.3. Runs Sun thru Fri at 6:15AM
4. Hourly User Log Backup Job (Selected All User Databases)
4.1. Backup Database (Transaction Log)
4.1. Runs Hourly Sun thru Sat at 6:00AM
This doesn't seem quite right to me. I am mostly confused by the additional
maintance options in the Wizard (Check Database Integrity, Shrink Database,
Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
Execute SQL Server Agent Job). I am wondering if these should be included in
the full, differential, log backup jobs as I have done above or if they
should be run seperatly as one or more standalone jobs on some frequency. Is
there any info on best practices and/or examples on how to handle these
other job tasks?It depends on the size of your databases and your backup window.
For example if you have several databases of only a few gigabytes each one
perhaps you can do full backups daily. If your databases are hundreds of
gigabytes the backup procedure you show make more sense.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:
> I am not a SQL Server DBA, but have a temporary need to play that role. I am
> new to the Maintenance Plan Wizard, but have thus far implemented the
> following:
> 1. Daily System Full Backup Job (Selected System Databases)
> 1.1. Check Database Integrity
> 1.2. Rebuild Index
> 1.3. Update Statistics
> 1.4. Clean Up History
> 1.5. Backup Database (Full)
> 1.6. Runs daily at 6:15AM
> 2. Weekly User Full Backup Job (Selected All User Databases)
> 2.1. Check Database Integrity
> 2.2. Rebuild Index
> 3.3. Update Statistics
> 3.4. Clean Up History
> 3.5. Backup Database (Full)
> 3.6. Runs every Saturday at 6:15AM
> 3. Daily User Differential Backup Job (Selected All User Databases)
> 3.1. Check Database Integrity
> 3.2. Backup Database (Differential)
> 3.3. Runs Sun thru Fri at 6:15AM
> 4. Hourly User Log Backup Job (Selected All User Databases)
> 4.1. Backup Database (Transaction Log)
> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> This doesn't seem quite right to me. I am mostly confused by the additional
> maintance options in the Wizard (Check Database Integrity, Shrink Database,
> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
> Execute SQL Server Agent Job). I am wondering if these should be included in
> the full, differential, log backup jobs as I have done above or if they
> should be run seperatly as one or more standalone jobs on some frequency. Is
> there any info on best practices and/or examples on how to handle these
> other job tasks?
>
>|||What I am not sure of is the other options, such as index maintenance, etc.
Should they be run as standalone jobs... and how do I select which to run.
For example, reorganize index and rebuild index do not seem to be candidates
to run together in a single job and I have heard that it is probably ot a
good idea to run Shrink Database at all.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
> It depends on the size of your databases and your backup window.
> For example if you have several databases of only a few gigabytes each one
> perhaps you can do full backups daily. If your databases are hundreds of
> gigabytes the backup procedure you show make more sense.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
>> I am not a SQL Server DBA, but have a temporary need to play that role. I
>> am
>> new to the Maintenance Plan Wizard, but have thus far implemented the
>> following:
>> 1. Daily System Full Backup Job (Selected System Databases)
>> 1.1. Check Database Integrity
>> 1.2. Rebuild Index
>> 1.3. Update Statistics
>> 1.4. Clean Up History
>> 1.5. Backup Database (Full)
>> 1.6. Runs daily at 6:15AM
>> 2. Weekly User Full Backup Job (Selected All User Databases)
>> 2.1. Check Database Integrity
>> 2.2. Rebuild Index
>> 3.3. Update Statistics
>> 3.4. Clean Up History
>> 3.5. Backup Database (Full)
>> 3.6. Runs every Saturday at 6:15AM
>> 3. Daily User Differential Backup Job (Selected All User Databases)
>> 3.1. Check Database Integrity
>> 3.2. Backup Database (Differential)
>> 3.3. Runs Sun thru Fri at 6:15AM
>> 4. Hourly User Log Backup Job (Selected All User Databases)
>> 4.1. Backup Database (Transaction Log)
>> 4.1. Runs Hourly Sun thru Sat at 6:00AM
>> This doesn't seem quite right to me. I am mostly confused by the
>> additional
>> maintance options in the Wizard (Check Database Integrity, Shrink
>> Database,
>> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
>> Execute SQL Server Agent Job). I am wondering if these should be included
>> in
>> the full, differential, log backup jobs as I have done above or if they
>> should be run seperatly as one or more standalone jobs on some frequency.
>> Is
>> there any info on best practices and/or examples on how to handle these
>> other job tasks?
>>|||Everything is "it depends".
Do not shrink your databases in a job and avoid it even manually.
Do not run reorganize index and rebuild index together. Most important, do
you really need to reindex? Do you have a fragmentation problem? How big are
your databases? How big are your tables? Spent some time learning about dbcc
showcontig.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:
> What I am not sure of is the other options, such as index maintenance, etc.
> Should they be run as standalone jobs... and how do I select which to run.
> For example, reorganize index and rebuild index do not seem to be candidates
> to run together in a single job and I have heard that it is probably ot a
> good idea to run Shrink Database at all.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
> >
> > It depends on the size of your databases and your backup window.
> >
> > For example if you have several databases of only a few gigabytes each one
> > perhaps you can do full backups daily. If your databases are hundreds of
> > gigabytes the backup procedure you show make more sense.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Bill Fuller" wrote:
> >
> >> I am not a SQL Server DBA, but have a temporary need to play that role. I
> >> am
> >> new to the Maintenance Plan Wizard, but have thus far implemented the
> >> following:
> >>
> >> 1. Daily System Full Backup Job (Selected System Databases)
> >> 1.1. Check Database Integrity
> >> 1.2. Rebuild Index
> >> 1.3. Update Statistics
> >> 1.4. Clean Up History
> >> 1.5. Backup Database (Full)
> >> 1.6. Runs daily at 6:15AM
> >>
> >> 2. Weekly User Full Backup Job (Selected All User Databases)
> >> 2.1. Check Database Integrity
> >> 2.2. Rebuild Index
> >> 3.3. Update Statistics
> >> 3.4. Clean Up History
> >> 3.5. Backup Database (Full)
> >> 3.6. Runs every Saturday at 6:15AM
> >>
> >> 3. Daily User Differential Backup Job (Selected All User Databases)
> >> 3.1. Check Database Integrity
> >> 3.2. Backup Database (Differential)
> >> 3.3. Runs Sun thru Fri at 6:15AM
> >>
> >> 4. Hourly User Log Backup Job (Selected All User Databases)
> >> 4.1. Backup Database (Transaction Log)
> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> >>
> >> This doesn't seem quite right to me. I am mostly confused by the
> >> additional
> >> maintance options in the Wizard (Check Database Integrity, Shrink
> >> Database,
> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
> >> Execute SQL Server Agent Job). I am wondering if these should be included
> >> in
> >> the full, differential, log backup jobs as I have done above or if they
> >> should be run seperatly as one or more standalone jobs on some frequency.
> >> Is
> >> there any info on best practices and/or examples on how to handle these
> >> other job tasks?
> >>
> >>
> >>
>
>|||Ok, tweaked it some more and the plans are looking cleaner.
Now I am wondering about History Cleanup... does that clean all outdated
backup files from system and user?
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
> Everything is "it depends".
> Do not shrink your databases in a job and avoid it even manually.
> Do not run reorganize index and rebuild index together. Most important, do
> you really need to reindex? Do you have a fragmentation problem? How big
> are
> your databases? How big are your tables? Spent some time learning about
> dbcc
> showcontig.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
>> What I am not sure of is the other options, such as index maintenance,
>> etc.
>> Should they be run as standalone jobs... and how do I select which to
>> run.
>> For example, reorganize index and rebuild index do not seem to be
>> candidates
>> to run together in a single job and I have heard that it is probably ot a
>> good idea to run Shrink Database at all.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
>> >
>> > It depends on the size of your databases and your backup window.
>> >
>> > For example if you have several databases of only a few gigabytes each
>> > one
>> > perhaps you can do full backups daily. If your databases are hundreds
>> > of
>> > gigabytes the backup procedure you show make more sense.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Bill Fuller" wrote:
>> >
>> >> I am not a SQL Server DBA, but have a temporary need to play that
>> >> role. I
>> >> am
>> >> new to the Maintenance Plan Wizard, but have thus far implemented the
>> >> following:
>> >>
>> >> 1. Daily System Full Backup Job (Selected System Databases)
>> >> 1.1. Check Database Integrity
>> >> 1.2. Rebuild Index
>> >> 1.3. Update Statistics
>> >> 1.4. Clean Up History
>> >> 1.5. Backup Database (Full)
>> >> 1.6. Runs daily at 6:15AM
>> >>
>> >> 2. Weekly User Full Backup Job (Selected All User Databases)
>> >> 2.1. Check Database Integrity
>> >> 2.2. Rebuild Index
>> >> 3.3. Update Statistics
>> >> 3.4. Clean Up History
>> >> 3.5. Backup Database (Full)
>> >> 3.6. Runs every Saturday at 6:15AM
>> >>
>> >> 3. Daily User Differential Backup Job (Selected All User Databases)
>> >> 3.1. Check Database Integrity
>> >> 3.2. Backup Database (Differential)
>> >> 3.3. Runs Sun thru Fri at 6:15AM
>> >>
>> >> 4. Hourly User Log Backup Job (Selected All User Databases)
>> >> 4.1. Backup Database (Transaction Log)
>> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
>> >>
>> >> This doesn't seem quite right to me. I am mostly confused by the
>> >> additional
>> >> maintance options in the Wizard (Check Database Integrity, Shrink
>> >> Database,
>> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History,
>> >> and
>> >> Execute SQL Server Agent Job). I am wondering if these should be
>> >> included
>> >> in
>> >> the full, differential, log backup jobs as I have done above or if
>> >> they
>> >> should be run seperatly as one or more standalone jobs on some
>> >> frequency.
>> >> Is
>> >> there any info on best practices and/or examples on how to handle
>> >> these
>> >> other job tasks?
>> >>
>> >>
>> >>
>>|||This cleans up information from the msdb database like backup and restore
history, jobs history and maintenace plan history.
It is up to you how much data you want to keep.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:
> Ok, tweaked it some more and the plans are looking cleaner.
> Now I am wondering about History Cleanup... does that clean all outdated
> backup files from system and user?
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
> >
> > Everything is "it depends".
> >
> > Do not shrink your databases in a job and avoid it even manually.
> >
> > Do not run reorganize index and rebuild index together. Most important, do
> > you really need to reindex? Do you have a fragmentation problem? How big
> > are
> > your databases? How big are your tables? Spent some time learning about
> > dbcc
> > showcontig.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Bill Fuller" wrote:
> >
> >> What I am not sure of is the other options, such as index maintenance,
> >> etc.
> >> Should they be run as standalone jobs... and how do I select which to
> >> run.
> >> For example, reorganize index and rebuild index do not seem to be
> >> candidates
> >> to run together in a single job and I have heard that it is probably ot a
> >> good idea to run Shrink Database at all.
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
> >> >
> >> > It depends on the size of your databases and your backup window.
> >> >
> >> > For example if you have several databases of only a few gigabytes each
> >> > one
> >> > perhaps you can do full backups daily. If your databases are hundreds
> >> > of
> >> > gigabytes the backup procedure you show make more sense.
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> >
> >> >
> >> >
> >> >
> >> > "Bill Fuller" wrote:
> >> >
> >> >> I am not a SQL Server DBA, but have a temporary need to play that
> >> >> role. I
> >> >> am
> >> >> new to the Maintenance Plan Wizard, but have thus far implemented the
> >> >> following:
> >> >>
> >> >> 1. Daily System Full Backup Job (Selected System Databases)
> >> >> 1.1. Check Database Integrity
> >> >> 1.2. Rebuild Index
> >> >> 1.3. Update Statistics
> >> >> 1.4. Clean Up History
> >> >> 1.5. Backup Database (Full)
> >> >> 1.6. Runs daily at 6:15AM
> >> >>
> >> >> 2. Weekly User Full Backup Job (Selected All User Databases)
> >> >> 2.1. Check Database Integrity
> >> >> 2.2. Rebuild Index
> >> >> 3.3. Update Statistics
> >> >> 3.4. Clean Up History
> >> >> 3.5. Backup Database (Full)
> >> >> 3.6. Runs every Saturday at 6:15AM
> >> >>
> >> >> 3. Daily User Differential Backup Job (Selected All User Databases)
> >> >> 3.1. Check Database Integrity
> >> >> 3.2. Backup Database (Differential)
> >> >> 3.3. Runs Sun thru Fri at 6:15AM
> >> >>
> >> >> 4. Hourly User Log Backup Job (Selected All User Databases)
> >> >> 4.1. Backup Database (Transaction Log)
> >> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> >> >>
> >> >> This doesn't seem quite right to me. I am mostly confused by the
> >> >> additional
> >> >> maintance options in the Wizard (Check Database Integrity, Shrink
> >> >> Database,
> >> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History,
> >> >> and
> >> >> Execute SQL Server Agent Job). I am wondering if these should be
> >> >> included
> >> >> in
> >> >> the full, differential, log backup jobs as I have done above or if
> >> >> they
> >> >> should be run seperatly as one or more standalone jobs on some
> >> >> frequency.
> >> >> Is
> >> >> there any info on best practices and/or examples on how to handle
> >> >> these
> >> >> other job tasks?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It seems rather pointless to keep history data on backup files that have
long since been purged. Around here, we keep our customers backups on disk
that are required to erstore from the last full backup, and everythingis on
tape for 31 days. Anything older than that is gone and purged in our jobs
using Sp_delete_backuphistory
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:D532E1BC-E544-46F1-BBBD-FF789A528EBC@.microsoft.com...
> This cleans up information from the msdb database like backup and restore
> history, jobs history and maintenace plan history.
> It is up to you how much data you want to keep.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
>> Ok, tweaked it some more and the plans are looking cleaner.
>> Now I am wondering about History Cleanup... does that clean all outdated
>> backup files from system and user?
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
>> >
>> > Everything is "it depends".
>> >
>> > Do not shrink your databases in a job and avoid it even manually.
>> >
>> > Do not run reorganize index and rebuild index together. Most important,
>> > do
>> > you really need to reindex? Do you have a fragmentation problem? How
>> > big
>> > are
>> > your databases? How big are your tables? Spent some time learning about
>> > dbcc
>> > showcontig.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Bill Fuller" wrote:
>> >
>> >> What I am not sure of is the other options, such as index maintenance,
>> >> etc.
>> >> Should they be run as standalone jobs... and how do I select which to
>> >> run.
>> >> For example, reorganize index and rebuild index do not seem to be
>> >> candidates
>> >> to run together in a single job and I have heard that it is probably
>> >> ot a
>> >> good idea to run Shrink Database at all.
>> >>
>> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> >> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
>> >> >
>> >> > It depends on the size of your databases and your backup window.
>> >> >
>> >> > For example if you have several databases of only a few gigabytes
>> >> > each
>> >> > one
>> >> > perhaps you can do full backups daily. If your databases are
>> >> > hundreds
>> >> > of
>> >> > gigabytes the backup procedure you show make more sense.
>> >> >
>> >> > Hope this helps,
>> >> >
>> >> > Ben Nevarez
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Bill Fuller" wrote:
>> >> >
>> >> >> I am not a SQL Server DBA, but have a temporary need to play that
>> >> >> role. I
>> >> >> am
>> >> >> new to the Maintenance Plan Wizard, but have thus far implemented
>> >> >> the
>> >> >> following:
>> >> >>
>> >> >> 1. Daily System Full Backup Job (Selected System Databases)
>> >> >> 1.1. Check Database Integrity
>> >> >> 1.2. Rebuild Index
>> >> >> 1.3. Update Statistics
>> >> >> 1.4. Clean Up History
>> >> >> 1.5. Backup Database (Full)
>> >> >> 1.6. Runs daily at 6:15AM
>> >> >>
>> >> >> 2. Weekly User Full Backup Job (Selected All User Databases)
>> >> >> 2.1. Check Database Integrity
>> >> >> 2.2. Rebuild Index
>> >> >> 3.3. Update Statistics
>> >> >> 3.4. Clean Up History
>> >> >> 3.5. Backup Database (Full)
>> >> >> 3.6. Runs every Saturday at 6:15AM
>> >> >>
>> >> >> 3. Daily User Differential Backup Job (Selected All User Databases)
>> >> >> 3.1. Check Database Integrity
>> >> >> 3.2. Backup Database (Differential)
>> >> >> 3.3. Runs Sun thru Fri at 6:15AM
>> >> >>
>> >> >> 4. Hourly User Log Backup Job (Selected All User Databases)
>> >> >> 4.1. Backup Database (Transaction Log)
>> >> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
>> >> >>
>> >> >> This doesn't seem quite right to me. I am mostly confused by the
>> >> >> additional
>> >> >> maintance options in the Wizard (Check Database Integrity, Shrink
>> >> >> Database,
>> >> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up
>> >> >> History,
>> >> >> and
>> >> >> Execute SQL Server Agent Job). I am wondering if these should be
>> >> >> included
>> >> >> in
>> >> >> the full, differential, log backup jobs as I have done above or if
>> >> >> they
>> >> >> should be run seperatly as one or more standalone jobs on some
>> >> >> frequency.
>> >> >> Is
>> >> >> there any info on best practices and/or examples on how to handle
>> >> >> these
>> >> >> other job tasks?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>