Резервное копирование модулей SQL с использованием триггера DDL

Создайте резервную копию ваших модулей SQL в таблице, чтобы сохранить предыдущие версии, или используйте триггер для отслеживания или синхронизации структурных изменений объектов базы данных.

Ручная или временная работа

Выполните приведенный ниже сценарий, чтобы включить резервное копирование вашего кода SQL. Резервирует следующие элементы в одну таблицу;

Хранимые процедуры
Все типы функций
Просмотры
Триггеры

Создайте задание, которое вызывает это с именем базы данных, чтобы включить регулярное резервное копирование. Это также работает на разных серверах, так что весь ваш код можно хранить в одном месте.

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

Использование триггера DDL

В то время как ручная/запланированная версия работает для некоторых, другим может потребоваться более надежная версия. В приведенном ниже коде используется функциональность триггера DDL, представленная в SQL Server 2005, и имеется необязательный параметр синхронизации, который при необходимости работает на сервере. Мы использовали наш стандартный шаблон создания базы данных служебных программ, но он может одинаково хорошо работать как в основной, так и в пользовательской базе данных.

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