1

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

2
  • How large is the database?
    – Adam
    Aug 20, 2009 at 12:19
  • The db is ~ 5GB Aug 20, 2009 at 13:40

5 Answers 5

5

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.

1
  • 1
    Ah - was about to reply with my script link :-) Aug 25, 2009 at 18:58
3

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.

3
  • What if the database is not being used? Why would there be any file size for the differential at all? Aug 20, 2009 at 13:43
  • We run incremental and on a day with no activity (well, actually we run it every 15 minutes) each backup file is still 128kb in size. I don't know how this is relevant I just felt like sharing. Aug 21, 2009 at 3:07
  • That's the backup header and the checkpoint log records from the transaction log. Aug 25, 2009 at 18:58
1

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.

1
  • I'm running profiler on it now, fire off the diff backup and it increases the .bak by 3MB. I hit it again, and it increases the file size by 3MB. Strange. No activity in Profiler. Aug 21, 2009 at 12:58
1

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:

  • Statistics are updated (stored in system tables which have pages allocated)
  • Indexes are rebuilt (lots of data shuffled around)
  • Page splits of data (which means 4 dirty pages for 2 small inserts)
  • ... and page splits of index structures
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .