Zálohujte moduly SQL pomocí spouštěče DDL

Zálohujte své moduly SQL do tabulky, abyste zachovali předchozí verze, nebo použijte spouštěč ke sledování nebo synchronizaci strukturálních změn databázových objektů

Manuální nebo časovaná práce

Spuštěním níže uvedeného skriptu povolte zálohování kódu SQL. Zálohuje následující položky do jedné tabulky;

Uložené procedury
Všechny typy funkcí
Pohledy
Spouštěče

Chcete-li povolit pravidelné zálohování, vytvořte úlohu, která volá toto s názvem databáze. Funguje to také mezi servery, takže veškerý váš kód může být uložen na jednom místě.

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

Použití spouštěče DDL

Zatímco pro některé funguje manuální/plánovaná verze, jiní mohou potřebovat robustnější verzi. Níže uvedený kód používá funkci spouštěče DDL zavedenou v SQL Server 2005 a má volitelnou možnost synchronizace, která v případě potřeby funguje napříč serverem. Použili jsme náš standardní vzor vytváření databáze utilit, ale může fungovat stejně dobře v hlavní nebo vlastní databázi.

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