Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using MS SQL Management Studio 2012 to restore the database from a .bak file. (That file is from the backup of the database on server)

In my new database, the triggers are missing. Why did I lose the triggers?

share|improve this question
1  
A .bak file is a complete database backup (if it's a full backup and not a differential or log, which are different types). But if your restore works and finishes successfully, everything that was in the original DB will also be in the copy. So the triggers should be in the restored database. Don't have an SQL 2012 to play with now, but they should be under Tables -> Your Table Name -> Triggers. –  Marian Mar 25 '13 at 19:01
    
Restore was successfully. On server there are triggers on that location (Tables -> Your Table Name -> Triggers), but when i open the database on my local machine that triggers doesn't extst. –  Marija Todorović Mar 25 '13 at 19:18
    
What's the version of SQL Server Management Studio on your local machine? –  Carol Baker West Mar 25 '13 at 19:24
1  
How have you validated the triggers? Have you checked SELECT * FROM [foo].[sys].[triggers] where foo is the name of your database? All objects should have been restored with the database backup, as @Marian stated. –  Mike Fal Mar 25 '13 at 19:34
1  
Are you restoring the backup to a different server than where the backup was taken? –  Jon Seigel Mar 26 '13 at 2:47
show 1 more comment

2 Answers

Sounds like the backup was created before the triggers were put into the database. Make sure that the backup is current. SQL Server won't just drop the triggers when it restores the database backup.

share|improve this answer
    
No, it's not that. Backup is from last night, that database is in use for 2 years, so something else is the problem. –  Marija Todorović Mar 25 '13 at 21:08
    
Then something is removing the triggers after the database has been restored. –  mrdenny Mar 26 '13 at 7:02
add comment

A wild guess - are the triggers based on CLR code? If CLR Integration has not been enabled on the machine you are restoring to then the triggers won't work.

CLR Integration can be enabled with the following code.

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
share|improve this answer
    
I executed that query, nothing changed. Is it possible that the problem is with users on database or with privillages? –  Marija Todorović Mar 26 '13 at 16:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.