2

I have a question regarding the transaction log backups in sql server 2008. I am currently taking full backups once a week (Sunday) and transaction log backups daily. I put full backup in folder1 on Sunday and then on Monday I also put the 1st transaction log backup in the same folder. On tuesday, before I take the 2nd transaction log backup I move the first transaction log backup from folder1 an put it into folder2 and then I take the 2nd transaction log backup and put it in the folder1. Same thing on Wed, Thurs and so on. Basicaly in folder1 I always have the latest full backup and the latest transaction log backup while the other transaction log backups are in folder2. My questions is, when sql server is about to take, lets say 4th (Thursday) transaction log backup, does it look for the previous transac log backups (1st, 2nd, and 3rd) so that this new backup will only include the transactions from the last backup or it has some other way of knowing whether there are other transac log backups. Basically, I am asking this because all my transaction log backups seem to be about the same size and I thought that their size will depend on the amount of transactions since the last transaction log backup.

Example: If you have a, lets say, full backup and then you take a transac log backup and this transac log backup is lets say 200 MB and now you immediatelly take another transac log backup, this last transac log backup should be considerably smaller than the first one because no or almost no transaction occured between these two backups, right? At least, that's what I've been assuming. What happens in my case is that this second backup is pretty much the same size as the first one and I am wondering if the reason for that is because I moved the first transac log backup to a different folder so now sql server thinks that all I have is just a full backup and it then gets all the transactions that happened since the full backup and puts it in the 2nd transac log backup.

Can anyone please explain if my assumptions are right? Thanks...

user44127
  • 131
  • 1
  • 4

5 Answers5

3

