Full and differential backup size same in SQL Server

20 pts.
Tags:
Backup and restore
SQL Backup
SQL Server
SQL Server backup and restore
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.

ASKED: December 29, 2010  1:56 PM
UPDATED: December 30, 2010  7:17 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Abdulsamad
    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.
    20 pointsBadges:
    report
  • Abdulsamad
    [...] artykuł: Full and differential backup size same in SQL Server Tags: and-differential, backup-size, facing-the, facing-the-following, following, [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following