SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Vanishing data and relationships
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/21/2002 :  08:57:21  Show Profile  Visit AskSQLTeam's Homepage
Norman writes "We recently have had missing data from our database in SQL Server 2000. The logs do not show anything, but the main table got records deleted or missing somehow, and it took records out of the other tables. All that was done recently was a backup of the table to a file on the D: drive, and an export from the database to another on another server. The export apparently has the same incomplete data as the database it was imported from. Before the import the second database was blank. My coworker seems to think that either the backup or export had caused the problem. Is it possible for a backup or export to delete records, and if so, could it only delete from certain tables? Also the relationships mysteriously seemed to have vanished from the tables.

Also what could be causing records to go missing? We have SQL Server 2000 and SP2 installed for SQL Server, and SP3 installed for Windows 2000 Server.

The only other time I have seen something like this, was when someone was deleting records without knowing that they where doing by hitting a wrong key. But that was in an Oracle Form, on a different database. This is a test database still being developed. But we have about a dozen people in the company with access to our Intranet. I doubt any of them would delete records, but I cannot find any log of it.

Any ideas?

If I do not find out soon, I could get fired for this. I've never seen such a situation with SQL Server before. Backups and Exports should not delete data (in half the tables), and remove relationships (half of them?) should they?"

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/21/2002 :  09:06:43  Show Profile  Visit Merkin's Homepage
Sounds very odd.
Do you have any cascading deletes enabled anywhere ? That is about all I could think of.

Damian
Go to Top of Page

rihardh
Constraint Violating Yak Guru

Slovenia
307 Posts

Posted - 10/21/2002 :  09:34:16  Show Profile
Somebody obviously wants your job...

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 10/21/2002 :  23:28:07  Show Profile
I've experienced loss of relationships when using the export functions. Now I only use restore.

You could create some auditing triggers on the tables,
http://www.sqlteam.com/item.asp?ItemID=11123
http://accesshelp.net/content/Report.asp?REPORT=4&PARAM_ID=31
http://www.sqlteam.com/item.asp?ItemID=3850

make sure your security is tight only allow data to be modified through stored procedures,
http://www.sqlsecurity.com

and monitor the server connections to cross reference time of incident with users that way if it happens again you will at least know at what time this is happening and who's logged on.

For forms and clients make sure you add a warning/verification message for deletes.

Also make sure to run a backup before you move your data around.

Hope you catch that $*%$$.

Edited by - ValterBorges on 10/21/2002 23:37:52
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000