You’re offline. This is a read only version of the page.
5
we have lost years of transactional data from our GP 2018 company. my 3rd party (who have been actively working on launching WMS and Manufacturing modules in the backgroud) says one of my staff must have run the Utility - delete records,,,, which none of my staff even remotely know how to do.
questions
- can you recover the data?
- can you identify when that utility was run, and by which user?
- is it possible something my 3rd party IT group was doing in the background - they could have accidentally removed the records / tables
help :)
I usually do not employ Full Recovery unless the user is ready to maintain point-in-time restores. Then they would be performing transaction log backups.
In an ideal world Full Recovery is the way to go. But with small databases just running full backups every few hours is sufficient.
I do like your idea however. That is good to know.
Hi Richard,
The method I describe in my blog doesn't need the USER2ENT or USWHPSTD fields to be present in the tables to find out who deleted the records from the database. The SQL transaction log stores the SQL User ID who issued the DELETE operations on the SQL database. Therefore you can query the SQL Transaction Log to find out which SQL User ID deleted the data. (assuming the SQL database is in the FULL recovery model)
Technically you can use this method to find out which SQL User ID deleted data from any SQL database, it doesn't have to be a Dynamics GP SQL database.
Thanks
Gavin
Not all tables have a column USER2ENT or USWHPSTD so there would be no way to tell who deleted these records regardless of the recovery method used. You would need something like FastPath to be running. The simplest way is to restore a backup to another database and then start comparing database. The horses have already left the barn in this case but going forward it would help to start restricting user access by assigning roles to users.
Do you have a backup that you can restore to a test company? You could then run scripts looking for missing DEX_ROW_ID values in the tables in question. I just went through this with another client. First, find out what is missing and then start asking who did the deleting.
Hi,
Unfortunately if the database is in the "Simple" recovery model the transaction log is reused when a SQL transaction commits. (an SQL transaction like a DELETE operation). Therefore you can't use the method I describe to find out I'm afraid.
Thanks
Gavin
thanks Gavin, very helpful, so our current system has been running in Simple Mode - do you mean we just need to switch this over to recovery mode and then run the diagnostics, or does your system have to already be in Recovery Mode for this to have worked ..... so are we too late to find this 'who done it' information at this point in time?
Backups would be the only thing we would have as far as getting data back.
As far as who ran the delete, it looks like the Community has some options for that.
Let us know if you have any questions.
Thanks
Hi,
In addition to this a few years ago I had to find out who had deleted a batch in Dynamics GP and wrote this blog on how this can be achieved in SQL.
You just need to know one of the tables that data was deleted from and plug it into one of the scripts.
Hope this helps.
Thanks
Gavin
Do you have a backup that you can restore to a test company? You could then run scripts looking for missing DEX_ROW_ID values in the tables in question. I just went through this with another client. First, find out what is missing and then start asking who did the deleting.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (