How do I back-up > truncate > shrink > back-up SQL 2000

[NOSPAM]

Hello,

I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).

1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.

As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!

If you know of a well-documented tutorial, please point me in the right direction.

Regards.

[Ray at]

"NOSPAM" <NO****@shaw.ca> wrote in message
news:plqBb.627140$9l5.184547@pd7tw2no...

If you know of a well-documented tutorial, please point me in the right

direction.

Books Online (F1). To encourage more detailed help from other users of
usenet, I suggest you post in plain text, and do not cross-post your message
to half a dozen groups.

Ray at work

[Greg D. Moore \(Strider\)]

"NOSPAM" <NO****@shaw.ca> wrote in message
news:plqBb.627140$9l5.184547@pd7tw2no...
Hello,

I am hoping you can help me with the following problem; I need to process
the following steps every couple of hours in order to keep our Sql 2000
database a small as possible (the transaction log is 5x bigger than the db).

1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.
======================= My post =======================
First, most of the newsgroups you posted to are completely inappropriate for
this.
Second, please don't post in HTML.

Having gotten my gripes out of the way.

First question to ask is, how important is the data?
This sounds like a production database.

However, for a moment, let's assume it's not. In that case, using SQL
Enterprise Manager you can set the recovery mode to SIMPLE RECOVERY and then
just do backups at your leisure. However, you'll have NO transaction log to
speak of and in the event of a failure you will only be able to restore to
the point of your last backup. (Which is essentially where you are at now.)

However, if it's a production database, what you want to do is schedule
transaction log backups as often as business requirements require. In other
words if you can't lose more than 15 minutes worth of data, do a transaction
log backup every 15 minutes.

Once you have transaction backups in place, DO NOT truncate the log. (It
shouldn't grow out of control at this point.)

Once you truncate a transaction log, you lose a lot of your ability to
perform an up to the minute restore.

Also, shrinking the log should be a fairly rare event (it is about the only
way one can end up with disk-level fragmentation of a db file which can
affect performance.)

================================================== ======

As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I
need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish
them!

If you know of a well-documented tutorial, please point me in the right
direction.

Regards.