Diffrential Backup concerns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Diffrential Backup concerns

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-16 : 22:33:53

Hi,

I have SQL Server 2000 PRODUCTION database size up to 39 GB
I am talking Full Backup every week which comes to 30 GB.
I am taking differential backup every 4 Hours, starting with 200 MB first differential, but in couple of days it becomes 24 GB?
Why it boosted that much? I am not doing any this differant? Normal Bulk import/Export, Lots of Data insertions.

Is that due to no doing Log Truncation?

Please any ideas?

Thanks

SKR

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-16 : 23:27:19

Differential backup contains all changes made since last full backup, that's why backup size will get bigger.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 00:43:50

Perform full backups more regularly to control the differential size.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 14:21:56

Thanks guys.
I can't understand one thing..That if I do make approx 800 MB changes through out day then basically I am increasing backup size up to 1 GB, and in a week time it should be around 7-10 GB approx.... but I didn't understand why it gone up to 24 GB in couple of days?

I do appreciate if I could get a suitable answer on that because I need to tell this to my network manager.

Thanks Guys

SKR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 14:24:18

Because that's how many changes there have been since the last full backup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 14:38:41

Thanks Tara, I will try to do Full Backup every couple of days now to keep the size down.
Thanks for your help.

SKR

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-17 : 17:02:24

Differential backup not only backup whatever changed, it backs up whole extent (8 pages) even just changed one row.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-18 : 12:40:50

quote:
Originally posted by rmiao

Differential backup not only backup whatever changed, it backs up whole extent (8 pages) even just changed one row.

Does tran log backup gets the whole extent too?

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-18 : 22:13:37

No, it only backs up committed transactions in log file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources