Back up and restore missing table update

SQL Server Administration
1

We recently had a user say a report was missing data and I looked into it and discovered that the table in SQL was indeed missing the data. This is in the live environment. I swapped the report to pick up the data from the live copy. The strange thing is there is a nightly backup and restore of the live system that overwrites the live copy, yet the live copy of that table, with the missing data, doesn't get copied over to the live copy version. So this has the data it should have with none missing and is leading me to ask why is the back up not pulling in the data from the liver version with the missing data. Ironically the live copy has provided the data we need for the report but raises the question why is the back up and restore not pulling in the live table?

I've had a look through the jobs and there doesn't appear to be any failures or obvious issues with the restore. Is there a way I can open the restore file somewhere else to see what is actually pulling through?

Note: I haven't been involved in building this set up, only came across it recently so still getting familiar with it.

2

Sorry - but this doesn't make sense. What is a 'live copy' of the data vs. 'live environment' vs. backup/restore?

If data appears to be missing from a backup/restore operation - it is because that data did not exist when the backup completed. That could be caused when an update/insert/delete operation is in progress at the end of the backup. This would cause those changes to be rolled back during the restore.

Something along these lines:

  1. Backup starts at 10pm
  2. Update/Insert query starts at 10:05pm
  3. Backup completes at 10:10pm
  4. Update/Insert completes at 10:15pm

On restore of the backup - the update/insert would be rolled back to a state prior to the start of the update/insert.

3

What is a 'live copy' of the data vs. 'live environment'

A copy of live if that make sense, so we copy the live and use it as a back up?

Seems there was an network interruption that caused this issue meaning the job still ran and never error but it had missed restoring it.