February 5, 2010 at 8:13 am
I want to set up what I think is a typical backup scenario on a lot of SQL Servers:
Weekly Full backups, daily Differential backups, and hourly log file backups.
So I schedule 3 different Maintenance Plans, one for each of these, so I can set the timing just right so they don't overlap. So for example, weekly Full is Monday at 12:10 am, daily Differentials are T, W, Th, F, Sat, Sun at 12:10 am -- but not on Mondays, etc.
Here's the problem: I have one monster database that is 120gb on a pretty slow server. The weekly Full backup takes more than an hour.
So what happens to the Hourly Log file backup that kicks off at 1:10 am?
Is SQL smart enough to skip this? Because it really can't do it at this time, right? It is still in the process of doing a Full backup, so a Log file backup doesn't even make sense, right??
Or does SQL mindlessly try to do a Log file backup right during a Full backup? That would really mess things up, wouldn't it? What if it did a log TRUNCATE right during a Full backup -- that seems really ugly! What does SQL do?
Or does SQL queue up the Log file backup, and wait for the Full to finish, then kick off the Log file backup immediately thereafter? (I have both SQL 2000 and 2005 servers I have to do this on!) So just imagine the Full takes 2.5 hours to finish, then there would be two log file backups that kick off, one right after the other. The first arguably would have all the transactions from 12:10 until 2:40 am, but the next one that kicked off would have a few seconds worth of transactions, then another one kicks off 30 minutes later. So ugly! (but I guess that would work -- if that's what SQL actually does!)
And another thing -- there is no way to predict how long a Full backup might take -- lets say it normally takes 30 minutes, but one Sunday night (Monday morning) you get totally slammed (it happens) -- and this is when it is most important for your backups to be working!! -- and now it takes, who knows, maybe FOUR hours to do the Full backup. Its not like you can just schedule the "amount of time" to do a Full backup -- because that changes -- you need to be ready for the worst case scenario, not just the best case!!
I'm sure this has been thought through before -- any help or insights much appreciated!!
Thank you in advance!!
February 5, 2010 at 8:37 am
subs99 (2/5/2010)
Or does SQL mindlessly try to do a Log file backup right during a Full backup? That would really mess things up, wouldn't it? What if it did a log TRUNCATE right during a Full backup -- that seems really ugly!
This is how it works on SQL 2005 and 2008. The log backup will run while the full backup is running, the only difference from a log backup at any other time is that the log backup won't truncate the log, because those log records are needed by the full backup.
Or does SQL queue up the Log file backup, and wait for the Full to finish, then kick off the Log file backup immediately thereafter?
This is how it works on SQL 2000. A log backup will be 'blocked' (for lack of a better word) until the full has completed and it will kick off straight after. Once that one's finished, the next one will run at the next scheduled time.
So just imagine the Full takes 2.5 hours to finish, then there would be two log file backups that kick off, one right after the other. The first arguably would have all the transactions from 12:10 until 2:40 am, but the next one that kicked off would have a few seconds worth of transactions
Not quite. A job can only be running once, it can't have executions queued up. So if the full starts at 12:10 and finishes at 2:40, then at 1:10 the tran log backup job starts but is blocked by the full backup (SQL 2000 only) and so waits. At 2:10 the job is scheduled to run again, but it's already running so nothing happens. At 2:40 the full finishes so the log backup can now run and it does. Next scheduled execution of that job is 3:10 and at that time the job will start and do the next backup.
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
February 5, 2010 at 8:42 am
One thing you could do, if you see a problem after reading what Gail just posted, is have the full backup job disable the log backup before starting the full backup, and re-enabling the log backup after the full backup. Be sure to re-enable the log backup if the full backup fails.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 5, 2010 at 10:23 am
GilaMonster (2/5/2010)
subs99 (2/5/2010)
Or does SQL mindlessly try to do a Log file backup right during a Full backup? That would really mess things up, wouldn't it? What if it did a log TRUNCATE right during a Full backup -- that seems really ugly!This is how it works on SQL 2005 and 2008. The log backup will run while the full backup is running, the only difference from a log backup at any other time is that the log backup won't truncate the log, because those log records are needed by the full backup.
Hey thank you so much for the detailed response!
Question, though: if the Full starts at 12:10 and takes say 1.5 hrs, and the Log backup starts at 1:10 takes a couple seconds and does NOT truncate the log, what happens when you do a restore? Oh, I think I understand: the Full will "end" at 12:10, when it started, right? Then the Log backup includes transactions from 12:10 and goes through 1:10 -- I think that's right. If that's the case, then they are simply sequential, like they should be. No big deal, and doing a restore would therefore just be like normal as well. If that's actually accurate, then cool.
GilaMonster (2/5/2010)
subs99 (2/5/2010)Or does SQL queue up the Log file backup, and wait for the Full to finish, then kick off the Log file backup immediately thereafter?
This is how it works on SQL 2000. A log backup will be 'blocked' (for lack of a better word) until the full has completed and it will kick off straight after. Once that one's finished, the next one will run at the next scheduled time.
Cool.
GilaMonster (2/5/2010)
subs99 (2/5/2010)
So just imagine the Full takes 2.5 hours to finish, then there would be two log file backups that kick off, one right after the other. The first arguably would have all the transactions from 12:10 until 2:40 am, but the next one that kicked off would have a few seconds worth of transactions
Not quite. A job can only be running once, it can't have executions queued up. So if the full starts at 12:10 and finishes at 2:40, then at 1:10 the tran log backup job starts but is blocked by the full backup (SQL 2000 only) and so waits. At 2:10 the job is scheduled to run again, but it's already running so nothing happens. At 2:40 the full finishes so the log backup can now run and it does. Next scheduled execution of that job is 3:10 and at that time the job will start and do the next backup.
I get it, and thanks again!
So I can see how that "works" - but when doing a restore, it will appear as if one of the Log file backups is missing, right? Because there actually will be one file less than normal (or a few missing, if the Full took a really long time), but all transactions are recorded in that one later Log file backup that covers a longer time-period. Wow, I could see how that could really trip you up, many days later, when putting it back together during a restore, it will seem like something screwed up since there is one file 'missing', and you would presumably have to dig through logs to see that it took a long time to do that Full backup, or just try the restore and I think SQL will tell you if the Log file is out of sequence, right? Well, assuming that works, it still may cause a slight aneurysm (at 3 in the morning on Saturday after you've been drinking -- which is inevitably when you have to restore the mission-critical database!!) 😀
February 5, 2010 at 10:33 am
Alvin Ramard (2/5/2010)
One thing you could do, if you see a problem after reading what Gail just posted, is have the full backup job disable the log backup before starting the full backup, and re-enabling the log backup after the full backup. Be sure to re-enable the log backup if the full backup fails.
I can see conceptually how that would work, but how would you actually make that happen? Are you thinking SQL 2000 or 2005/2008?
Honestly I have much more experience with 2000 Maintenance Plans, and they seem to be quite limited in what they can do. Digging into the SQL 2000 Jobs I also don't see how one could temporarily turn off another (Full turn off Log)... am I missing something?
Would this be pretty easy to do in a SQL 2005/2008 SSIS package??
Thanx!
February 5, 2010 at 10:33 am
The only t-log backups you need for a restore are those created AFTER the full backup completed.
February 5, 2010 at 10:43 am
Lynn Pettis (2/5/2010)
The only t-log backups you need for a restore are those created AFTER the full backup completed.
Hmmm.. I'm not sure that's accurate... I thought that if a Full backup starts at 12:10 am, then the last transaction it will have is 12:10 am or earlier. So the last transaction in a backup is the last transaction when the backup started, not when it finished.
This seems like it would make sense, since if you are the DBA for Amazon.com you might have a zillion transactions 24x7, and therefore when you are just about "done" with a backup, there are many transaction still happening, so you would have to grab those too, but by the time you're done with them, there are more, you have to add them in, and so on... and you would never ever actually finish a backup. Right?
That's why I think the actual millisecond you kick off a backup is the last transaction that may be included in this backup.
And so if that backup actually takes 2 hours, and you are backing up to another machine, but then just as the backup finishes the server blows up (and you have your Log file on that same server) you would forever lose those 2 hours of data.
So if your Full backup starts at 12:10 am, and completes at 2:10 am, you need to restore the Log file backups that go back to 12:10 am (when the Full backup started), not 2:10 am (when the Full backup completed).
February 5, 2010 at 10:50 am
subs99 (2/5/2010)
Lynn Pettis (2/5/2010)
The only t-log backups you need for a restore are those created AFTER the full backup completed.Hmmm.. I'm not sure that's accurate... I thought that if a Full backup starts at 12:10 am, then the last transaction it will have is 12:10 am or earlier. So the last transaction in a backup is the last transaction when the backup started, not when it finished.
This seems like it would make sense, since if you are the DBA for Amazon.com you might have a zillion transactions 24x7, and therefore when you are just about "done" with a backup, there are many transaction still happening, so you would have to grab those too, but by the time you're done with them, there are more, you have to add them in, and so on... and you would never ever actually finish a backup. Right?
That's why I think the actual millisecond you kick off a backup is the last transaction that may be included in this backup.
And so if that backup actually takes 2 hours, and you are backing up to another machine, but then just as the backup finishes the server blows up (and you have your Log file on that same server) you would forever lose those 2 hours of data.
So if your Full backup starts at 12:10 am, and completes at 2:10 am, you need to restore the Log file backups that go back to 12:10 am (when the Full backup started), not 2:10 am (when the Full backup completed).
No, the last thing that the FULL backup does is to backup enough of the transaction log to provide you with a consistant database when restored. I have had full backups running when transaction log backups have also run and completed. When restoring from the those full backups, the only transaction log backups I needed were the ones create AFTER the full backup completed, not the ones that ran during the full backup.
February 5, 2010 at 11:33 am
Lynn Pettis (2/5/2010)
subs99 (2/5/2010)
Lynn Pettis (2/5/2010)
The only t-log backups you need for a restore are those created AFTER the full backup completed.Hmmm.. I'm not sure that's accurate... I thought that if a Full backup starts at 12:10 am, then the last transaction it will have is 12:10 am or earlier. So the last transaction in a backup is the last transaction when the backup started, not when it finished.
This seems like it would make sense, since if you are the DBA for Amazon.com you might have a zillion transactions 24x7, and therefore when you are just about "done" with a backup, there are many transaction still happening, so you would have to grab those too, but by the time you're done with them, there are more, you have to add them in, and so on... and you would never ever actually finish a backup. Right?
That's why I think the actual millisecond you kick off a backup is the last transaction that may be included in this backup.
And so if that backup actually takes 2 hours, and you are backing up to another machine, but then just as the backup finishes the server blows up (and you have your Log file on that same server) you would forever lose those 2 hours of data.
So if your Full backup starts at 12:10 am, and completes at 2:10 am, you need to restore the Log file backups that go back to 12:10 am (when the Full backup started), not 2:10 am (when the Full backup completed).
No, the last thing that the FULL backup does is to backup enough of the transaction log to provide you with a consistant database when restored. I have had full backups running when transaction log backups have also run and completed. When restoring from the those full backups, the only transaction log backups I needed were the ones create AFTER the full backup completed, not the ones that ran during the full backup.
You're pretty close to right. 😛
From MSDN:
A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished.
...but more importantly...
You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. When the database is recovered, uncommitted transactions are rolled back. The restored database matches the state of the original database when the restored backup finished, minus any uncommitted transactions.
http://msdn.microsoft.com/en-us/library/ms186289.aspx
So say you are the Amazon DBA, and 10,000 people hit the Place Order button a few milliseconds before your Full backup "finishes", then all those transactions are mid-transaction (not committed). I believe they are included in the Full backup (technically, the Log backup part of the Full backup!), but when the Full backup is restored, each of those transactions is rolled back, leaving you with a database that is 1. In a consistent state, and 2. Some amount of time before this Full backup actually finished.
Example: If one of those transactions takes 0.5 seconds (500 milliseconds) to complete, and this transaction began at 499 milliseconds before the Full backup finished, then this transaction will be rolled back during the restore, therefore the restored database is now at (Full Backup End Time) - (499 milliseconds).
That still makes me wonder: Does SQL do the main data part of a Full backup, then at that instant when it is done, it then kicks off the "Log file backup part of the Full backup" -- and then what if that takes a really "long" time -- nothing is instantaneous, right??. Then what??
I guess, in general, there must always be arbitrary times that are set, and there could always be any number of transactions that are mid-transaction (not committed), and therefore all restores must just be to the end of the backup, then rolled back in time to the latest consistent state (whenever that is), and that's about all that can be done. So if you restore from a backup that "finished" at exactly 1:12:000am, you will probably get a database that was in a consistent state at 1:11:??? am -- sometime earlier than when the backup actually "finished". Probably the word "finished" doesn't actually make sense in terms of SQL restores -- it is always to the closest and latest consistent state.
And anyway, back to my original question: it seems clear you do NOT need to do any log file backups during a Full backup.
Unless (!) ...now this is a really unlikely scenario, but what the heck: You are doing a Full backup, and even though you know it will take a very very long time, you turn off the overlapping hourly Log file backups, since you know the Full backup will include at the end the Log file backup. But during this Full backup, your server explodes. Now you lose all that data from the last Log file backup before you started the Full backup -- this could be hours. And if you are doing an hourly Log file backup, it is presumably because 1 hour is your tolerance for data loss (SLA, if you will). If your tolerance for data loss is longer, then do longer Log file backups! ...if it is only 5 minutes, shame on you for doing hourly Log backups!
So anyway, the only way to avoid this (unlikely, but that's the job, right?!?!) scenario, is to do those interim Log file backups during the Full backup (to another machine). So you could still do a restore using the latest actual separate Log file backup (done during the Full backup), together with all the Log file backups from before the Full backup started, all the way back to the previous Full backup.
Dude, oh ya, and this would ONLY work in SQL Server 2005/2008, not 2000, according to one Mr. GilaMonster, since in 2000 it will NOT do these Log file backups during a Full backup. I wonder if Microsoft actually thought of this scenario when they make this a possibility in 2005/2008?? Dude, that would be cool. 😎
February 5, 2010 at 4:50 pm
Unless (!) ...now this is a really unlikely scenario, but what the heck: You are doing a Full backup, and even though you know it will take a very very long time, you turn off the overlapping hourly Log file backups, since you know the Full backup will include at the end the Log file backup. But during this Full backup, your server explodes. Now you lose all that data from the last Log file backup before you started the Full backup -- this could be hours. And if you are doing an hourly Log file backup, it is presumably because 1 hour is your tolerance for data loss (SLA, if you will). If your tolerance for data loss is longer, then do longer Log file backups! ...if it is only 5 minutes, shame on you for doing hourly Log backups!
Here is where the improvements in 2005/2008 have decreased the potential loss of data. In 2000, if your system crashed during your backup in such a way that you no longer have access to the log file (for tail log backup) you could end up losing all of the changes that have occurred since the last transaction log backup was run.
Now, we don't have to worry about that (as much 😉 ), because we can go back to the previous backup and apply all of the transaction log backups up to the point in time of the crash. This is why it is so important to make sure you maintain your log chain - because without that you have no way to recover.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2010 at 11:29 am
That's an important fact to remember. The backup includes changes up until the backup finishes, not starts.
March 16, 2010 at 11:29 am
I haven't forgotten this thread, and I am STILL working on this (I will know everything there is to know about SQL backups very soon!)
One other really funky thing I ran into:
I set my backups to do:
weekly Full (Monday 12:05am)
daily Differential (every day but Monday at 12:05am)
hourly Log (every hour at :05 except 12:05am)
But if it is a Tuesday like it is now, you MUST kick off a FULL backup now, right? Otherwise all the Differentials and Log backups are useless.
So I did a Full Backup at about 1:00 in the afternoon.
Big database, takes ~20 minutes to finish the backup.
Uh oh... a Log backup happened at 1:05pm, right on schedule.
That file is junk, right?
SQL is still performing a Full backup, how could a Log backup be correct if there isn't a complete Full backup in existence yet?
So I will just delete that file. But imagine it is x days/weeks/months/years-even later, and someone has "all" the backup files, "starting" with this Full, then they would try to restore this "first" Log backup file, and what?? I don't know, and I think I can just toss it out, but if I weren't sitting here babysitting it, what would happen???
Strange.
oh, I've changes screen names, jpSQLDude is so much better. 😛
March 16, 2010 at 11:40 am
jpSQLDude (3/16/2010)
I haven't forgotten this thread, and I am STILL working on this (I will know everything there is to know about SQL backups very soon!)One other really funky thing I ran into:
I set my backups to do:
weekly Full (Monday 12:05am)
daily Differential (every day but Monday at 12:05am)
hourly Log (every hour at :05 except 12:05am)
But if it is a Tuesday like it is now, you MUST kick off a FULL backup now, right? Otherwise all the Differentials and Log backups are useless.
So I did a Full Backup at about 1:00 in the afternoon.
Big database, takes ~20 minutes to finish the backup.
Uh oh... a Log backup happened at 1:05pm, right on schedule.
That file is junk, right?
SQL is still performing a Full backup, how could a Log backup be correct if there isn't a complete Full backup in existence yet?
So I will just delete that file. But imagine it is x days/weeks/months/years-even later, and someone has "all" the backup files, "starting" with this Full, then they would try to restore this "first" Log backup file, and what?? I don't know, and I think I can just toss it out, but if I weren't sitting here babysitting it, what would happen???
Strange.
oh, I've changes screen names, jpSQLDude is so much better. 😛
"....
But if it is a Tuesday like it is now, you MUST kick off a FULL backup now, right? Otherwise all the Differentials and Log backups are useless.
..."
How do you come to this conclusion ? You've got a Full from Monday, Diff from Tuesday mornig, and logs since then. Why did you run another full on Tuesday ? Not needed, but not a problem either.
I'm not sure if your 1:05 tran log backup is junk. Perhaps it would be useful to restore to point-in-time before the full backup completed.
March 16, 2010 at 11:42 am
jpSQLDude (3/16/2010)
I haven't forgotten this thread, and I am STILL working on this (I will know everything there is to know about SQL backups very soon!)One other really funky thing I ran into:
I set my backups to do:
weekly Full (Monday 12:05am)
daily Differential (every day but Monday at 12:05am)
hourly Log (every hour at :05 except 12:05am)
But if it is a Tuesday like it is now, you MUST kick off a FULL backup now, right? Otherwise all the Differentials and Log backups are useless.
So I did a Full Backup at about 1:00 in the afternoon.
Big database, takes ~20 minutes to finish the backup.
Uh oh... a Log backup happened at 1:05pm, right on schedule.
That file is junk, right?
SQL is still performing a Full backup, how could a Log backup be correct if there isn't a complete Full backup in existence yet?
So I will just delete that file. But imagine it is x days/weeks/months/years-even later, and someone has "all" the backup files, "starting" with this Full, then they would try to restore this "first" Log backup file, and what?? I don't know, and I think I can just toss it out, but if I weren't sitting here babysitting it, what would happen???
Strange.
oh, I've changes screen names, jpSQLDude is so much better. 😛
Don't delete that t-log backup file created at 1:05. It is fine. If you should need to restore that database to 1:03, you will need it plus the previous t-log backups and full backup (not the one you started at 1:00 today).
The full backup you are creating does not break the log chain from the previous full backup. It does start a new base for differential backups.
March 16, 2010 at 12:25 pm
homebrew01 (3/16/2010)
How do you come to this conclusion ? You've got a Full from Monday, Diff from Tuesday mornig, and logs since then. Why did you run another full on Tuesday ? Not needed, but not a problem either.I'm not sure if your 1:05 tran log backup is junk. Perhaps it would be useful to restore to point-in-time before the full backup completed.
Sorry -- my bad -- not clear on my part!
This is a BRAND NEW backup. There is no old backup at all -- no previous Full backup, nothing.
So there is no Full from the previous Monday. This is a fresh start.
So this full is the first Full ever for this database, therefore this Log has no Full that it is ummm... related to? Based on??
So that is why I think this Log backup file is junk.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply