Random Table Data Deletion

[hunsingerl]

First time poster here, long time reader. I have a mystery on my hands and I'm hoping for some Insite.
2016 MS Access with about 10 users running their own front end (all the same version) with a backend on a server. Yesterday ALL of the records in about 10 tables got deleted. I have run down every possibility I could think of and haven't solved it. It wasn't user error (I had the user that reported seeing this happen live show me step by step what they were doing at the time), there isn't any code in the front end that could have done it, there isn't any relationship cascade delete for the tables effected, there wasn't any network interruptions, there wasn't any automated tasks ran at the time, and there isn't any archive/pruning set up.
Was able to restore to a backup from the previous day and it hasn't happened again, but I would like to figure out what caused this. Any help is appreciated!

 

[Ranman256]

Is the backend Access db? If so, make frequent backups in case of corruption.

also make sure users can delete mass records. (or any records)

 

[hunsingerl]

Is the backend Access db? If so, make frequent backups in case of corruption.

also make sure users can delete mass records. (or any records)

Yes, the backend is Access db. I backup every day, so the data loss isn't horrible. Users can't delete records.

 

[GPGeorge]

Yes, the backend is Access db. I backup every day, so the data loss isn't horrible. Users can't delete records.

"Users can't delete records."

Unfortunately, that level of confidence might turn out to be misplaced. Users can be a canny lot. Query each one of them for insights....

 

[hunsingerl]

"Users can't delete records."

Unfortunately, that level of confidence might turn out to be misplaced. Users can be a canny lot. Query each one of them for insights....

Yeah, I've checked with all of them and had them show me what they were doing at that time. Either way, the amount of data across the tables in question were deleted way too fast for it to be one of the users. Virus/Malware scan also came back clean.

 

[tvanstiphout]

I would step up backups to once every 15 min. A simple xcopy backup may be enough for that.

What are the relations between the tables? Hopefully they are enforced. If this were a Northwind 2 style app, trying to delete all rows in random table would quickly run into RI preventing it. If the data WAS deleted in a specific order, a sentient being would be involved.

 

[hunsingerl]

I would step up backups to once every 15 min. A simple xcopy backup may be enough for that.

What are the relations between the tables? Hopefully they are enforced. If this were a Northwind 2 style app, trying to delete all rows in random table would quickly run into RI preventing it. If the data WAS deleted in a specific order, a sentient being would be involved.

Yeah, I've never run into an issue like this in the decade of work with this db. Safe to say I'll be stepping up the backups.
The tables with data loss are enforced. It's like a DELETE * ran on the tables effected, but none of the users did anything of the sort and there isn't any automaton that runs that kind of script.

 

[GPGeorge]

Yeah, I've checked with all of them and had them show me what they were doing at that time. Either way, the amount of data across the tables in question were deleted way too fast for it to be one of the users. Virus/Malware scan also came back clean.

Delete * FROM tblYourTableNameGoesHere can run really fast.

 

[hunsingerl]

Delete * FROM tblYourTableNameGoesHere can run really fast.

None of the users know SQL or know how to run a SQL statement.

 

[isladogs]

Check whether those tables that were emptied have a relationship with a parent table in which referential integrity is applied together with cascade delete

 

[sonic8]

Yeah, I've checked with all of them and had them show me what they were doing at that time. Either way, the amount of data across the tables in question were deleted way too fast for it to be one of the users.

Lesson #1 when dealing with users: There can be a significant discrepancy between what users think they did, what they tell you they did, and what they actually did!

My own biggest mess up as a user of an Access application: In a continuous (or datasheet) form I selected several hundreds of records with the intention to copy them into an Excel sheet for some custom reporting. - Instead of [CTRL]+[C] I accidentally pressed [CTRL]+[X].
The selected records were deleted from the database. Cascading deletes were activated. Without really noticing (it took a moment until I did), I accidentally deleted thousands records across multiple tables from the database in an instant. The result was exactly what @hunsingerl is seeing in his database.

 

[hunsingerl]

Lesson #1 when dealing with users: There can be a significant discrepancy between what users think they did, what they tell you they did, and what they actually did!

My own biggest mess up as a user of an Access application: In a continuous (or datasheet) form I selected several hundreds of records with the intention to copy them into an Excel sheet for some custom reporting. - Instead of [CTRL]+[C] I accidentally pressed [CTRL]+[X].
The selected records were deleted from the database. Cascading deletes were activated. Without really noticing (it took a moment until I did), I accidentally deleted thousands records across multiple tables from the database in an instant. The result was exactly what @hunsingerl is seeing in his database.

Absolutely agree with that first statement.
As far as statement 2 goes is the weird part... Cascading deletes are set up, but the records that could cause that kind of delete weren't modified. As an example: Table 1 is the main with tables 2-10 as children of that main table. Relationship is set up so that if table 1 record gets deleted, data related to that table 1 record for tables 2-10 get deleted. Problem is all table 1 data is fine, and table 2, 5, and 10 are all fine. Tables 3, 4, 6-9 ALL data is gone. A user would of had to delete ALL table 1 data to cause ALL table 2-10 data to delete, which didn't happen.

 

