Backup questions for 612GB database!

Question

Backup questions for 612GB database!

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

akhamoshioke (5/5/2014)
I do believe that diff and log backups don't break the log chain but the rely on the LAN from the last full to work don't they?

Diffs do, not logs.

If not, why is a full backup needed to restore a log or differential backup?

Well how else are you going to get a DB to apply the differences (differential) or transactions (log) to?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 2

My point exactly. If the diffs don't rely on LSN, how then can they be applied to the full and how do they know what full they are built on?

The answer is quite simple. Data extents.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Diffs DO rely on the LSN of the last full backup. Logs do not. Logs rely on the LSN of the previous log backup.

That's why COPY_ONLY is used on a full backup to not interfere with future differential backups.

What does data extents have to do with anything?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 4

http://technet.microsoft.com/en-us/library/ms345448(v=sql.105).aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

akhamoshioke (5/5/2014)
http://technet.microsoft.com/en-us/library/ms345448(v=sql.105).aspx

I'm fully aware of how differential backups work, as I'm sure is Perry. Was there a specific point you wanted to make?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 6

Yes Gail!

Thanks for the insights.

It just gets a little confusing sometimes.

There is documentation about differentials relying on the data extents as well as the LSN so which is it exactly?

Also, i am aware of the fact that log backups rely on the LSN of the previous log backups but then isn't it safe to say that in a scenario where i have a log backup taken at 4PM and a full at 6PM and another log at 8PM, although the 8PM log contains the changes from the 4PM log backup, i can still chose to restore the 6PM full and apply the 8PM log without loss of data for that time period?

Now my question would be;

Is that possible because the full didn't change the lsn? (cos if it did, why is the 8PM log still relevant to the 4PM log?)

or

Is it more of the fact that a full backup contains both the data and log file and hence the 8PM log can read off the 4PM log from the 6PM full and keep track of it's last LSN? (Which would make a lot of sense to me and has been my thought pattern for years now).

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 7

Trust me Gail!

I have followed you and Perry for a while and your expertise is in no way in question here. Just trying to get deeper with the subject as far as understanding goes.

:):-D:-)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Differential backups are reliant on the differential base LSN. This is changed during each full backup. A full backup may be followed by as many diffs as you need. As soon as another full backup is taken the base LSN is moved forward making all previous diffs incompatible with the new full backup.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

GilaMonster (5/5/2014)
Jeff Moden (5/4/2014)
my interpretation is that if the COPY_ONLY option is on, it won't interfere with either differential backups or PIT log backups.
Correct.
However, a full backup without COPY_ONLY won't interfere with log backups either. Full and diff backups do not and never have broken the log chain or interfered in any way with log backups, no matter what options are specified. Log backups aren't based on the last full/diff, they're only based on the previous log backup (unless the full/diff was taken after the log chain was broken)
The sole use for COPY_ONLY on a full backup is to prevent that full backup from resetting the differential base, so that any differential is based on the last normal full backup, not the one with COPY_ONLY.
For a more complete coverage: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

Ah, thanks. You're coming at it from a different angle than I came at it. I use COPY_ONLY because, when I need to do so, it's usually a backup to a different drive other than the ones that normally contain the backups. Doing so certainly won't break the log chain but, if you don't use COPY_ONLY , the GUI recognizes that latest backup as the starting point for a PIT restore. If someone ever does such a thing while I'm unavailable and deletes that file AND they need to do a restore, I'm not sure they'd know how to handle it.

Regardless of any of that and for any one reading this, you're absolutely correct about the COPY_ONLY having zero impact on the log chain. It's not the log chain that I try to protect by using COPY_ONLY... it's to save on any confusion that someone might have in the "chain of files" presented during a restore in my absence using only the GUI.

I also don't use DIFs.

Thanks for the feedback, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 10

Thanks Perry!

Now how does that tie into the data extents?

Also how about the previous inquiry into log backup operations?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

