cleaning up backups

I recently took responsibility for a sharepoint 2003 server & corresponding
SQL 2000 server. The backups are working through a maintenance plan, but
I'd like to implement a richer plan and am having difficulties developing
the best approach. understand I've not done much care & feeding of SQL in
my life. (lots of other infrastructure work, through). I have read the
technet articles on sql 2000 backup & restore and the pocket consultant:
database backup & recovery. Perhaps you can refer me to a better book?
I'd like to implement a weekly full db backup,
nightly differentials
nightly transaction log backups.
All of this would be done to a file server
When I tried to do this in the maintenance plan wizard, it didn't offer the
differential option. So then I looked at creating a backup job directly and
there the option no (apparent) to limit the number of backup files kept in
the file system. Am I missing something?
Then on the transaction logs, I see when I configure the job through the
backup tool, it allows me to select "remove inactive entries from
transaction log". This option is not available when I create the back
through a maintenance plan. Do I need to do this and why can't I do it
through both tools.
Thanks in advance.
\\Greg> Perhaps you can refer me to a better book?
SQL Server Books Online (the documentation that comes with the product)? It is very good.
> I'd like to implement a weekly full db backup,
> nightly differentials
> nightly transaction log backups.
Hmm, why only nightly log backups? For above, I'd expect something like hourly or every 10 minutes
or so.
> When I tried to do this in the maintenance plan wizard, it didn't offer the differential option.
Correct.
> So then I looked at creating a backup job directly and there the option no (apparent) to limit the
> number of backup files kept in the file system. Am I missing something?
Nope, you are correct again. You would have to roll your own. Google is your friend, and you are
likely to find code "out there" for this. For instance
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too
hard to make 2000) and do not include code to remove old backup files.
> Then on the transaction logs, I see when I configure the job through the backup tool, it allows me
> to select "remove inactive entries from transaction log". This option is not available when I
> create the back through a maintenance plan. Do I need to do this and why can't I do it through
> both tools.
The GUI is confusing and badly designed. Un-checking this option will add the NO_TRUNCATE option to
your backup command. This option is badly named and should have been named
ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *not* something you want to
specify for other than extreme cases. Leaving this checked in the GUI is same as not specifying the
option, which is same as how Maint Plan does it. Some more reading:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"uSlackr" <gmartin@.gmartin.org> wrote in message news:%23jJDy2xeIHA.4164@.TK2MSFTNGP05.phx.gbl...
>I recently took responsibility for a sharepoint 2003 server & corresponding SQL 2000 server. The
>backups are working through a maintenance plan, but I'd like to implement a richer plan and am
>having difficulties developing the best approach. understand I've not done much care & feeding of
>SQL in my life. (lots of other infrastructure work, through). I have read the technet articles on
>sql 2000 backup & restore and the pocket consultant: database backup & recovery. Perhaps you can
>refer me to a better book?
> I'd like to implement a weekly full db backup,
> nightly differentials
> nightly transaction log backups.
> All of this would be done to a file server
> When I tried to do this in the maintenance plan wizard, it didn't offer the differential option.
> So then I looked at creating a backup job directly and there the option no (apparent) to limit the
> number of backup files kept in the file system. Am I missing something?
> Then on the transaction logs, I see when I configure the job through the backup tool, it allows me
> to select "remove inactive entries from transaction log". This option is not available when I
> create the back through a maintenance plan. Do I need to do this and why can't I do it through
> both tools.
> Thanks in advance.
> \\Greg|||On Mar 1, 3:59=A0am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Perhaps you can refer me to a better book?
> SQL Server Books Online (the documentation that comes with the product)? I=t is very good.
> > I'd like to implement a weekly full dbbackup,
> > nightly differentials
> > nightly transaction log backups.
> Hmm, why only nightly log backups? For above, I'd expect something like ho=urly or every 10 minutes
> or so.
> > When I tried to do this in the maintenance plan wizard, it didn't offer =the differential option.
> Correct.
> > So then I looked at creating abackupjob directly and there the option no= (apparent) to limit the
> > number ofbackupfiles kept in the file system. =A0Am I missing something?=
> Nope, you are correct again. You would have to roll your own. Google is yo=ur friend, and you are
> likely to find code "out there" for this. For instancehttp://www.karaszi.c=
om/SQLServer/util_backup_script_like_MP.asp, which is 2005 (shouldn't be too=
> hard to make 2000) and do not include code to remove oldbackupfiles.
> > Then on the transaction logs, I see when I configure the job through the=backuptool, it allows me
> > to select "remove inactive entries from transaction log". =A0This option= is not available when I
> > create the back through a maintenance plan. =A0Do I need to do this and =why can't I do it through
> > both tools.
> The GUI is confusing and badly designed. Un-checking this option will add =the NO_TRUNCATE option to
> yourbackupcommand. This option is badly named and should have been named
> ALLOW_LOG_BACKUP_OF_A_CORRUPT_DATABASE. As you imagine, this option is *no=t* something you want to
> specify for other than extreme cases. Leaving this checked in the GUI is s=ame as not specifying the
> option, which is same as how Maint Plan does it. Some more reading:http://=
www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>
> "uSlackr" <gmar...@.gmartin.org> wrote in messagenews:%23jJDy2xeIHA.4164@.TK=2MSFTNGP05.phx.gbl...
> >I recently took responsibility for asharepoint2003 server & corresponding= SQL 2000 server. =A0The
> >backups are working through a maintenance plan, but I'd like to implement= a richer plan and am
> >having difficulties developing the best approach. =A0understand I've not =done much care & feeding of
> >SQL in my life. (lots of other infrastructure work, through). I have read= the technet articles on
> >sql 2000backup& restore and the pocket consultant: databasebackup& recove=ry. =A0Perhaps you can
> >refer me to a better book?
> > I'd like to implement a weekly full dbbackup,
> > nightly differentials
> > nightly transaction log backups.
> > All of this would be done to a file server
> > When I tried to do this in the maintenance plan wizard, it didn't offer =the differential option.
> > So then I looked at creating abackupjob directly and there the option no= (apparent) to limit the
> > number ofbackupfiles kept in the file system. =A0Am I missing something?=
> > Then on the transaction logs, I see when I configure the job through the=backuptool, it allows me
> > to select "remove inactive entries from transaction log". =A0This option= is not available when I
> > create the back through a maintenance plan. =A0Do I need to do this and =why can't I do it through
> > both tools.
> > Thanks in advance.
> > \\Greg- Hide quoted text -
> - Show quoted text -
Greg,
Why read books on how to use the native tools?
Go to our website www.avepoint.com and see if our backup solutions are
sufficient.
Give me a call or drop me an email if you have any questions.
Thanks,
John Hohenadel
312-558-1694
john.hohenadel@.avepoint.com