For my backup plan for SQL Server 2005, I want to do a full on Sunday:
BACKUP DATABASE myDatabase TO DISK = 'D:\Database Backups\myDatabase_full.bak' WITH FORMAT
GO
Then I want to do a differential nightly the rest of the week:
BACKUP DATABASE myDatabase TO DISK = 'D:\Database Backups\myDatabase_Diff.bak' WITH DIFFERENTIAL
GO
My assumption was that if there was little/no activity in the database, then the differential would not increase in size (or wouldn't increase by much).
However, when I run the differential backup above (with little or no activity), I'm seeing the differential backup increase by megs at a time. Why is it increasing like that?
Thanks
Something is changing your database!
Things like index rebuilds or defrags will cause page changes. Changes that are rolled back may have changed pages, so they count as well.
In addition differential backups are considered 'fuzzy' and so will have transaction log data in the backup, which it needs for consistency.
Paul Randal wrote a very cool script a while back that will tell you how many extents have changed since your last full backup, so you can use this to calculate how big your differential is going to be.
In addition, you should be able to use this script to tell you what pages have changed in your db. This may help you solve your changing data mystery.
Remember that a differential is not the same as an incremental. For example, if you have a 100MB database, and make 5MB of changes, each differential backup will be at least 5MB. With Incremental, if you have a 100MB database and make 5MB of changes on Monday, and no changes on Tuesday, you'll have a 100MB full backup, a 5MB incremental for Monday, and a small sub 1MB backup for Tuesday.
For differential, if you have 100MB database, and do 5, 1, 3, and 5MB of changes over the next 4 days, you will have differential backups of 5, 6, 9, and 14MB.
Are you entirely certain that there is no activity on that database at all? Perhaps you could run profiler to see if there's anything happening that you aren't expecting, and also check maintenance plans or scheduled tasks for anything there that might be modifying data.
That said... I realize I don't have a true answer. The daily differentials I do on one particular database (~6GB) always increase over the weekend, even when there is theoretically nobody logged into the application that uses the database. I haven't bothered to look into it as I know that there are services on that server that occasionally read/write to that database on demand, though infrequently.
A differential backup backs up 8K pages that have been "touched".
If all your changes are small and in the same area, you'll have a small backup. Which is unlikely.
A differential backup may not grow if you keep dirtying the same pages (also unlikely)
Some things that will dirty a page:
I am (basically a java developer) facing the same prob that my live db is 7.5 GB and its diff backups per day are 1.5 GB. (in fact we take diff backups for 1 hr) When i ran randy's script for the half day (i.e at lunch time which is half of a complete business day) I got following output:
total change percentage 125658 17169 13.66
I am sure this is a old application and very few ppl use this but how can this be possible to show 17169 changed extents which is causing: 17169 * 64k => 1098.816 MB (nearly 1GB differential) for such a less used application. Please share your ideas on this...how can I debug this further ?? I mean how can I reduce extents further down to get small differentials.