May 21, 2012 at 9:17 am
Hi, I'm a beginner DBA.
I created 2 seperate maintenance plans (from SSMS -> Task-> Backup wizard)to backup my database- Full and Diff. Differential .BAK files are created daily from Mon thru Sat, and Sunday Full .BAK file is created. If i understand correct, the Differential database backup is the backup of all the changes made in database from last full backup; it is cumulative itself. so the size of next differential database backup is more than the previous differential database backup.
The size of Full database .BAK file sizes from previous Sunday to next Sunday is growing, which is good. but the next Sunday db Full .BAK file size doesn't add up to be the size of the last Full db .BAK + most recent Diff db .BAK file size.
look below my db file sizes...
1st Sunday - 7.53 MB (Full .Bak file size)
Mon - 2.34 MB (Diff .Bak file size)
Tue - 3.34 MB (Diff .Bak file size)
Wed - 3.78 MB (Diff .Bak file size)
Thur - 4.22 MB (Diff .Bak file size)
Fri - 4.35 MB (Diff .Bak file size)
Sat - 4.47 MB (Diff .Bak file size)
2nd Sunday - 8.10 MB (Full .BAK file size)
shouldn't my 2nd Sunday Full .BAK file size be 12 MB (7.53 MB (1st Sun) + 4.47 MB (Sat) )? but why is it only 8.10 MB (slight change only .57 MB) ?
sorry if i'm asking the basic question. i appreciate if someone can answer my question.
Thanks!
May 21, 2012 at 9:41 am
preetid2 (5/21/2012)
If i understand correct, the Differential database backup is the backup of all the changes made in database from last full backup; it is cumulative itself.
Correct, so in a restore you'd only need to restore the most recent differential backup.
I wouldn't worry about the sizes of the backup files - simple example - differential backup contains info about a deleted row, which takes up space in the differential backup file, but none in the full backup file.
May 21, 2012 at 9:47 am
I'm not sure that you can count on the diff to be the size of data change. I suspect that changed extents in there are included, but if anything changes in there, the extent is included, so you could potentially have small data changes including large values in your diff.
As mentioned, I wouldn't worry about the sizes. I track them so that I can see data growth, but only for full backups.
May 21, 2012 at 9:55 am
The 2 backups work differently, so there isn't a connection between the sizes of differential backup and full backup. The full backup will back up the entire database regardless if it was already backed up in the past or not, so the size of the full backup is connected to the size of the database.
Differential database works differently. In the database there are special pages that mark each page in the database that was modified. Whenever a differential backup is running, it will back up only the pages that are marked as modified. Pages that were not modified won't be included in the backup file. When you run a full backup, all the pages that were marked as modified will be cleared and marked as if they were not modified.
Here is a small example. Suppose that you have a table that is using 1000 pages and you want to update all the rows and change the value in a integer column. Since the update statement modified 1000 pages, a differential backup will back up those 1000 pages, but the number of used pages in the database will stay the same and full backup's size will not be modified by this update.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 10:01 am
Thank you! i need to answer this to my IT admin, would be nice if i can show him the reason why it doesn't add up. atleast if i can give few will be nice.
btw, i noticed my both diff , and full maintenance plan has Append existing mode. is it because of this? what is this? see attachment...
May 21, 2012 at 12:05 pm
It doesn't add up because the differentials contain CHANGED data, not just ADDED data. When existing records are modified, the modified pages make the diff backup larger but the FULL will not change very much because the total number of records is not changed. DELETES will also make the diff backup larger (because the pages have changed) but have a negative affect on the full backup size because records have been removed.
May 22, 2012 at 6:38 am
Thank you so much Adi and Dan! thanks for the clear explain and example.
so what is the Append mode in the maintenance plans means?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply