I am having issues with items randomly getting deleted in our SQL server database. I have checked the codes in front end (access) and unable to locate the cause of the items getting deleted. Is there away I can write something in the SQL server management studio side that before an item is deleted it actually backs up that row in the table to another location?
This way if I wanted to retrieve any missing data I would be able to just copy them from one location to another.
Thanks in advance.
Yes. Create a DELETE trigger that copies the row to another location after it is deleted (this sounds odd, but it can be done in a DELETE trigger, because that trigger still has access to the row(s) that have just been deleted).
If you put your database into the Full recovery model, make sure you do your transaction log backups then you can restore the backups into a fresh database and recover the deletions. It may be more reliable than a trigger.
You may also want to set up a profile trace to capture what is being done to the database and hopefully ascertain what is "randomly" deleting your data.