I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??
If someone can help, it would be great.....
2  4315 
On 28 Oct 2003 15:57:10 -0800, ja**********@pe risher.com.au (trotter)
wrote: I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Well, optimization means "changing things", and when you change
things, disasters are possible. Full backups come before
optimizations. Integrity *checks*, that is, looking to see if
everything is okay, should probably come before backup (you don't want
to back up bad data).
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??
Here's the deal on that.
The transaction log is a listing of what has been done (or is being
done) to the database. Once you've done a full backup, the transaction
log is no longer necessary to restore your database to the state it
was in as of the completion of the backup. So, if you backup the
database at 3:00am, the transaction log will do you no good in
restoring the database to the point that it was at 3:00am.
Okay... so why backup the transaction log at all?
Well, first, it backs up a lot faster than the database (though it's
slower to restore)... on a busy system, you might not be able to run
multiple full backups during the day, but you can probably run many
transaction log backups during the day.
Second, unless you tell it not to, the backup procedure will delete
all transactions that are already written to the database; this
reduces the amount of your log file that's in use, and keeps it from
growing.
So, once you've backed up the database, you don't need to backup the
transaction log to help restore the database, but you might want to
anyway, to delete the inactive portions.
If you're using the full database recovery model, you'll probably want
to take multiple transaction log backups during the day, both for
database recovery, and to keep the transaction log from filling up.
(Yes, it can autogrow, but it's better if it doesn't.)
--
Everything I needed to know in life I learned in Kindergarten. Like:
Once you pull the pin on Mr. Hand Grenade, he is no longer your friend.
On 28 Oct 2003 15:57:10 -0800, ja**********@pe risher.com.au (trotter)
wrote: I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Well, optimization means "changing things", and when you change
things, disasters are possible. Full backups come before
optimizations. Integrity *checks*, that is, looking to see if
everything is okay, should probably come before backup (you don't want
to back up bad data).
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??
Here's the deal on that.
The transaction log is a listing of what has been done (or is being
done) to the database. Once you've done a full backup, the transaction
log is no longer necessary to restore your database to the state it
was in as of the completion of the backup. So, if you backup the
database at 3:00am, the transaction log will do you no good in
restoring the database to the point that it was at 3:00am.
Okay... so why backup the transaction log at all?
Well, first, it backs up a lot faster than the database (though it's
slower to restore)... on a busy system, you might not be able to run
multiple full backups during the day, but you can probably run many
transaction log backups during the day.
Second, unless you tell it not to, the backup procedure will delete
all transactions that are already written to the database; this
reduces the amount of your log file that's in use, and keeps it from
growing.
So, once you've backed up the database, you don't need to backup the
transaction log to help restore the database, but you might want to
anyway, to delete the inactive portions.
If you're using the full database recovery model, you'll probably want
to take multiple transaction log backups during the day, both for
database recovery, and to keep the transaction log from filling up.
(Yes, it can autogrow, but it's better if it doesn't.)
--
Everything I needed to know in life I learned in Kindergarten. Like:
Once you pull the pin on Mr. Hand Grenade, he is no longer your friend. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: teedilo |
last post by:
We have an application with a SQL Server 2000 back end that is fairly
database intensive -- lots of fairly frequent queries, inserts, updates
-- the gamut. The application does not make use of performance hogs
like cursors, but I know there are lots of ways the application could
be made more efficient database-wise. The server code is...
|
by: Matt |
last post by:
I run SQL Server 2000 and use thier database maintenance plans to
backup my databases. My questions is what happens if a change is being
made to a database table while a backup is running? Should I be locking
the databases before the backup begins?
Scenario:
1) Database Plan begins backup at 7:00 PM.
2) At 7:01 PM a web user updates...
|
by: sam |
last post by:
I have to import data into a empty database, that has many tables.
some tables have to be inserted first than others due to the foreign
keys.
How do I find out the order of the tables that I have to insert data
into?
Thanks in advance!
Sam
|
by: Larry Maturo |
last post by:
I noticed that in SQL Server Management Studio, under Management, there is a
Legacy folder with maintenace plans created by adding entries to the system
tables dbo.sysmaintplan_* tables (i.e. all the maintenace plans created in
sql server 2000). Where are the new maintenance plans kept? Is there any
API interface to it?
-- Larry Maturo
|
by: Beowulf |
last post by:
I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.
Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an...
| | |
by: hallpa1 |
last post by:
Hi all,
I posted messages before about trying to purge many records (about 35%)
of a 200Gig database. The responses gave me a lot to think about,
especially regarding the indexes. But due to the short windows that I
have to run in, manipulating the indexes is not an option.
But this leads to another question. When all of this is done, we...
|
by: Icemokka |
last post by:
Hi,
We have a SQL-cluster with over 6300 databases, most of them pretty
small in size and usage.
For this reason all these databases are on auto-close ( Otherwise SQL
would need many many gigabytes ).
We've recently upgraded to SQL 2005 because of the Management Studio,
because the EM is unusable on a SQL-Server with lots of databases.
|
by: bosko19 |
last post by:
We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:
Incorrect syntax near '.'. (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is...
|
by: David Greenberg |
last post by:
Hi
We use Sql 2000.
When defining a database maintenance plan one of the options is to
optimize the database, reorganize data and index pages, remove unused
pages etc.
Is there a command to do this manualy (or in a job) and not by usung a
DBM plan ?
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
| | |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
