February 6, 2006 at 11:49 am
This is SQL Server 2000. I changed our production database from Simple to Bulk Logged mode and started transaction log backups on a bihourly basis (every two hours if that's not a word) during production times.
Right now the maintenance plan deletes transaction log backups more than 2 days old. However, I do two full backups of the database itself nightly; one automatically done by the SQL Server and one I burn to disc.
Is it really necessary to keep transaction log backups from before the last full database backup? If I backup nightly, wouldn't that include all the transactions that would have been in the log that day?
February 6, 2006 at 12:16 pm
Yes, and no. Generally speaking you don't need to keep transaction log backups that were made prior to the last GOOD full backup. However, some mistakes aren't caught in a timely manner and I have faced a situation where we needed to rollback to a point in time prior to the last full backup. Yes, it was ugly, but if we had deleted the log backups we would have lost even more work.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 7, 2006 at 9:33 am
I agree and have walked down dcpetersen's path as well. Besides, if you are backing your files up to tape after they are on disk, the only reason to keep more than the current day's transaciton logs online on disk would be to hasten a special restore. At our site we keep 3 days of full backups online and 2 days of transaction logs. The 2 days transaction logs are tyhere primarily in case the nightly backup to tape fails for some reason.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 7, 2006 at 11:34 am
Right now, I keep 3 days full, 2 days transaction. A separate backup is made to burn to CD each night (I have to zip the file because it is >4GB in size and Big Speed Zipper works nicely with that)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply