473,394 Members | 1,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 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 11394

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: jason | last post by:
Hi Ray...a while ago you explained an elegant solution to enable me to CREATE and EDIT existing tables and queries inside my online access 2000 database.... could you provide refresher links on...
0
by: rjp | last post by:
Hi, I am using a SQLServer 7 SP4 version. I have scheduled one job with the SQLServer agent which backup the log of the databases every day. The command is : "backup log base to with noinit"...
1
by: R Camarda | last post by:
Help, I have a database that has a data file of 2GB and a log file of 31GB. In enterprise manager, when I choose shrink it says there is 30GB of unused space. When I shrink the database, it does...
2
by: Robert Kruk | last post by:
Hello, Recently I've tried to restore backup made on SQL Server 6.5. Unfortunately, the only version of SQL Server I currently possess is SQL Server 2000. I have access only to that backup,...
3
by: Mark Bennett | last post by:
Hello, Embarrassing as it is, I'm stumped by a (I'd imagine) fairly elementary CSS problem. Consider the code below. We have a table with three columns, two of which are set to a width of...
4
by: Dr John Stockton | last post by:
Throughout the world in general, ISO 8601 Week Numbers are used, in which weeks are numbered 01 upwards and Week 01 contains the first Thursday of the Gregorian Calendar Year. There are,...
3
by: | last post by:
I have been researching articles on google on how to create a simple RSS feed that sucks <title><blurb><link><date> out of a sql server 2000 database via an aspx page. I know it has to be pushed...
3
by: alwinkotiya | last post by:
Hi all, If I set recovery model SIMPLE then truncation of log files will be automated , but I will lose database changes made since the last backup was taken. Is there any way to automate...
2
by: John | last post by:
I have an Access 2000 form that has a Hyperlink that opens an Excel 2000 worksheet located in the same network folder as the Access MDB. The Hyperlink works great. When users close the Excel...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.