2000 DB restored on 2005...missing triggers???

[theoryofben]

I backed up a database from 2000 and restored it on 2005. Everything seemed to go fine...except that I am missing triggers. I had ~1000 triggers and I'm missing 62 of them. I also have 10 triggers that did not exist in the backed up database (although they used to be in it). How can this be?

My first thought is that the triggers that showed up out of nowhere must have been orphaned in 2000 and when 2005 restored the DB, it found them and "reinstated" them.

But what about the missing triggers?

Any ideas?

________________________________________________
[sub]"I have not failed. I've just found 10,000 ways that won't work."-Thomas Edison[/sub]

 

[calico]

Hi,

Did you execute "DBCC Checkdb" before the backup and after the restore to be sure that the BD is not corrupted ?

 

[theoryofben]

I have to admit...I didn't. We're still running the old server alongside the new with merge replication keeping things in sync...so I guess I can run a checkdb on the 2000 box and see what comes up.

I just don't understand how this could happen without some sort of error being thrown.

Thanks for the help.

________________________________________________
[sub]"I have not failed. I've just found 10,000 ways that won't work."-Thomas Edison[/sub]

 

[calico]

Hi,

Hummm. Merge replication !
I am not familar with merge replication

I think it is normal that you also have 10 triggers that did not exist in the backed up database. I think those triggers are created by the replication process.

For the "missing 62 of them", check in the old server if trigger are created with FOR REPLICATION option set ?

From book online
-------------------------------------------------------
.... The main area to consider is triggers. For example, if you define an insert trigger with the NOT FOR REPLICATION option set, all user inserts fire the trigger, but inserts from replication agents do not. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table....
-------------------------------------------------------

I hope this can help

calico

 

[theoryofben]

You are correct, when you add a merge subscription, it adds 3 triggers...but these are not what is missing. I excluded replication triggers in my query.

Anyway, it seems this is just a symptom of a much bigger problem. Last night, the server suddenly started dropping and re-adding tables alphabetically. It made it to the "E"'s before something crashed and stopped it. Luckily we still have all of the data on the old server.

There is no information in the logs to show why this happened. We are just going to blow the server away and reinstall.....what a headache.

Thanks for your help...I'll post any info I find about the problem.

________________________________________________
[sub]"I have not failed. I've just found 10,000 ways that won't work."-Thomas Edison[/sub]