2

I have a database which is currently of size 50 MB. The database is bound to grow by 10 to 15 MB per hour and the data is important. So, I wanted to ask what should be the optimal time interval in which a transaction log backup should be scheduled so that in case there is any data loss then it is as minimal as possible? Thanks in advance.

Jayesh
  • 23
  • 2

1 Answers1

2

The main question to ask yourself is how much data can you afford to lose. That will determine how often you need to backup the log. But there is nothing wrong with running transaction log backups very often, even every minute. This accomplishes 2 things: 1. Provides you with the security that you won't lose data; 2. Improves performance - the less data you backup at any one time the lower your I/O overhead. I recently read a blog post stating that, from a performance point of view, log backups taken every minute greatly reduced I/O pressure. The downside of such frequently run backups is restoring them. As Bogdan rightly mentions in the comments, you should write a script to perform the restores. Depending on how far back in time you need to go you may need to restore a large number of transaction log backups. Doing that manually is very time consuming and prone to errors. There are probably third party tools that help with this as well.

tommyfly
  • 191
  • 5
  • Answer is OK. Just one remark - when you try to resore the DB you have to restore each LOG backup. Manually this can take a lot of time. You must prepare a script in advance to have some sort of automatic restore. Also you can make differential backups during the day. Take into account that the will grow in size until the full backup of the DB - so you have to consider how often to do them during the day. – Bogdan Bogdanov Jul 31 '15 at 07:55
  • Thanks for the response. I did say that the downside was restoring. – tommyfly Jul 31 '15 at 09:09
  • Sorry, @tommyfly. I don't want to say that your answer does not include that. Just want to add details about that - a person who does not have experience can make such error. I made it myself. I also vote up your answer. – Bogdan Bogdanov Jul 31 '15 at 09:30
  • @Bogdan: No problem, just making sure you saw that I mentioned it. I should have been more explicit in my answer. – tommyfly Jul 31 '15 at 09:33
  • Just edit your answer, @tommyfly. – Bogdan Bogdanov Jul 31 '15 at 09:34