Copia de seguridad de módulos SQL mediante un disparador DDL
Haga una copia de seguridad de sus módulos SQL en una tabla para conservar las versiones anteriores o use un disparador para rastrear o sincronizar los cambios estructurales de los objetos de la base de datos
Trabajo manual o cronometrado
Ejecute el siguiente script para habilitar la copia de seguridad de su código SQL. Realiza una copia de seguridad de los siguientes elementos en una tabla;
- Procedimientos almacenados
- Todos los tipos de funciones
- Puntos de vista
- disparadores
Cree un trabajo que llame a this con el nombre de la base de datos para permitir copias de seguridad periódicas. Esto también funciona entre servidores para que todo su código se pueda almacenar en un solo lugar.
SQL
CREATE TABLE dbo.SQLModules([System] varchar(50) NOT NULL,[Schema] nvarchar(50) NULL,ObjectName nvarchar(200) NULL,[object_id] int NOT NULL,ChangeDate datetime NULL,[definition] nvarchar(max) NOT NULL)GOCREATE CLUSTERED INDEX CDX_SQLModules ON [dbo].[SQLModules](ChangeDate,System,object_id)GOCREATE PROC dbo.SQLModules_Backup(@DB NVARCHAR(50),@Server NVARCHAR(50)=NULL) AS BEGINDECLARE @SQL NVARCHAR(MAX)='INSERT INTO SQLModulesSELECT '''+ISNULL(@Server+'.','')+@DB+''' System,s.name,o.name,m.object_id,GETDATE() ChangeDate,m.definitionFROM '+ISNULL(@Server+'.','')+@DB+'.sys.all_sql_modules mINNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.all_objects o ON o.object_id=m.object_idINNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.schemas s ON s.schema_id=o.schema_idLEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY System,object_id ORDER BY ChangeDate DESC) RowNumberFROM SQLModules) ltWHERE RowNumber=1) l ON l.object_id=m.object_id AND l.System='''+ISNULL(@Server+'.','')+@DB+'''AND m.definition COLLATE Latin1_General_CI_AS=l.definition COLLATE Latin1_General_CI_ASWHERE m.object_id>0 AND l.object_id IS NULL AND m.definition IS NOT NULL'EXEC sp_executesql @SQLENDGOUso de un disparador DDL
Mientras que la versión manual/programada funciona para algunos, otros pueden necesitar una versión más robusta. El siguiente código utiliza la funcionalidad de activación de DDL introducida en SQL Server 2005 y tiene una opción de sincronización opcional que funciona en todos los servidores si es necesario. Hemos utilizado nuestro patrón estándar de creación de una base de datos de utilidades, pero puede funcionar igualmente bien en las bases de datos maestras o personalizadas.
SQL
CREATE TABLE [dbo].[DDLEvents]([EventDate] [datetime] NOT NULL DEFAULT (getutcdate()),[EventType] [nvarchar](64) NULL,[EventDDL] [nvarchar](max) NULL,[DatabaseName] [nvarchar](255) NULL,[SchemaName] [nvarchar](255) NULL,[ObjectName] [nvarchar](255) NULL,[HostName] [varchar](64) NULL,[IPAddress] [varchar](32) NULL,[ProgramName] [nvarchar](255) NULL,[LoginName] [nvarchar](255) NULL)SQL Trigger
CREATE TRIGGER [Code_Watch] ON DATABASEFOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_SCHEMA, DROP_SCHEMAAS BEGINSET ANSI_NULLS ON;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET ARITHABORT ON;SET CONCAT_NULL_YIELDS_NULL ON;SET NUMERIC_ROUNDABORT OFF;SET QUOTED_IDENTIFIER ON;DECLARE @EventData XML = EVENTDATA();DECLARE @ip VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID);INSERT INTO dbo.DDLEvents(EventType,EventDDL,DatabaseName,SchemaName,ObjectName,HostName,IPAddress,ProgramName,LoginName)SELECT@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),DB_NAME(),@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),HOST_NAME(),@ip,PROGRAM_NAME(),SUSER_SNAME();
--Optional syncronisation option, see below--DECLARE @DB NVARCHAR(MAX)=DB_NAME(),@SQL NVARCHAR(MAX)=@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')--EXEC {server}.{database}.dbo.SyncCode @DB,@SQL--End Optional syncronisation optionENDGOENABLE TRIGGER [Code_Watch] ON DATABASEGOSQL Sync
CREATE PROC SyncCode(@DB NVARCHAR(MAX),@SQL NVARCHAR(MAX)) AS BEGIN EXEC ('use '+@DB+'; exec sp_executesql N'''+@SQL+''' ') END