August 9, 2006 at 9:48 am
Hi everyone,
I am trying to figure out the best way to implement a transaction log backup rotation. Everybody says to do the full/differential backup nightly and log backups hourly, but details are hard to come by. My question is:
Is there such a thing as commonly accepted best practices recommendation?
if not, there are some decisions to make:
1. to append t-log backups to one file, or to use 24 files
2. if append, how to overwrite day old backups? Some have suggested that RETAINDAYS, or EXPIREDATE would do the trick, but they are really there just to do the opposite - prevent media from being overwritten
3. is there any need to to worry about coordinating t-log and regular DB backups? for example, if T-logs are backed up every hour on the hour, and nightly full backup runs from 1:00AM to 2:30AM would I be able to do a restore with the full backup and t-logs from 3:00AM on? and what happens if something needs to be restored from 2:00AM? do we take yesterdays backup and add all the t-logs up to 2:00AM?
Thanks to everyone for your input.
Mordechai
August 9, 2006 at 3:50 pm
Best practices are determined by the business unit (or users or owners of data). They decide how much data they are willing to lose at any one given time. Another limited factor is how much money you have for storage and backup solutions.
1) I've never appended t-log backups, as then I'd have to transfer around one large file rather than 24 smaller ones. One file wouldn't be a good practice if you only needed 1/2 the t-logs for the restore and you had to transfer the file to another server in a disaster situation. Again, how much time do the users require the database to be recovered.
2) Again, I wouldn't append t-logs
3) The answer is no on the 2:00 am restore. That's the biggest problem with disaster recovery planning in 2000. They supposedly addressed this in 2005 and allow a t-log backup during a full backup. However, in 2000, you can only recover to before the full if the database goes down during the full backup. THe answer is yes on the 3:00 am restore. Also, you don't have to have the Full that occurred between 1:00 to 2:30 am to restore to 3:00 am. You can take the full from the previous night's and apply all the logs between that full and the 3:00 am t-log backup. That will work as well. The full backup doesn't start the process all over again. Techinically you can take last year's full and recover to today, if you have all the t-logs that were taken between then and now.
August 9, 2006 at 3:53 pm
3) Forgot one thing. If you have the t-log backup before the FULL at 1:00 am and the t-log after the FULL at 3:00 am then yes, you can restore to 2:00 am using the FULL from the night before and using all the t-logs from then until the 3:00 am t-log dump. The scenario I addressed in my previous 3) is only if the database goes down/suspect/caput during the FULL backup.
August 11, 2006 at 2:15 pm
Ok, I hear what you're saying about not appending the t-logs. However with any backup strategy, there's only one question that will drive the entire process. Its driven by the "business".
"How much risk is acceptable?"
That's it. Its just that simple. There are a few "best practices", but realistically in this situation the best practice is one that meets the business needs. The business must grapple with the fact that data loss is inevitable in a recovery. They have to decide how much data loss is acceptable. Lower tolerances for lost data usually mean higher costs.
For example, lets just say you don't append the t-log backup and you create several hundred t-log files between diffs. Scenario - one of the t-log files is corrupt. You're sunk. You won't be able to recover any more t-logs. If you have one t-log file, there is less of a chance that the one file will be corrupt. However there is the trade off of having a rather large file to move around. Plus, you don't completely eliminate the fact that the single t-log file can be corrupted itself, meaning you lose all t-logs.
You can really go around in circles trying to plug all the holes in the dam. However the reality is that data loss in a recovery is always a threat to your data. You can even implement the most expensive "off site" solutions and still have situations where you lose considerable amounts of data and uptime.
The point is, education is the key. Once you educate the business side, you can draft a comprehensive SLA for the SQL Server data services while setting reasonable expectations. Of course, data loss is never acceptable to the business folks. However given reality its something they will have to be able to deal with and possibly minimize wherever possible. The only factor is how much they are willing to spend to do it. And there are diminishing returns with additional dollars spent.
Anyhow, just to sum up. Its not a decison for a DBA. Its an agreement between you (the DBA) and the business folks in the terms of an SLA. And for your sake I reccomend getting it in writing.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply