cleaning up backups

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

uSlackr - 29 Feb 2008 21:55 GMT

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

Reply to this Message

Tibor Karaszi - 01 Mar 2008 09:59 GMT

> 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

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>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
[quoted text clipped - 20 lines]
>
> \\Greg

Reply to this Message

johnnyho034@yahoo.com - 03 Mar 2008 19:26 GMT

On Mar 1, 3:59 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:

> > Perhaps you can refer me to a better book?
>
[quoted text clipped - 58 lines]
>
> - 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

Reply to this Message