differential backup missing last updated data
SQLNewbie
Posts: 3
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.
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8