March 16, 2010 at 12:28 pm
Lynn Pettis (3/16/2010)
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.
Sorry, again my bad -- this is a brand new, first-time-ever backup of a new database, so there is no other Full in existence.
So again, I think I need to delete this Log backup file.
In 2005 and 2008 -- AFAIK from earlier in this thread -- when the Full completes, it actually encapsulates the Log backup within itself, at the end of the Full backup. So if I start at 1:00pm, and it takes 20 minutes to complete, then that Full backup is a backup of all transactions up to and including 1:20pm -- and that Log backup file that happened at 1:05pm is totally useless, I believe.
March 16, 2010 at 12:33 pm
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!
As others have said, the FULL contains enough of the Transaction Log to restore the DB to a consistent state and those rolled back transactions that weren't committed end up right back in the Transaction Log. You don't lose them.
In the case of a TLog backup running during a FULL backup and truncating the log file, SQL Server only truncates the Transaction Log of committed transactions. Uncommitted transactions do not get truncated. The only way they would disappear is if you had some major data corruption that violated basic ACID rules.
Regarding the other questions, have you read about the concept of backup chains?
Essentially what the DB does is keep track of each and every backup that is NOT a "copy only" backup. Every time a FULL backup is done, the server "starts" a new chain with that FULL backup, then tags every Differential and Transaction Log backup in order of them being done with a "link" that chains them back to the very last FULL backup. The new chain does NOT delete the old chain, though. It simply adds itself to the end of the last chain.
So I have a FULL backup on Monday, a Trans Log backup every hour on every day, and Differentials running Tuesday thru Sunday. For two weeks of backups, I have chain 1 which has the first FULL backup, each transaction log backup from the FULL to the first Differential, Tuesday's Differential, etc. It might look like this:
Monday 1 FULL -> Trans1 -> Trans2 -> ...Trans22... -> TuesdayDiff -> Trans25 -> Trans26 -> ...Trans48... -> WednesdayDiff.... etc. -> Monday 2 FULL -> M2Trans1 -> M2Trans2 -> etc...
Essentially, this chain means I have to restore these items in this order precisely with only one exception. Transaction Logs can be skipped during the restore process, so long as I am going from one committed data file backup to another. So I can restore Monday 2's FULL backup and Wednesday 2's Differential without restoring the TLogs in between. However, if I want to restore transactions after Wednesday's Differential but before Thursday's, this is when I need to restore transaction logs between the two up until the point where I have the data I need.
You can also go all the way back to Monday 1 and restore Friday 1's Differential without a problem because of the way the chain works. (Mind you, we're talking FULL Recovery Mode here. SIMPLE doesn't allow Differential or Transaction Log backups.)
So if you turn off Transaction Log backups during your FULL / Differential backups, *It Won't Hurt You*. Not only does the backup cover your backside with it's consistency checks, but as soon as you start Transaction Log backups again, they get marked in the chain in their proper place and nice things happen.
However, if you delete one backup in the middle of the chain, it can cause you problems. Going back to the first example, if you delete Monday 2's FULL backup, you CANNOT start with Monday 1 and go all the way to Wednesday 2's Differential. The backup chain will *require* Monday 2's FULL backup in order to restore your database. Or you can go to Monday 1, restore Sunday 1 Differential and all Transaction Logs up to right before the missing Monday 2. But you'll be missing 3 days worth of data.
And that's how it works. I've either cleared things up, or completely muddied the waters. I hope it's the former. And if anyone takes issue with anything I said, please let me know.
March 16, 2010 at 12:34 pm
jpSQLDude (3/16/2010)
Lynn Pettis (3/16/2010)
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.
Sorry, again my bad -- this is a brand new, first-time-ever backup of a new database, so there is no other Full in existence.
So again, I think I need to delete this Log backup file.
In 2005 and 2008 -- AFAIK from earlier in this thread -- when the Full completes, it actually encapsulates the Log backup within itself, at the end of the Full backup. So if I start at 1:00pm, and it takes 20 minutes to complete, then that Full backup is a backup of all transactions up to and including 1:20pm -- and that Log backup file that happened at 1:05pm is totally useless, I believe.
The full backup copies enough of the transaction log to ensure a consistant database upon restore up to the time of the backup completing. It DOES NOT clear committed transactions. That is only done by transaction log backups.
For more info, read the last article I reference below in my signature block, Managing Transaction Logs.
March 16, 2010 at 12:41 pm
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.
Never question the value of a backup file. ANY backup file.
I haven't tried this myself, so I'm a little shakey on the basics, but essentially, if you take that FULL backup and try to restore to the middle of the FULL backup, SQL Server may ask you to throw that log file into the restore. If you don't have the file... you'll have a major problem.
Is your space that tight that you absolutely need to delete the Transaction Log backup? Is there a reason why that backup has offended you to the point that you must execute (or excommunicate) it on general principals?
In all things with SQL Server, my motto is "better safe that sorry". Keep it around until your next FULL backup. That way, if you need it, it's there. If you don't need it, it really hasn't hurt anything.
March 16, 2010 at 12:54 pm
Brandie Tarvin (3/16/2010)
Is there a reason why that backup has offended you to the point that you must execute (or excommunicate) it on general principals?In all things with SQL Server, my motto is "better safe that sorry". Keep it around until your next FULL backup. That way, if you need it, it's there. If you don't need it, it really hasn't hurt anything.
Yes! There is a reason I think I want to delete this file!
The names of the files are:
MyDatabase_backup_201003161300.bak <-- 1:00pm Full backup
MyDatabase_backup_201003161305.trn <-- 1:05pm Log backup
Now imagine it is tomorrow, the server blows up, I'm laying on a beach in Waikiki never to return 😎 (I can dream) and YOU have my old job (I'm so sorry!!) -- and you have to put this backup back together again.
If you didn't know anything about what happened, it seems obvious what you would do: Restore the Full Backup, the .bak file, with the timestamp built right into the name at 1300 = 1:00pm. That's the first ever backup file for this database.
Then -- here comes the moment of truth -- you would restore the next backup file, the Log file, with the next timestamp built right into the name 1305 = 1:05pm.
What happens?
A log file is "based on" (or whatever you call it) a Full, but there was no Full in existence at 1:05.
In fact the Full ended at 1:20 and encapsulates all transactions until 1:20.
So what-the-heck did this Log use as its "first" transaction?? What happens when you import it, after importing the Full?
Actually, not really a hard test. Guess I could try it. Maybe SQL throws a friendly error. (there's a first time for everything!)
March 16, 2010 at 12:56 pm
SQL Server will NOT allow you to restore files out of order. See my post about backup chains. You can read up about it in BOL too.
There's really nothing to worry about. If SQL Server allows the restore, it's because the log is required. If not, then it's because the log is outside the current backup chain.
March 16, 2010 at 1:13 pm
If the full backup taken at 1:00 PM is the first ever full backup, your t-log backup at 1:05 PM would fail as you wouldn't have a full backup from which to start your t-log chain. If, however, you have a previous full backup from the previous day, then the t-log backup at 1:05 is a part of that t-log chain and would run. It would NOT however, clear any transactions that have been committed because of the active full backup running.
March 16, 2010 at 1:22 pm
Lynn Pettis (3/16/2010)
If the full backup taken at 1:00 PM is the first ever full backup, your t-log backup at 1:05 PM would fail
If you mean Fail as in not be created, it IS in fact created, and appears normal just like any other TRN file.
If you mean Fail as in SQL will throw an error when you try to restore this as the first Log file after you restore the Full, that I'm not sure about, or how clear the error is. Will it be clear from the SQL error that you can merrily skip this file (seemingly breaking the log file chain, which you "never" do) and just restore the next Log backup file. I think that would freak me out!
March 16, 2010 at 1:29 pm
jpSQLDude (3/16/2010)
Lynn Pettis (3/16/2010)
If the full backup taken at 1:00 PM is the first ever full backup, your t-log backup at 1:05 PM would failIf you mean Fail as in not be created, it IS in fact created, and appears normal just like any other TRN file.
Then there is a prior full backup somewhere. If there has never been a backup, attempting to take a log backup will fail. (fail as in give an error and do nothing)
Specifically
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
If you mean Fail as in SQL will throw an error when you try to restore this as the first Log file after you restore the Full, that I'm not sure about, or how clear the error is. Will it be clear from the SQL error that you can merrily skip this file (seemingly breaking the log file chain, which you "never" do) and just restore the next Log backup file. I think that would freak me out!
If you try and restore and out-of-sequence log backup, the restore will fail with a message saying either that the log backup is too early (meaning the records in there aren't needed) or too late (meaning there's a log backup missing)
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
March 16, 2010 at 1:46 pm
When restoring, you need full backup and all the log backups, starting with the first one after the full 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
March 16, 2010 at 1:54 pm
jpSQLDude (3/16/2010)
Lynn Pettis (3/16/2010)
If the full backup taken at 1:00 PM is the first ever full backup, your t-log backup at 1:05 PM would failIf you mean Fail as in not be created, it IS in fact created, and appears normal just like any other TRN file.
If you mean Fail as in SQL will throw an error when you try to restore this as the first Log file after you restore the Full, that I'm not sure about, or how clear the error is. Will it be clear from the SQL error that you can merrily skip this file (seemingly breaking the log file chain, which you "never" do) and just restore the next Log backup file. I think that would freak me out!
Gail has already emphasized this but I feel compelled as well, please read the highlighted part of my previous post, you will notice I said that if the t-log was created that there was a previous full backup:
Lynn Pettis (3/16/2010)
If the full backup taken at 1:00 PM is the first ever full backup, your t-log backup at 1:05 PM would fail as you wouldn't have a full backup from which to start your t-log chain. If, however, you have a previous full backup from the previous day, then the t-log backup at 1:05 is a part of that t-log chain and would run. It would NOT however, clear any transactions that have been committed because of the active full backup running.
March 18, 2010 at 9:11 am
GilaMonster (3/16/2010)
Then there is a prior full backup somewhere. If there has never been a backup, attempting to take a log backup will fail. (fail as in give an error and do nothing)Specifically
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Thank you all very much for your help, and I am sorry I have been confusing, but I have been somewhat confused... I have been thrown into an environment with a hunormous number of SQL Servers, all of which are in different states of disrepair. There is a tape backup system in place (using ARCserve) which has its own SQL Server agent that can pull backups directly out of SQL, obviating the need to have SQL do backups.
But... just imagine every single possible combination of catastrophic errors -- that's what I'm dealing with. For example: some servers have the ARCserve agent doing backups directly (so it truncates the log), AND SQL itself is set to do backups -- how much worse can it get than that?! Some servers SQL does backups and ARCserve is supposed to just pull those files (sometimes it does, and on some servers the permissions are wrong so they do NOT get pulled to tape). And on and on it goes. Nightmare.
So I have rolled my own system: Let SQL backup SQL to files, do NOT use ARCserve to back up SQL directly. I then wrote a VBScript that controls WinRAR, which compresses and encrypts each backup file, and deletes the original when successful. And I wrote a VBScript that controls Robocopy which copies the compressed/encrypted files to a remote file server. SQL does some form of backup every hour, and the VBScripts are scheduled to run first WinRAR, then Robocopy staggered a few minutes so everything flows nicely. All SQL servers back up to one directory on the remote file server, and ARCserve backs up that directory, as a tertiary backup. There's actually a lot more to it than that, but you get the general idea.
So anyway, when I "deploy" this system to a new server, I have to create a number of folders, create SQL Maintenance Plans, create Scheduled Tasks, edit the VBScript files, kick off an initial Full backup to get the process started, etc.
And there's where the problem has been. Some of the systems have been being backed up (someway or another) -- even though those backups are likely useless, or unrecoverable, or on bad tapes, or products of conflicting log truncations, or whatever. SQL just knows somehow or another a Full backup had supposedly happened sometime in the past.
I have turned off the ARCserve agent prior to deploying my new system. I deploy, then I kick off a Full backup (which is then compressed & encrypted and moved to the remote server). I watch while all this happens, and I know the database is now fully backed up, so then I delete all those old likely-useless discombobulated backup files that are filling up many production SAN hard drives.
And then this is where the story starts, where I have a Log backup happening during that initial Full backup, etc, etc... so yes, y'all are correct -- there must have been a Full sometime in the past on the server that I was doing this on, but I have so many servers and I don't know or care what had been happening in the past, that I just ignored, deployed, and restarted -- and I just wanted to ask a short concise question here and not bother you with all the background (which I'm sure few will ever actually read!) but the price one pays for not giving the whole story is, well, just what happened -- I ended up leaving off some important facts. Sorry!
So is there any way to tell SQL, "Yo. Ignore all backups that have ever happened, reset to 0, all backups have been deleted, so this next Full is to be considered the first Full ever done."
Is that possible? And thanks again for all the help (to the few of you who've made it this far!!!)
:hehe:
March 18, 2010 at 9:19 am
jpSQLDude (3/18/2010)
So is there any way to tell SQL, "Yo. Ignore all backups that have ever happened, reset to 0, all backups have been deleted, so this next Full is to be considered the first Full ever done."
Yes. Switch the recovery model from full to simple and back to full.
That breaks the log chain. Log backups will fail from that point on until you run a full backup. NB. No point in time recovery possible until a full backup is run.
And there's where the problem has been. Some of the systems have been being backed up (someway or another) -- even though those backups are likely useless, or unrecoverable, or on bad tapes, or products of conflicting log truncations, or whatever. SQL just knows somehow or another a Full backup had supposedly happened sometime in the past.
No, it knows that a full backup did happen and it knows that the log chain is unbroken (not truncated and discarded) since that full 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
March 18, 2010 at 9:47 am
jpSQLDude (3/18/2010)
Thank you all very much for your help, and I am sorry I have been confusing, but I have been somewhat confused...
Don't be sorry. We appreciate the acknowledgement, but it's not necessary. we very much are here to help, and we enjoy it. Our intention is not to do work for you, but to help you learn and figure out things yourself. Or give you our thoughts /advice on issues.
Best of luck, and please feel free to post back if something confuses you further.
March 18, 2010 at 10:24 am
GilaMonster (3/18/2010)
Yes. Switch the recovery model from full to simple and back to full.That breaks the log chain. Log backups will fail from that point on until you run a full backup. NB. No point in time recovery possible until a full backup is run.
Oh hey! That's so simple, thank you!
Do you think it makes any difference if you switch to Simple, then take a Full backup, then switch to Full Recovery Model; or switch from Full to Simple back to Full, then take a Full backup?
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply