September 16, 2011 at 11:28 pm
GSquared (9/16/2011)
GilaMonster (9/16/2011)
ALZDBA (9/16/2011)
Tom.Thomson (9/16/2011)
I forgot one:5) risks of using backup log to append a log backup to an existing file containing log backups instead of always creating a new backup file for each backup.
Do you have a ref for that ?
In the 10+ years I haven't encountered issues with PIT restores using file level incremental log backups.
Have I been lucky or are you just scaring me off :crazy:
It's more a risk with full or diff backups than logs (since losing one log backup breaks the log chain anyway)
Imagine 4 full backups appended in one file (and no other full backups existing) and the header-portion of the backup file getting damaged. Instead of losing one backup and being able to go back to an earlier one (assuming sufficient retained log backups), you've lost all of them.
Well, if you create a new log backup file after each Full/Diff, and append log backups to that, you're okay, but if you have one log backup file that stretches past your last Full/Diff, and you lose that file, you lose some PIT options you would otherwise have.
Example:
Log backup file created on Sunday
Full backup nightly
Logs backed up into that file every 15 minutes
Lose the log backup file (corrupted header or whatever) on Thursday afternoon
If you find that you need to restore to any PIT from that week other than the start of a nightly full backup, you can't.
Example of breaking it up further:
Full backup nightly
Log backup file created for the first log backup after that
Log backups into that file all day
Lose the log backup file on Thursday afternoon
You still have your Wednesday, Tuesday, et al, log backups, and that can be useful if you find you need to restore to a prior day PIT. For audit purposes for example.
Example of the way Tom does it, one file per log backup:
Full backup nightly
Log backup every 15 minutes, each to its own file
Lose the log backup file on Thursday afternoon at 2:03 PM (14:03 to those who use a clock that makes sense)
You can restore up to the point of the end of the last uncorrupted log file, 13:45 in this case.
Maybe you discover this when your server crashes at 8 PM and you have to restore to PIT, and find that you can only get to the last full backup with either the daily or weekly log backup file being appended to, or to 1:45 PM with the each-backup-in-its-own-file method. You lose less data that way.
Odds of it mattering are small. You have to (a) need a PIT restore, and (b) have a corrupted log backup file, both at the same time. Odds are slim for both to happen in a way that matters. But Murphy hates us, so it's not really paranoia to go for the belt and suspenders version, in my book.
And now I'm discussing SQL in the "Anything that is NOT about SQL!" forum, and in The Thread even!
Boy am I in trouble now!
Oh wait! Steve's AFTT (Away From The Thread), isn't he? Woohoo! My knuckles escaped the ruler! 😛
It's an unlikely edge case with a low risk, but it is something to consider.
Ah, indeed.
Our scenario has a single file singel BU full ackup, if needed single file single BU diffs, and a single file multi BU log backup. All bu-files only handle a single db and a single backup type.
All files are stored locally and copied to safe zone as a last step of the backup job.
We halt incremental log backup jobs at the beginning of the full backup job ( which actually first adds log backups and then handles full backup ) a the end of the full backup job, a job "initial log backup" is scheduled. That job creates log backups "with init" and re-enables the regular incremental log backup job.
All jobs leave their fingerprints so we know in which stage or what caused potential failures.
( fingerprint is just a rename of the job being disabled. prefixing it with who disabled it )
So a backup set will basically be composed of 3 files and PIT supported.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2011 at 4:51 am
The responses to this question struck me as being carelessly wrong, so I jumped in with a contradictory response. Could domeone with a really good understanding of the interaction between indexes and locking have a look and join the discussion, please?
fix typos (finger on two keys at once).
edit again - fix accidentally deleted characters
Tom
September 17, 2011 at 4:57 am
Tom.Thomson (9/17/2011)
The responses to [http://www.sqlservercentral.com/Forums/Topic1176445-1550-1.aspx?Update=1]this question[/url] struck me as being carelessly wrong, so I jumped in with a contradictory response. Could domeone with a really good understanding of the interaction between indexes and locking have a look and join the discussion, please?fix typos (finger on two keys at once).
I don't think he's clearly wrong. He actually made an excellent point. We all assumed that both indexes would have a high beneficial impact on the db but, really how did we all come to that conclusion?
We don't know how big's the table, how large are the columns and how often they are each DMLed, what queries, if any, will go faster and by how much.
He also says that like most of us he'd go for #2, but that it's not an automatic call.
September 17, 2011 at 5:04 am
Tom.Thomson (9/17/2011)
Could domeone with a really good understanding of the interaction between indexes and locking have a look and join the discussion, please?
I already did, but seeing as I'm one of those being 'carelessly wrong' feel free to ignore it.
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
September 17, 2011 at 5:11 am
GilaMonster (9/17/2011)
Tom.Thomson (9/17/2011)
Could domeone with a really good understanding of the interaction between indexes and locking have a look and join the discussion, please?I already did, but seeing as I'm one of those being 'carelessly wrong' feel free to ignore it.
I don't ignore things you write, Gail. I read them carefully and quite often adjust my own ideas to fit yours better, as yours are quite often the better ones.
Tom
September 17, 2011 at 6:01 am
Tom.Thomson (9/17/2011)
The responses to [http://www.sqlservercentral.com/Forums/Topic1176445-1550-1.aspx?Update=1]this question[/url] struck me as being carelessly wrong, so I jumped in with a contradictory response. Could domeone with a really good understanding of the interaction between indexes and locking have a look and join the discussion, please?fix typos (finger on two keys at once).
Hey Tom, I see that you've got your #sqllearning hat on pretty early today (well, early for me!), and Gail is doing some awesome teaching there (and not just for you, but for all of us).
On a side note, it looks like you were trying to make the above a hyperlink. You need to start it off with [ url=] (without the space), like this (quote this message to see): this question
(Edit: 24 posts to go to reach 30,000 :w00t::w00t:)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2011 at 8:51 am
WayneS (9/17/2011)
On a side note, it looks like you were trying to make the above a hyperlink. You need to start it off with [ url=] (without the space), like this (quote this message to see): this question
Who stole my "url="? Where did it go to? I've put another one in there now, but if people keep on stealing them I may use so many that we end up with a world shortage of "url="s, and that surely would not be a good thing.
(Edit: 24 posts to go to reach 30,000 :w00t::w00t:)
23 now
Tom
September 17, 2011 at 8:56 am
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2011 at 9:02 am
Oh and 21 now
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2011 at 9:59 am
September 17, 2011 at 10:31 am
Steve Jones - SSC Editor (9/15/2011)
Please let's not have quotes of every post that counts down from 95, 94, 93.I'm going out of town for a few days. I'm hoping that I don't have 300 posts in this thread when I get back :w00t:
Jan Van der Eecken (9/17/2011)
And down to 20. This will become an ugly contest 😀
So, we're ignoring Steve (again)? (and this makes 19...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2011 at 10:38 am
WayneS (9/17/2011)
Steve Jones - SSC Editor (9/15/2011)
Please let's not have quotes of every post that counts down from 95, 94, 93.I'm going out of town for a few days. I'm hoping that I don't have 300 posts in this thread when I get back :w00t:
Jan Van der Eecken (9/17/2011)
And down to 20. This will become an ugly contest 😀So, we're ignoring Steve (again)? (and this makes 19...)
Apparently so.
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
September 17, 2011 at 11:50 am
GilaMonster (9/17/2011)
WayneS (9/17/2011)
Steve Jones - SSC Editor (9/15/2011)
Please let's not have quotes of every post that counts down from 95, 94, 93.I'm going out of town for a few days. I'm hoping that I don't have 300 posts in this thread when I get back :w00t:
Jan Van der Eecken (9/17/2011)
And down to 20. This will become an ugly contest 😀So, we're ignoring Steve (again)? (and this makes 19...)
Apparently so.
Well you aren't - you didn't include the number remaining in your comment.
And neither am I - I haven't said the number one less thanm the one you didn't say.
Tom
September 17, 2011 at 12:17 pm
Tom.Thomson (9/17/2011)
GilaMonster (9/17/2011)
WayneS (9/17/2011)
Steve Jones - SSC Editor (9/15/2011)
Please let's not have quotes of every post that counts down from 95, 94, 93.I'm going out of town for a few days. I'm hoping that I don't have 300 posts in this thread when I get back :w00t:
Jan Van der Eecken (9/17/2011)
And down to 20. This will become an ugly contest 😀So, we're ignoring Steve (again)? (and this makes 19...)
Apparently so.
Well you aren't - you didn't include the number remaining in your comment.
And neither am I - I haven't said the number one less thanm the one you didn't say.
I'm keeping to to the word of Steve's request and breaking the spirit of the request. 😀
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
September 17, 2011 at 12:38 pm
GilaMonster (9/17/2011)
Tom.Thomson (9/17/2011)
GilaMonster (9/17/2011)
WayneS (9/17/2011)
Steve Jones - SSC Editor (9/15/2011)
Please let's not have quotes of every post that counts down from 95, 94, 93.I'm going out of town for a few days. I'm hoping that I don't have 300 posts in this thread when I get back :w00t:
Jan Van der Eecken (9/17/2011)
And down to 20. This will become an ugly contest 😀So, we're ignoring Steve (again)? (and this makes 19...)
Apparently so.
Well you aren't - you didn't include the number remaining in your comment.
And neither am I - I haven't said the number one less thanm the one you didn't say.
I'm keeping to to the word of Steve's request and breaking the spirit of the request. 😀
The request? I thought we were just hoping to break Steve's spirit in general.
CRAP, I've been doing it wrong again!;-)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 29,971 through 29,985 (of 66,742 total)
You must be logged in to reply to this topic. Login to reply