Chris and joeqwerty, Thank you guys for your answers. I understand the difference between differential and log backup but I guess I didn't express myself clearly. I always thought that the log backups contain all the transactions since the last log backup (or full backup in case of the first log backup) and you just confirmed that so I guess I was right there. What threw me off is that I took a log backup this morning and then I had to take another (unplanned) log backup this afternoon and these 2 turned to be about the same size even though there shouldn't have been too much activity on the db (though I might be wrong about this). So basically, I was afraid that by moving the old log backup files to a different folder I am 'forcing' the SQL Server to grab all the transactions since the full backup (and essentially behave like a differential backup because that's what the 1st log backup is anyway) for each new log backup. So, I guess as long as SQL server tracks this info internally and I can move the files around I'm good. Thanks again...

user44127
  • 131
  • 1
  • 4
2

I think you're confusing transaction log backups and differentials.

A SQL log backup is the changes since the last log backup. To do a restore you'd need the full backup plus the full chain of log backups otherwise you can't restore to a point in time. SQL tracks everything internally so it doesn't matter if you move files around. The key thing is needing the full backup plus the unbroken chain of ALL log backups.

A diff backup on the other hand will include all changes since the last full backup irrespective of any other diff or log backups that you've made in the interim.

If you're traffic is consistent then your log backups could work out at approximately the same size since they're averaging the same volume of transactions in each.

It goes without saying... but I'll say it anyway... test your backups frequently by trying some test restores - it's the best way to find out if your strategy has any holes in it.

Chris W
  • 2,670
  • 1
  • 23
  • 32
  • +1. SQL backups aren't dependent on any previous chain of log file backups. SQL restores on the other hand are dependent, depending on your recovery needs. – joeqwerty May 25 '10 at 22:38
2

SQL server doesn't pay any attention to the backup files on the hard drive when it performs any later backups. It keeps track of all that stuff internally inside the database log file.

Yes (generally speaking) the size of the transaction log backup will depend somewhat on how much activity took place since the last log backup, but there may be a minimum size.

But weekly fulls and daily log backups is kind of an unusual plan. Much more common would be weekly fulls and daily differentials, plus hourly log backups if you truly need point-in-time recovery. +1 on spencer's link.

BradC
  • 2,220
  • 4
  • 27
  • 36
  • Thanks for your answer BradC. It helps to know that SQL server keeps track of that stuff internally and that moving around the backup files would not affect the backup chain. I also noticed that a lot of people do weekly full, daily diff, and hourly tlog but we don't really have a need to do an hourly tlog backup. I could probably do diff instead of tlog backups on a daily basis but tlog backups will be faster. Is there any drawback of my approach besides the fact that is an unusual one? Thanks... – user44127 May 25 '10 at 23:24
  • 1
    Whether a differential backup or a transaction log backup takes longer will depend on how much activity you have in the database. But if you ever have to do a RESTORE, restoring from the full then a (single) differential backup will be VASTLY faster and easier than restoring a week's worth of tran logs. And that's why we do backups anyway, right? So we can restore when needed? Anyway, the fundamental question is: do you need point-in-time recovery, or not? (this allows you to restore to a specific time in the day, instead of just to when the backup occurred). – BradC May 26 '10 at 12:48
  • 1
    (finishing my thought) If you don't need point-in-time recovery, then the database should be in Simple mode, not Full, and you shouldn't be doing transaction log backups at all. If you DO need point-in-time recovery, then having daily differentials STILL helps you, because to recover you can restore the full, then the latest differential, then all transaction logs up to the point that a problem occurred. But the tran log backups don't have to be hourly. Could be every 2 or 4 or 6 hours. The fundamental question you haven't answered is: how much data/time can your company afford to lose? – BradC May 26 '10 at 12:52
  • Thanks again BradC. Basically, the way it is right now we are fine w/ weekly full backups and daily diff or tlog backups. The more I think about it the more it seems that full and diff would probably make more sense in our case especially if we can have that in the simple recovery mode. My only concern is how is sql server able to do full and diff backups in simple recovery mode if the transactions get truncated on checkpoints. If I take a full backup now and a diff backup in 20 days how will sql server be able to get all the transactions that happened in those 20 day if they got truncated? – user44127 May 27 '10 at 04:04
  • 1
    "Truncated on Checkpoint" just means that in Simple mode, the log file doesn't bother keeping track of any transactions that have already been committed to the database. So a differential backup just looks at all changes *to the database itself* since the last full backup. It doesn't care about the log at all. – BradC May 27 '10 at 15:40
2

A transaction log backup only truncates the inactive portion of the log. The definition of 'inactive' is what a lot of people seem to have trouble with. The inactive point is the area of the transaction log behind the oldest of: the most recent checkpoint or the start of the oldest non-committed transaction. For most databases the most recent checkpoint defines the inactive portion of the log.

So, when does the DB checkpoint? Only when it has to. That is, when you shut it down, the log becomes too full, or the active portion of the log gets large enough that the DB thinks it would take longer than recoveryinterval to play it forward in case of a failure. The idea behind the infrequent checkpoints is that the DB knows that writing to disk is expensive, so it tries to keep as much in memory as it can.

So, in your case, when you do two tlog backups back to back without either a natural checkpoint occurring or you forcing a checkpoint (with the checkpoint command) you're backing up the same chunk of transaction log twice because it's still considered active.

Donnie
  • 121
  • 3
  • Thanks for your thorough explanation Donnie, it helps a lot. I usually don't do back to back tlog backups but I took one this morning as it was scheduled and then I had to take another one this pm so I thought that this 2nd one would be considerably smaller than the one in the morning as I didn't think there was much activity during the day but maybe I was wrong about that. Thanks again.. – user44127 May 25 '10 at 23:19
1

(This question is probably more suitable to serverfault.com rather than stackoverflow.)

A backup of the transaction log ONLY backs up transactions NOT ALREADY backed up by a previous transaction log backup.

That is, for a recovery, you will need the full backup AND each and every transaction log backup since the start of the full backup.

HTH

I think you may have transaction log backups confused with differential backups.

http://technet.microsoft.com/en-us/library/aa337534%28SQL.100%29.aspx

spencer7593
  • 111
  • 2