The only 'tie-in' with extents is that full and diff backups both backup extent by extent, not page by page. A differential backup contains all the extents which changed since the full backup that it is based on

They have nothing to do with telling which full backup a diff belongs against, that's done via LSN, specifically what's called the 'differential base', set by full backups (without the copy only).

As for full and log backups. To restore to a point in time, you need a full backup and all the log backups since that full. It does not matter in the slightest whether there were other full backups or differential backups between the full you restore and the point to which you're restoring to, as long as you have an unbroken chain of log backups (and neither full nor diff backups break the log chain)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

humbleDBA Hall of Fame Points: 3540 More actions · Answer 12

Hyabusact (4/30/2014)
...We do an incremental backup every 15 minutes so that we can restore up to any 15 minute windows if necessary. ...

Ok, I'd hate to think I've missed something with regard to SQL Server, but my understanding is that there is no such thing as 'Incremental' backups, as in each 'incremental' backup adds just the changes to the database from the last 'full' or 'incremental' backup. I'm guessing this is just a mis-use of a word and you meant to say 'Differential' backup, as in each 'differential' backup contains changes to the database since the last 'full' backup.

Hyabusact (4/30/2014)
...The nightly backup is a full backup with transaction logs so that we release the space back to the log file. ...

Are you saying here that you only back up the Trasaction Log once-a-day on or around the time you do a Full backup? As others have mentioned, Transaction Log are usually done at frequent intervals, allowing for reasonable point-in-time recovery and keeping the Transaction Log to a reasonable size, amongst a couple of other things.

Hyabusact (4/30/2014)
...One option is to convert the server into a virtual server (VMware) where more spindles are working with the database. ...

I guess my question here would be, why can you not extend current Windows volumes (with more spindles) or add extra Volumes to your (my assumption here) Physical Server? Having a VM does not necessarily mean you will get more spindles, because it depends on what your Infrastructure Team (if you have this) allocate to you, along with many other factors such as whether Infrastructure share your 'spindles' with other applications, etc.

To give you an idea of what can be possible, one 500GB DB we have (on a physical box) backups up in just over 20 mins to a separate File Server on our estate. As to options, I can't really add much more than what Gail, Andrew, and a couple of others have said.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

humbleDBA (5/7/2014)
Ok, I'd hate to think I've missed something with regard to SQL Server, but my understanding is that there is no such thing as 'Incremental' backups, as in each 'incremental' backup adds just the changes to the database from the last 'full' or 'incremental' backup. I'm guessing this is just a mis-use of a word and you meant to say 'Differential' backup, as in each 'differential' backup contains changes to the database since the last 'full' backup.

Some backup tools refer to log backups as 'incrementals'. They're technically not, but...

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

humbleDBA Hall of Fame Points: 3540 More actions · Answer 14

GilaMonster (5/7/2014)
humbleDBA (5/7/2014)
Ok, I'd hate to think I've missed something with regard to SQL Server, but my understanding is that there is no such thing as 'Incremental' backups, as in each 'incremental' backup adds just the changes to the database from the last 'full' or 'incremental' backup. I'm guessing this is just a mis-use of a word and you meant to say 'Differential' backup, as in each 'differential' backup contains changes to the database since the last 'full' backup.
Some backup tools refer to log backups as 'incrementals'. They're technically not, but...

...they are incorrect

I appreciate your comment, Gail...honest 😀 ...but there are enough myths and misconceptions in the SQL Server community...with 'incremental' being one I've heard before (in relation to backups) and they are not, they are different-ial. 😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

humbleDBA (5/7/2014)
but there are enough myths and misconceptions in the SQL Server community...with 'incremental' being one I've heard before (in relation to backups) and they are not, they are different-ial. 😛

I'm not claiming that any backup tool calls a differential an incremental, I've never seen one that does that. I've seen ones which call *log* backups incremental. In some senses log backups do behave like incrementals, though they're not under the covers.

Generally if I hear someone talking about incremental backups in SQL Server, I assume they're talking about log backups.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass