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"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegroups.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:
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegroups.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
> >|||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:
> 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:
> > > "Not the Face" <notthef...@.gmail.com> wrote in message
> > >news:1166552868.097104.258360@.73g2000cwn.googlegroups.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- Hide quoted text -- Show quoted text -