Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.sqlmonster.com
Hi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK
|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas

"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.c om...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!