Avatar of synergeticsoul
 asked on

SQL Table values disappear..have to reload backup..cause??

I have a sql database that has about 60 related tables.  i continue to have problems with 1 table.  every so often, the values of the table disappear and I have to import the data from a backup version.

can anyone tell me why this is happening and what can i do to stop this?  i fear that this could happen to some of my more important tables.

thank you for your help.
Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Anthony Perkins

Somebody is deleting them, perhaps?

You could implement a basic archive procedure to find the culprit:

Create a new table with a similar structure to the original, but with two new columns to hold date and Login. Then implement a DELETE trigger on the original table, which stores the deleted record(s) in your new archive table along with the current date (GETDATE()) and login (SYSTEM_USER).

Then you will be able to recover your data from this archive if necessary, but it will also help point the finger at whoever or whatever process is deleting your records.

I am not sure if someone is deleting the data.  however, CHRISTHORNTON's suggestion on creating a new table seems a good option.  I am just confused as how to create a delete trigger.  would i have much control since the database is housed on our webhost's server?  the only user who logs in is me...  could you please elaborate?  thank you.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Anthony Perkins

1. Create a new table (Table2) with the same structure as the old one, but with three additional columns:

Create Table Table2 (
      Col1 datatype,
      Col2 datatype,
      UserName varchar(50),
      HostName varchar(15),
      DateCreated datetime default GETDATE())

2. Create a Delete Trigger on the original table (Table2)
Create Trigger trg_LogDeletes On Table1

For Delete

Insert       Table2 (
Select      Col1,
From      Deleted
Anthony Perkins

Create a Delete Trigger on the original table (Table2)

Should have read:
Create a Delete Trigger on the original table (Table1)
Anthony Perkins

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.