December 12, 2011 at 6:59 am
Hi,
We have a SQL Server 2008 database which currently has a once a day full backup and a once a day transaction log backup. We have been told by the company that installed it that it will need to be capable of point in time recovery.
I have a few questions that I'd like people to give opinions on please:
1. Am I right in thinking that it will not really be possible to do point in time recovery unless the transaction log backups run far more frequently than once a day? More like once an hour, or half hour etc, assuming they want to be able to recover to around an hour ago?
2. The database backup job is set to append rather than overwrite. We have a DR system which takes all the SQL Server backups off to a different server at 6am, so if we back up the db once a day before 6am, I think we can set it to overwrite rather than append, therefore saving space. However I want to check that there is no harm in doing this given that we may want to recover to any of those backups that have been taken off. I assume we just put them back on the server if needed and SQL Server still knows the correct chain of events and the order in which to restore them?
3. The system will be importing huge numbers of emails at implementation (and at set times after that). Can I change the recovery mode to bulk load during those times, or will that break the chain for recovery?
4. Can I shrink the transaction log? I anticipate this growing huge, but if I shrink it will I delete chunks of it that would be needed for recovery? Is there any other wasy o fkeeping it small? Will regular log backups be enough to keep it under control (in my experience no!).
Sorry this is so long, please let me know your opinions.
Thanks,
P
December 12, 2011 at 7:09 am
A good start for these questions is the 'Stairways' series on Transaction Log Management:
December 12, 2011 at 7:13 am
Another One.. 😉 Introduction to Backup and Restore Strategies in SQL Server
December 12, 2011 at 7:26 am
Paula-196779 (12/12/2011)
1. Am I right in thinking that it will not really be possible to do point in time recovery unless the transaction log backups run far more frequently than once a day? More like once an hour, or half hour etc, assuming they want to be able to recover to around an hour ago?
Oh, your setup will allow point-in-time recovery, providing the log isn't damaged. If it is, then you've lost up to a day of data. It's far from an ideal setup though.
The interval between log backups should be the maximum amount of data that you are willing to lose in a disaster. If you're willing to lose a day, you may as well use simple recovery. If the maximum amount of data that can be lost in a disaster is 1 hour, then your log backups need to be at least every hour.
2. The database backup job is set to append rather than overwrite. We have a DR system which takes all the SQL Server backups off to a different server at 6am, so if we back up the db once a day before 6am, I think we can set it to overwrite rather than append, therefore saving space. However I want to check that there is no harm in doing this given that we may want to recover to any of those backups that have been taken off. I assume we just put them back on the server if needed and SQL Server still knows the correct chain of events and the order in which to restore them?
Personally I strongly recommend backups each go to their own file, preferably with the date and time stamped into the file name. It makes recovery much easier if you can look at your backup files and know just from the names which one is from when and which you need to restore.
SQL only knows the correct chain of backups if the msdb backup history tables are intact. If not, then you need to figure it out.
3. The system will be importing huge numbers of emails at implementation (and at set times after that). Can I change the recovery mode to bulk load during those times, or will that break the chain for recovery?
You can set to bulk-logged. Depending how you're importing the emails that may not make any difference. Only certain operations are minimally logged, it's not everything. If won't break the log chain.
4. Can I shrink the transaction log? I anticipate this growing huge, but if I shrink it will I delete chunks of it that would be needed for recovery? Is there any other wasy o fkeeping it small? Will regular log backups be enough to keep it under control (in my experience no!).
Don't shrink it. It'll just regrow and growing a log is IO-intensive and will slow the DB down. If the log is too big, reduce the interval between your log backups, that's the main thing that affects log size (along with large transactions like index rebuilds)
I covered a lot of these in my articles on recovery models and log management.
Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2011 at 7:45 am
I tend to agree with Gilamonster's advice. The one thing I'd add about appending is that if something happens to the backup file, then you lose not only one, but multiple backups. I recommend separate files as a risk reduction move as well.
Also, space is always an issue, but depending on one backup file is a risk. What if there's a corrupt file, or someone deletes it? I'd recommend you keep two backup files, even if you copy the older one off to another drive. Note that to recover forward, you need all log backups since that earliest full backup that you keep.
December 12, 2011 at 8:15 am
Thanks for all this, great advice. The stairways info is good too.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply