SQL Server Performance Forum – Threads Archive

Backup Size

Hi There Below are my scripts for full backup and differential backup. Full backup runs once a week and usually it is around 5 GB. Differential backup runs everyday (except on full backup day); however the size of differential backup is 3GB for a day, so total backup file will be 5 + 3 = 8 GB after a first differential backup. 8 + 3 = 11GB after second differential back and keeps growing until full backup day. After a full backup it shrinks back to approximately 5 GB again. My database size doesn#%92t grow that fast yet the differential backup grows 3 GB each day (Size of differential backup is huge comparing to full backup and database growth). Is my backup script not correct or is this abnormally large differential backup file caused by some other settings in my database? Full backup:
BACKUP DATABASE [Mydatabase] TO [mydatabase Full Database Backup] WITH INIT , NOUNLOAD , NAME = N’mydatabase full backup’, SKIP , STATS = 10, NOFORMAT
Differential backup:
BACKUP DATABASE [Mydatabase] TO [Mydatabase Full Database Backup] WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N’Mydatabase daily differential backup’, SKIP , STATS = 10, NOFORMAT
Many Thanks Dipendra

Hi ya, While your database doesn’t grow that fast is there much data that is changed or deleted as well as inserted? Updates can cause large differentials especially if fields from clustered indexes are being updated Cheers
Twan

I would suggest to test the backups on a secondary machine in order to prove your disaster recovery process is intact. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

A differential backup contains all changes made to the database since the last full backup. So tomorrow’s differential backup will contain everything that was in today’s plus whatever happens between now and then. By the very nature of differential backups, they will continually grow larger until another full backup is taken. On very active databases, a differential backup could be larger than the full backup. You are taking a full backup "WITH INIT", which recreates the backup file, and then each day, taking a differential backup to the same file "WITH NOINIT", which appends the differential backup to the existing backups each day. So, if your full backup is 5gb, and your 1st differential is typically 3gb what you end up with is something like this… Mon 5gb = 5gb
Tue 5gb + 3gb = 8gb
Wed 5gb + 3gb(from yesterday) + 4gb(from today, yesterday + changes) = 12gb
Thu 5gb + 3gb(from Tue) + 4gb(from Wed ) + 5gb(today, yesterday + changes) = 17gb
and so on. Steve

]]>