SQL-Module mit einem DDL-Trigger sichern

Sichern Sie Ihre SQL-Module in einer Tabelle, um frühere Versionen beizubehalten, oder verwenden Sie einen Trigger, um strukturelle Änderungen von Datenbankobjekten zu verfolgen oder zu synchronisieren

Manueller oder zeitgesteuerter Job

Führen Sie das folgende Skript aus, um die Sicherung Ihres SQL-Codes zu aktivieren. Sichert die folgenden Elemente in einer Tabelle;

Gespeicherte Prozeduren
Alle Funktionstypen
Ansichten
Löst aus

Erstellen Sie einen Job, der this mit dem Datenbanknamen aufruft, um regelmäßige Backups zu ermöglichen. Dies funktioniert auch serverübergreifend, sodass Ihr gesamter Code an einem Ort gespeichert werden kann.

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  @SQLENDGO

Verwenden eines DDL-Triggers

Während die manuelle/geplante Version für einige funktioniert, benötigen andere möglicherweise eine robustere Version. Der folgende Code verwendet die in SQL Server 2005 eingeführte DDL-Triggerfunktion und verfügt über eine optionale Synchronisierungsoption, die bei Bedarf serverübergreifend funktioniert. Wir haben unser Standardmuster zum Erstellen einer Dienstprogrammdatenbank verwendet, aber es kann in der Master- oder benutzerdefinierten Datenbank gleichermaßen gut funktionieren.

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 DATABASEGO

SQL Sync

CREATE PROC SyncCode(@DB NVARCHAR(MAX),@SQL NVARCHAR(MAX)) AS BEGIN   EXEC ('use '+@DB+'; exec sp_executesql N'''+@SQL+''' ')   END