Printable View
I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Thanks in advance for your opinions/help.
LN
-----------------------------
Hi.
I believe it depends on the standards.
The logic behind the backup is how much important the data is.
If a loss of one minute data is going to disturb the corporate
cost then the strategy of backup differs accordingly.
Accordingly the Scenerio changes like adding stanby server,
disaster recovery site etc.
I think the following standards help u ...
MAK
For eg: (Some standards from some corporation.....)
Development server - Database backup - every week
(with less transtn) Transaction log - Truncate log on Checkpoint
Testing Server - Database backup - each night
(with less transtn) Transaction log - Truncate log on checkpoint
Testing Server - Database backup - each night
(with less transtn) Transaction log - backup every 6 hrs
Production server - Database backup - each night
(with less trnctn) Tran log backup - every 6 hrs
Production server - Database backup - every 6 hrs
(with medium trnscn) Tran log backup - every 2 hrs
(like banks)
Production server - Database backup - every 6 hrs
(with High trnscn) Tran log backup - every hr
(like banks)
------------
LN at 9/29/99 8:59:00 AM
I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Thanks in advance for your opinions/help.
LN
Mak,
When you say "Database Backup", are you referring to backup the W H O L E database, even though we need to back up a 12 gb database during the day if it is on every 6 hour schedule in your example?
Do you like backing up to the disk first (either backup db or backup the tran log) before going to the tape?
It saves disk space...
Benson
------------
MAK at 9/29/99 10:49:12 AM
-----------------------------
Hi.
I believe it depends on the standards.
The logic behind the backup is how much important the data is.
If a loss of one minute data is going to disturb the corporate
cost then the strategy of backup differs accordingly.
Accordingly the Scenerio changes like adding stanby server,
disaster recovery site etc.
I think the following standards help u ...
MAK
For eg: (Some standards from some corporation.....)
Development server - Database backup - every week
(with less transtn) Transaction log - Truncate log on Checkpoint
Testing Server - Database backup - each night
(with less transtn) Transaction log - Truncate log on checkpoint
Testing Server - Database backup - each night
(with less transtn) Transaction log - backup every 6 hrs
Production server - Database backup - each night
(with less trnctn) Tran log backup - every 6 hrs
Production server - Database backup - every 6 hrs
(with medium trnscn) Tran log backup - every 2 hrs
(like banks)
Production server - Database backup - every 6 hrs
(with High trnscn) Tran log backup - every hr
(like banks)
------------
LN at 9/29/99 8:59:00 AM
I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Thanks in advance for your opinions/help.
LN
Hi Benson.
As LN stated that he is taking backup of the WHOLE database each
and every six - eight hr, it is obvious the database is small and
the transaction is very less. And the example I had given in this
is really an EXAMPLE and cannot be practically applied as it is.
Taking backup depends on
1. No. of Processor u use (for speed)
2. Amount of RAM
3. Amount of Pagefile.sys (virtual memory)
4. No. of transaction
5. How heavy the transaction is.
6. DISK space
7. Corporate standards
8. Size of database
9. Size of tranlog
and many to add.....
The example I gave is like the script used in stage dramas.
Basically they have a script in hand but when they go to the
stage the use the script as a base, they wont do the dialogue
as it is.
If I am wrong in any way I am ready to accept.!!!!!
PLease let me know ......
TO ERROR IS HUMAN
TO ACCEPT THE ERROR IS KNOWLEDGE
TO CORRECT THE ERROR IS WISDOM
MAK
------------
Benson at 9/29/99 8:51:20 PM
Mak,
When you say "Database Backup", are you referring to backup the W H O L E database, even though we need to back up a 12 gb database during the day if it is on every 6 hour schedule in your example?
Do you like backing up to the disk first (either backup db or backup the tran log) before going to the tape?
It saves disk space...
Benson
------------
MAK at 9/29/99 10:49:12 AM
-----------------------------
Hi.
I believe it depends on the standards.
The logic behind the backup is how much important the data is.
If a loss of one minute data is going to disturb the corporate
cost then the strategy of backup differs accordingly.
Accordingly the Scenerio changes like adding stanby server,
disaster recovery site etc.
I think the following standards help u ...
MAK
For eg: (Some standards from some corporation.....)
Development server - Database backup - every week
(with less transtn) Transaction log - Truncate log on Checkpoint
Testing Server - Database backup - each night
(with less transtn) Transaction log - Truncate log on checkpoint
Testing Server - Database backup - each night
(with less transtn) Transaction log - backup every 6 hrs
Production server - Database backup - each night
(with less trnctn) Tran log backup - every 6 hrs
Production server - Database backup - every 6 hrs
(with medium trnscn) Tran log backup - every 2 hrs
(like banks)
Production server - Database backup - every 6 hrs
(with High trnscn) Tran log backup - every hr
(like banks)
------------
LN at 9/29/99 8:59:00 AM
I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Thanks in advance for your opinions/help.
LN