views:

114

answers:

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 my megs at a time. Why is it increasing like that?

Thanks

+1  A: 

i must say at first i would have thought like you about the differential backup size ... so i had to check it up... from what i could read :

The downside is if you run multiple differential backups after your full backup, you're probably including some files in each differential backup that were already included in earlier differential backups, but haven't been recently modified

i am still trying to find what kind of file could be included that were not in the full backup cant seem to find the answer but from what i could see the reason of the backup growing by a lot would be that ... im still gonna keep looking if ever i find the answer ill come back and comment

Lil'Monkey
also : Diff. backup not just contains changed data. If you modify one row, sql will backup whole extent the row resides in. If that row has relation to other table, will backup more extents.... so im guessing maybe your database contains a lot of relations .. so one chance could make the whole backup grow by a lot
Lil'Monkey