A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
Steve
Hi
From BOL:
"A differential backup is based on the most recent, previous full backup of
the data that is included in the differential backup. A differential backup
captures only the data that has changed since that full backup. This is known
as the base of the differential. A differential backup includes only the data
that have changed since the differential base. "
If you don't do another full backup between the differential backups they
will only get bigger if someone changes the data, and will stay the same if
they don't.
You don't say how old your base is, but the size of the differential backups
indicates a significant amount of changes. Have you re-indexes or shrunk the
files since the full backup?
John
"Not the Face" wrote:

> I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>
|||"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegro ups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.

> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>
|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegro ups.com...
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in the
> database.
>
|||Hi
4.7GB does seem large for a differential backup. If you ran the backups as a
script such as:
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
Then a directory of C:\backups gives
Directory of C:\Backups
17/01/2007 15:18 <DIR> .
17/01/2007 15:18 <DIR> ..
17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
17/01/2007 15:18 171,002,368 AdventureworksFull.bak
4 File(s) 174,401,536 bytes
2 Dir(s) 1,241,235,456 bytes free
This shows what you would expect.
John
"Not the Face" wrote:

> Sorry about the lag and I appreciate the responses.
> The DB is currently ~7.6 gb.
> The Transaction Log is currently 5 mb.
> I have tried shrinking the DB and Log files (shrinking the whole DB and
> each file individually)
> This is on a test system, so I have control over the database changing.
> It isn't. I was literally doing the differential immediately after
> the full backup.
> Thanks for your help.
> Steve.
> Greg D. Moore (Strider) wrote:
>
|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
> N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
>
>
>
>
>