[The_Doc_Man]

An excellent point, @sonic8 - and I have to correct @hunsingerl in that to use the database at all, users MUST have delete capabilities from the raw permissions viewpoint. To say that users cannot delete records is not technically correct. You SHOULD say "you think have blocked off ways for users to delete records." (Because that is all ANY of us can do.)

For a bulk record deletion in a single table, you have potential issues with relational cascades. But losing all the records in 10 tables? You said you had no cascade delete relationships set up, so the only thing I can see is a type of corruption OR somehow messing with system tables.

Did any of the user report (after this event) that they got a "Database in unusable format" error? (Sign of corruption.)

Did you run a Compact & Repair on a copy of the damaged database to see what it would return?

When you applied that backup copy, did you compare sizes between the suddenly damaged DB and the backup copy?

It might be that NONE of those questions actually are related to what happened, but if you got an anomalous result from one them, it would go a long way in explaining what happened.

 

[hunsingerl]

Check whether those tables that were emptied have a relationship with a parent table in which referential integrity is applied together with cascade delete

Cascading delete is set up, but you can check the above reply as to why that wasn't the case here.

 

[hunsingerl]

An excellent point, @sonic8 - and I have to correct @hunsingerl in that to use the database at all, users MUST have delete capabilities from the raw permissions viewpoint. To say that users cannot delete records is not technically correct. You SHOULD say "you think have blocked off ways for users to delete records." (Because that is all ANY of us can do.)

For a bulk record deletion in a single table, you have potential issues with relational cascades. But losing all the records in 10 tables? You said you had no cascade delete relationships set up, so the only thing I can see is a type of corruption OR somehow messing with system tables.

Did any of the user report (after this event) that they got a "Database in unusable format" error? (Sign of corruption.)

Did you run a Compact & Repair on a copy of the damaged database to see what it would return?

When you applied that backup copy, did you compare sizes between the suddenly damaged DB and the backup copy?

It might be that NONE of those questions actually are related to what happened, but if you got an anomalous result from one them, it would go a long way in explaining what happened.

First point is correct. I have blocked delete ability for users.

Thats what is most confusing to me is that a singular table I could see an issue with cascade failure, but it was 10 random tables.

No users reported a Database in unusable format (Definitely doesn't mean that didn't happen, users click past errors all the time...).

Nothing came back on the compact & repair.

There was a sizeable difference in the size comparison of the backup vs. the damaged DB, lots of data missing.

Best guess is to chalk it up to database corruption, but I've never seen a corruption cause ALL data in 10 random tables to be deleted.

 

[ebs17]

Deleting records in many and dependent tables is not that easy. Implementing it in a script requires some considerations that not everyone can master.

If all the statements made above are true, I would then look more in the direction that tables were deleted directly in the backend and restored incorrectly.

 

[hunsingerl]

Deleting records in many and dependent tables is not that easy. Implementing it in a script requires some considerations that not everyone can master.

If all the statements made above are true, I would then look more in the direction that tables were deleted directly in the backend and restored incorrectly.

Only person that has direct access to the backend file is yours truly.

 

[The_Doc_Man]

There was a sizeable difference in the size comparison of the backup vs. the damaged DB, lots of data missing.

This is potentially a significant fact. Was that size difference between the damaged DB and the backup based on the "raw" damaged database for which you had not yet attempted a Compact & Repair?

If you use Access to make a backup copy, I don't know if it implies a C&R. If you use Copy/Paste from Windows Explorer, that DOES NOT imply C&R and thus the copied file should be the same size. If the damaged DB was not C&R'd before examination and was STILL shorter than the backup DB, that is a sign of corruption. An "ordinary" delete doesn't reclaim space from a file until the C&R occurs.

Stated another way, if you took a backup of the DB, copied that, and deleted tables manually, but did NOT do a Compact & Repair, the two files should have been approximately the same size within a couple of percent. If the damaged DB is significantly shorter, the physical file is damaged.

I would run a CHKDISK on the disk holding that damaged back-end to see if it has one or more file fragments it can't identify as being part of a named file. (Because you have already replaced the original with its backup, thus replacing the name.) If that has happened, you had a disk failure.

To follow up on that, you should check the system's event viewer. If you know approximately when the loss occurred, go to that machine that served the DB and check the event log viewer. Entries are sorted by date/time so it would be easy to find the detailed logs for the time of the error. Look for a disk error. If you had a disk error and lost the back part of the file, the REAL surprise is that you didn't run into even worse errors than merely losing data.

 

[Cotswold]

Could it be that a backup and compact were run whilst users were still using the database? I suppose someone could have made a copy of the database, taken it home, created a series of cascade delete queries and then ran it against the database back at the office.

 

[jdraw]

Have you considered or do you have an Audit Log to identify what changes were made to data (tables), when and by whom?