differential backup missing last updated data

SQLNewbieSQLNewbie Posts: 3
edited February 26, 2010 12:59AM in SQL Backup Previous Versions
Hi,
I hope if you can help me to figure solution for this issue. I have 40 GB Once a week we run FULL backup but during the week we run a differential backup every hour then we restore the differential backup on ReadOnly DB.

The process is working fine for the past year, lately we notice the differential backup will miss some records that should be included in the bk.

Here is Example to make it easy.
if user changes some records @ 12:40 PM, we will expect the differential backup process when it runs @ 1 PM it will include the changes done @ 12:40 PM.

What we found the differential backup @ 1 PM does not include those changes but later at 2 PM or 3 PM, the differential backup will include the changes done @ 12:40 PM.

As if differential backup include the db changes done 2 or 3 hours ago.

I checked the SQL events, error log, red-gate logs and I could not find any errors.

Is there anyway to make sure the differential backup include last hour changes.

Thank you very much.

Comments

  • peteypetey Posts: 2,358 New member
    I can only guess that the transaction at 12:40 PM was not committed by 1 PM, hence it wasn't visible in the read-only database.

    If you can reproduce this situation easily, you could try changing some records using your application, then use Query Analyzer/SSMS to verify if the changes are immediately committed. I wouldn't use the application itself to check, just in case they run using the read-uncommitted isolation level or lower (I used to work on an ERP software that actually did this).

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I tried to enter data via the website and after saving the changes. I checked the table and I verified the data is already saved. I ran DBCC OPENTRAN and there was no active open transactions.

    Then I ran differential backup and restored it then checked the readonly DB and the changes I made was not carry over with the diff backup.

    Red gate backup is great product but this issue can cause us to look for different product.

    I will hope to fix this issue and keep the backup software.
  • peteypetey Posts: 2,358 New member
    Could you repeat the same tests, but this time, instead of using SQL Backup to perform the differential backup, could you try using the SQL Server to perform the differential backup? Do your get the same results?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.