Full and differential backup size same in SQL Server
Hi,
Sub:Full and differential backup size same.
I am facing the following problem on production server,I am getting the full backup(600MB) and differential backup size(598MB).On off peak hours I have taken full backup and just after a min I have taken differential backup but the sizes of both backups are almost same.As per BOL diff backup will contain only the modified extents after the recent full backup.
Do I have to make any changes in sql server settings before taking backups.
We have 3 different servers and I am facing this problem only on one of the 3 servers,other servers are giving differential backups in KBs (as expected) if I take it within a span of one minute but I am facing this problem only on server1. I am using sql server R2. I have also scheduled transaction log backup every 1 hour and trans log backup size is in kbs(14kb);but the problem is with diff backup size.
Only maintenance plan is running for taking backups(full,diff,trans log) No other job is running which might continuously update tables.
Please help me in solving this issue.
Thanks.
Software/Hardware used:
Sub:Full and differential backup size same.
I am facing the following problem on production server,I am getting the full backup(600MB) and differential backup size(598MB).On off peak hours I have taken full backup and just after a min I have taken differential backup but the sizes of both backups are almost same.As per BOL diff backup will contain only the modified extents after the recent full backup.
Do I have to make any changes in sql server settings before taking backups.
We have 3 different servers and I am facing this problem only on one of the 3 servers,other servers are giving differential backups in KBs (as expected) if I take it within a span of one minute but I am facing this problem only on server1. I am using sql server R2. I have also scheduled transaction log backup every 1 hour and trans log backup size is in kbs(14kb);but the problem is with diff backup size.
Only maintenance plan is running for taking backups(full,diff,trans log) No other job is running which might continuously update tables.
Please help me in solving this issue.
Thanks.
Software/Hardware used:
ASKED:
December 29, 2010 1:56 PM
UPDATED:
December 30, 2010 7:17 AM
Answer Wiki:
That is very strange. The only reason that the differential backup would not be very small after the full backup was taken would be if the COPY_ONLY flag was used when the full backup was taken. Check the maintenance plan and make sure that the package isn't generating T/SQL using this flag.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi, Mrdenny thanks for taking your precious time out in replying to this post.
Following is the output of running RESTORE HEADERONLY from full and diff backup sets.
Full backup:
RESTORE HEADERONLY
FROM DISK=’C:full.bak’
BackupType IsCopyOnly CompressedBackupSize
1 0 597880750
Differential backup :
RESTORE HEADERONLY
FROM DISK=’C:diff.bak’
BackupType IsCopyOnly CompressedBackupSize
5 0 592478028
As in the above output IsCopyOnly is set to false(0) but the backup sizes of two backups(full and diff ) are almost same.
Please tell me where am I going wrong.
Please reply as soon as possible, I am stuck in resolving this issue.
Thanks.