Data deleted durning Import

[ecobb]

Something very strange happened this morning on one of my SQL Servers and I was wondering if anyone else has ever had this problem. On my development server, I had created 2 new tables for a new section on a web site. I went into Enterprise Manager and imported these 2 tables (Table E and Table F) and their data into another database, on another server. Everything imported fine, but for some reason SQL Server deleted about 100 rows of data out of Table A, which is not connected to the new tables (E and F) in any way. I restored the database and tried to import again (several times) and each time it did the same thing. Each time I made absolutely sure that I was importing ONLY the 2 new tables. I wound up scripting the 2 tables and writing queries to transfer the data, but I'm just curious, has anyone else ever seen anything like this?

 

[SQLBill]

Was/is there an A table on the server you are copying E and F from?

If so,
Look for triggers on Tables E and F, it's possible there's a trigger that is 'firing' and causing data in A to be deleted. This is more likely if the answer to the question is YES.

-SQLBill

 

[ecobb]

Hey SQLBill, both databases are identical, I just built E and F in the Dev. database and transferred it to the other one, and there are no triggers on any of the tables in either database.

 

[TheBugSlayer]

I am a bit curious. This behaviour seems to indicate that an action is being TRIGGERed on table A when you import E&F to another Server2. But what kind of action would that be? No matter what method you use to transfer the data, no modification is taking place on tables A , E and F on Server1; what is happening is an INSERT in the tables E&F on Server2. Unless there are triggers FOR SELECT.

When you fix your problem let us know what caused it.

Thanks.

 

[ecobb]

Normally I would agree with you about the triggers. However, this is a new database that I just started building a couple of weeks ago. So far there are only 7 tables (yeah, I'm taking my time) and 3 Stored Procedures and that's all. No triggers, no views, no UDFs...just the tables and procedures. None of the procedures do any deletes, they mostly just select info from various tables, do some calculations, then return the results.

The only "fix" I had was to restore from the backup I made right before I tried the import, then script the tables and run it Query Analyzer.

Hopefully I'll get a chance to work with it some more tomorrow and find out what went wrong (or what I did wrong).

Thanks.

 

[JayKusch]

How are you validating what records from table A had been deleted.

Thanks

J. Kusch

 

[ecobb]

Actually, I didn't have time to validate what was missing. There were 401 records in table A just before I did the import, and 298 records immediately after the import. When I found that, I had to restore the database before anyone tried to use it. I have before/after backups of the database and hopefully I'll get a chance to set them up today and start checking out all of the details to try and pinpoint what happend.

Thanks