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, NigelrivettAs 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 ?ThanksMartyn. |
|
|
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 HEADERONLYFROM DISK=’C:\full.bak’BackupType IsCopyOnly CompressedBackupSize 1 0 597880750Differential backup :RESTORE HEADERONLYFROM DISK=’C:\diff.bak’BackupType IsCopyOnly CompressedBackupSize 5 0 592478028As 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 MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL 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 LastLSN1 3935463424 19000448530800001 20000021365600001CheckpointLSN DatabaseBackupLSN20000021356900224 20000017546900066Following is the output of Restore header from differential backup.BackupType BackupSize FirstLSN LastLSN 5 2227660800 19000448530800001 20000026349100001 CheckpointLSN DatabaseBackupLSN DifferentialBaseLSN20000026339700443 20000021356900224 20000021356900224The 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. |
|
|
|