Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server differential backup file size smaller than previous one? Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 4:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 5:43 AM
Points: 485, Visits: 235
Hi All,

I use Production Database Backup Strategy as follows:

- Full Backup: every 2 weeks

- Differential Backup: every day

- Transactional Backup: every hour

Sometimes differential backup file size become smaller than previous one. Could anyone give me some more detail regarding this issue.

Is it normal or differential backup file size should increase until the next full backup?

Is this differential backup reliable? As the database size is very large (around 2.5 TB) and according to hardware resource limitation, It's not possible to restore database to verify backup accuracy.

Thanks in advance,
Sanaz.



Post #1461973
Posted Tuesday, June 11, 2013 4:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 41,569, Visits: 34,494
Someone took an ad-hoc full backup?
Lots of data was deleted?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1461976
Posted Tuesday, June 11, 2013 4:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 2,078, Visits: 2,411
GilaMonster (6/11/2013)
Someone took an ad-hoc full backup?

You can check this by restoring the header of the backups and look at (the sequence of) the LSN numbers.
Do you use compression on the backup? That could explain some differences in the size of the files.

sani (6/11/2013)
As the database size is very large (around 2.5 TB) and according to hardware resource limitation, It's not possible to restore database to verify backup accuracy.
You should get some place where you can verify your backup by using a complete restore. Without a proper restore you will never know for sure if your backup is valid.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1461980
Posted Tuesday, June 11, 2013 4:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 41,569, Visits: 34,494
HanShi (6/11/2013)
GilaMonster (6/11/2013)
Someone took an ad-hoc full backup?

You can check this by restoring the header of the backups and look at (the sequence of) the LSN numbers.


Or, much easier, query the msdb backup tables and see if there's an other full backup listed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1461981
Posted Tuesday, June 11, 2013 4:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 5:43 AM
Points: 485, Visits: 235
Thanks for the reply.

As a DBA, I have fully control on Backup activities and I am sure no full backup job has been run.

As a business process point of view, It is possible large amount of data have been deleted. Now I want to know does the deletion consequent to have smaller differential backup size?

Many Thanks,
Sanaz.



Post #1461983
Posted Tuesday, June 11, 2013 4:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 41,569, Visits: 34,494
sani (6/11/2013)
Now I want to know does the deletion consequent to have smaller differential backup size?


It can do, depending on what's deleted and how much. Backups only back up allocated extents.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1461987
Posted Tuesday, June 11, 2013 4:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 5:43 AM
Points: 485, Visits: 235
What do you mean by "what's deleted".

Could you please give me more detail information or any MSDN link regarding this issue.

Thanks in advance,
Sanaz.



Post #1461992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse