DB Backups Size

Question

DB Backups Size

ChiragNS

One Orange Chip

Points: 26137
More actions
October 12, 2006 at 10:40 pm

#113877

Hi
The size of my database backups (differential backups) seems to increase by 1MB everyday. That is the size of the backup taken today is more than the size of the backup taken yesterday by 1MB.
Any idea what could be the reason behind this.
"Keep Trying"

Viewing 4 posts - 1 through 3 (of 3 total)

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

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

This is how differential backups work - by definition, it will store all changes since the last full backup, so that for a daily diff backup you should see an increasing size of the backup file (unless no changes have been made since the last diff backup).

Note that this behaviour is different to a transaction log backup. This is why when you restore a database to a point in time, you first restore the full database backup, followed by the MOST RECENT diffirential backup, and ALL relevant transaction log backups since the most recent differential backup.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

hi paul

thanks for ur explanation. i do know about differential backups and how to restore them. What i would like to know is why the size of diff backups increase by 1MB for each backup.

Ex:- Size of diff backup on Wednesday is 13.1 MB

Size of diff backup on Thrusdayis 14.1 MB

Size of diff backup on Friday is 15.1 MB

Hope u can help me........

"Keep Trying"

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

OK, we'll need to look a bit deeper into how SQL Server works. As you know, SQL Server stores data in 'pages' that is sized at 8KB - this is the reason why the maximum column width is limited to 8000 characters.

Now, if a change is made to a data residing in a page, that page is flagged as 'changed'. When you come to perform a differential backup, SQL Server will search out all the 'changed' pages and write them to the backup file. The pages flagged as 'changed' are not reset for a diff backup, so when you perform the next diff backup (assuming further data changes on different pages have been effected), the size of the backup file should also increase.

Depending on how your data is 'spread' across the pages and what changes you are performing on a daily basis (if any) would determine the size of the diff backup files. HTH.