Full and Differential backup size same ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Full and Differential backup size same ?

Author

Topic

Martyn123
Starting Member

29 Posts

Posted - 2010-12-29 : 02:41:08

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 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.

Please help me in solving this issue.

Please reply to this post as soon as possible.

Thanks.

Martyn.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 03:06:39

Are you running something like an index rebuild after the full backup (maybe as part of the backup process)?
Are you sure you are taking a full backup of the correct database? Try restoring and applying the diff.
Are you sure something isn't updating a lot of pages continuously?
How are you taking the backups? Is it just a backup statement? Try a full and diff from a query window.

>> Please reply to this post as soon as possible.
This won't speed up responses and might put people off.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Martyn123
Starting Member

29 Posts

Posted - 2010-12-29 : 04:48:21

Thanks for ur immediate reply nigelrivett.

No index rebuilding,no job nothing is updating lot of pages,I have taken full and diff backup using ssms wizard.

I have also scheduled transaction log backup every 1 hour,transaction log backup is in KBs(14 kb).Transaction log backup size is correct but diff backup is giving us the problem.

what else I should do.

Thanks.
Martyn

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 04:59:57

Try restoring the diff after the full.
Otherwise I have no idea what is causing it.
Sounds like the full is not resetting the flags.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 05:01:24

quote:
Originally posted by nigelrivett

Try restoring the diff after the full.
Otherwise I have no idea what is causing it.
Sounds like the full is not resetting the flags.

I take it the tr log backups get larger and smaller depending on the transactions i.e. there's not an open transaction.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Martyn123
Starting Member

29 Posts

Posted - 2010-12-29 : 05:36:41

Hi, Nigelrivett

As you have replied "Sounds like the full is not resetting the flags."

Is there any SQL Server level settings which makes the full backup reset the flags ?

Thanks
Martyn.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 05:44:07

Nope - all happens automatically.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2010-12-29 : 09:54:58

Are the backups APPENDING to the file? or creating a new one?

Do the Tlog contain incomplete transactions? I'm just thinking that maybe these would not be included in the TLog backups, but maybe they would be in the FULL / DIFF? (just guessing thought)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-12-29 : 11:41:24

Did you check to make sure your backup is not a "COPY_ONLY" backup?

A COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.

Run the "restore headeronly" command against your full backup file and look at the value it returns in column IsCopyOnly. If it's 1, then it's a COPY_ONLY backup. Also, verify it is actually a full backup (BackupType = 1)

restore headeronly from disk = '\\MyServer\MyShare\MyFolder\MyBackupfile.BAK'

You should run the "restore headeronly" against your differential backup file to make sure it only contains one backup set. If you are not doing an INIT, you could just be adding additional differential backups to the same file.

CODO ERGO SUM

Martyn123
Starting Member

29 Posts

Posted - 2010-12-30 : 02:25:28

Hi, MichaelValentineJones 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.

I got only one line of output when I ran the RESTORE HEADERONLY does this mean the backup contains only one backup set?

Please tell me where am I going wrong or what must be the possible resolution.

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-30 : 22:37:47

Have you tried the restore I suggested?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2010-12-31 : 03:04:32

"I got only one line of output when I ran the RESTORE HEADERONLY does this mean the backup contains only one backup set?"

AFAIK yes

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-12-31 : 21:02:36

Using this as a reference, can you tell which full backup is your differential backup using as the base?

Thanks.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

Martyn123
Starting Member

29 Posts

Posted - 2011-01-03 : 05:09:56

Hi,
Yeou ray mond thanks for replying to my post.

Following is the output of Restore header from full backup.

BackupType BackupSize FirstLSN LastLSN
1 3935463424 19000448530800001 20000021365600001

CheckpointLSN DatabaseBackupLSN
20000021356900224 20000017546900066

Following is the output of Restore header from differential backup.

BackupType BackupSize FirstLSN LastLSN
5 2227660800 19000448530800001 20000026349100001

CheckpointLSN DatabaseBackupLSN DifferentialBaseLSN
20000026339700443 20000021356900224 20000021356900224

The above mentioned backup files were generated within a span of 1 min.ie I had taken a full backup and after 1 min differential backup is taken but why am I getting size of diff backup is so large on off peak hour almost same as that of full backup.

In the above outputs FirstLSN value of full backup and differential backup are both equal , might this be the reason of getting full and differential backup sizes almost same?

Please help me out in resolving this issue.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources