Restore points available

Hi group,
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.

> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...

> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
>
> unused
> managable
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.

> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
Ray at home
Microsoft ASP MVP