question

sand143eep avatar image
sand143eep asked

Incremental backup is growing rapidly

Hi Team,

we have backup strategy weekly full backup and daily Differential backup and hourly Tlog backup.

we observed that the Differential backups are growing fast like after taking full backup on sunday, on monday the differentail backup size is 75GB and on thursday it will come to 180Gb and then on saturday around 220GB. on monday again the size will go to 75GB so just wanted to know how this happens.please guide me.

sqlserver2012sql2014sql2008r2
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks its clear now.

0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

A differential backup contains all the changes since the last full backup, so it will grow daily until you take a new full backup weekly.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-server?view=sql-server-ver15

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just needed a small clarification, the data file is growing 2GB per day but the differential backup is growing 30GB. from the above comment its should grow 2gb daily as there is 2gb changes are happening. please clarify.

0 Likes 0 ·

That's 2 different things. If your data file is growing by 2GB per day, that's 2GB of new data. The diff backup contains changes - so it could be 28GB of changes to existing data pages, and 2GB of new data

0 Likes 0 ·
sand143eep avatar image sand143eep Kev Riley ♦♦ ·

Changes in the sense it will be update or delete or insert right.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.