So I have a bit of an issue. I opened a ticket with Veeam and was told it was an MS SQL issue. I've opened a ticket with MS, and they're telling me it's a Veeam issue. I agree with Veeam that it's an MS SQL issue.
My issue in brief is this:
A backup from last night will be missing 5+ days worth of data (in fact I can find no rhyme or reason for the number of days of data loss, it is quite variable through my testing). The backup is successful, both in the Veeam job log and in related windows events (ie that a database's transaction logs have been backed up). The server is physical and thus being protected by VEB.
My testing process is this:
1) Restore the MDF and LDF from most recent backup with "Copy To" functionality
2) Attach to either the same server with a different name, or a different server with no name change
3) Select from a table in both the production and restored copies of the database and identify missing rows (easily done as I order by a datetime of entry)
My troubleshooting steps tested:
1) Issue a checkpoint on the database, then issue a manual backup in VEB, then repeat testing process
2) Use an MSSQL-initiated backup (creating a .BAK file), then issue manual backup in VEB, then repeat testing process
The first troubleshooting step does not resolve the issue. The second does.
The MS tech seems fixated on it being an issue with backups. I have tried several times to point out that backups are in fact a red herring in this case, and that the issue is MS SQL not committing dirty pages to disk, but it's tough slogging.
Does anyone have any feedback, suggestions, other courses of action, or anything that might help us out?