Restore DataBase.bak without affecting triggers Current SQL SERVER

Navigation
0

How is a DataBase.bak in SQL Server 2008 R2 overwritten? While I have the Triggers in the database that is working. I need to do a restoration of it without affecting the triggers of the old database, that is to say that only the records of the base data remain.

In other words, there are more than 5000 Triggers in the current database, while the base to be restored DOES NOT have one of the Triggers.

I REPEAT are 5000 Scripts, do not you think it's a bit of annoyance? that's why I ask here. I've been searching and I did not get any response. I just want to know if you can restore only the tables WITHOUT the Triggers.

    
asked by Juan Carlos Villamizar Alvarez 20.09.2016 в 23:38
source

1 answer

1

What I would do would be to remove the triggers script before doing the restore and then execute them to create them again, to get the triggers two options occur to me:

1.- You can get the triggers with this script (tested in SQL Server 2012) 

SELECT
   ServerName   = @@servername,
   DatabaseName = db_name(),
   SchemaName   = isnull( s.name, '' ),
   TableName    = isnull( o.name, 'DDL Trigger' ),
   TriggerName  = t.name, 
   Defininion   = object_definition( t.object_id )

FROM sys.triggers t
   LEFT JOIN sys.all_objects o
      ON t.parent_id = o.object_id
   LEFT JOIN sys.schemas s
      ON s.schema_id = o.schema_id
ORDER BY 
   SchemaName,
   TableName,
   TriggerName

2.- Right click on the bd select Tasks> Generate Scripts> Next> Select tables > In 'Advance' select create triggers = true, these will appear at the end of your script (if everything goes as in my tests), disabling the information you do not need like primary keys or foreign keys

    
answered by 21.06.2017 в 22:09
JSONArray eliminate duplication- Drag and drop in Sublime Text