Link to home
Create AccountLog in
Avatar of synergeticsoul
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.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

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.
Avatar of synergeticsoul
synergeticsoul

ASKER

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.
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 (
      Col1,
      Col2,
      ...
      UserName,
      HostName,
      DateCreated)
Select      Col1,
      Col2,
      ...
      SYSTEM_USER(),
      HOST_NAME(),
      GETDATE()
From      Deleted
This:
Create a Delete Trigger on the original table (Table2)

Should have read:
Create a Delete Trigger on the original table (Table1)
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account