471,853 Members | 1,869 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

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

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.
Jul 20 '05 #1
2 11328

"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
Jul 20 '05 #2

"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.
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mark Bennett | last post: by
4 posts views Thread by Dr John Stockton | last post: by
3 posts views Thread by alwinkotiya | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.