SQL Server backup causes recent table records to disappear for one table

Question

I just noticed two months after switching out a backup drive that one table in one of the backed-up databases is losing records past a certain point.

The database is backed up weekly.

Prior to the new drive, the table had records from 3/11/2010 to 6/8/2010. After the first backup ran, the table was missing all records past 3/11/2010,except for a single record or two created the day before the backup. The records started accumulating at that point without incident until 3 backups later, a month since the first backup that coincided with data loss. At this point, all records past 3/11/2010 were again missing (except for one or two that were created right before the backup).

This is just affecting one table in the database and it doesn't happen with every backup, just with the ones happening around 6/11 and 7/11.

Any ideas? I'm completely stymied about how to even diagnose this. Other databases on the same backup drive seem unaffected, and other tables in this database are unaffected.

Answer 1

Creating a database backup will not cause rows to be deleted. Something else must be happening to cause that behavior.

Do you know that the rows disappear at (about) the same time as the backup is being made? Perhaps within +/- minutes, hours, or days? Can the problem be replicated, or does it appear to occur randomly? (How long does it take to perform the backup? Does this occur for complete, differential, and/or transaction log backups?)

I'd recommend running SQL Profiler before, during, and after the backup (during that +/- window), and watch carefully for events that might delete rows. You may have to do this for each backup for a while, until you hit an occurance of the actual problem.