November 9, 2008 at 11:44 pm
hai friends
i have doubt on diff backup
after full backup how its taking which one is modifying data for diff backup
please explain
November 10, 2008 at 12:01 am
you can get the information based on trace file or log file. see more information related to fn_trace_gettable in SQL BOL.
November 10, 2008 at 1:08 am
If I understand the original post correctly the poster is asking how a diff backup can only take changed parts of the db?
As part of each physical db file there are some reserved pages known as the DCM (Differential change Map) this contains a flag for each extent in the db (extent being 64k - 8 pages). When a full backup is made all the entries are cleared. When a page gets updated the corresponfing DCM flag is set on, so the system knows that extent got changed . Then when you ask for a DIFF backup it can examine the DCM flags and only back up the extents that have changed. Really simple and means diff backups can be blindingly fast even on large dbs. "Inside SQL Server" has loads more details
Mike John
November 10, 2008 at 12:06 pm
Diff backups run well and Mike has a great explanation.
Note that diffs grow larger each time because each diff grabs data since the last full, not since the last diff.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 11, 2008 at 6:55 am
We hardly use differential backups here anymore. Less headaches is to have a full backup daily and transaction log backups every 30-60 minutes.
For example, if you use diff backups, one schedule used is:
Sun: Full
Mon-Sat: Diff
And in between everything, let's say you still do transaction log backups.
To restore, you need to restore the Full, the diff's, and the trans logs since the last diff.
Easier to backup up full every day, and restore that full and all trans logs since then.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
November 11, 2008 at 9:59 am
Gaby A. (11/11/2008)
Easier to backup up full every day, and restore that full and all trans logs since then.
Depends what kind of maintenance window you have nightly. Last big DB I worked on took 4 hours to backup fully. Nightly maintenance window was 2 hours. Hence we did the fulls over the weekend when we had hours and a diff nightly because that was all that fit into the time we had available. Then tran log every 15 min.
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
November 11, 2008 at 10:19 am
GilaMonster (11/11/2008)
Depends what kind of maintenance window you have nightly. Last big DB I worked on took 4 hours to backup fully. Nightly maintenance window was 2 hours. Hence we did the fulls over the weekend when we had hours and a diff nightly because that was all that fit into the time we had available. Then tran log every 15 min.
True. I guess for us most of our backups are under two hours and our maint windows are about 4-5 hours. I'm sure when we hit something big enough, we'll have to consider the diff's.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply