Backup SQL Modules using a DDL trigger

Backup your SQL Modules into a table to keep previous versions or use a trigger to track or synchronise structural changes of database objects

Manual or timed job

Execute the script below to enable the backup of your SQL Code. Backs up the following items into one table;

Stored Procedures
All Function Types
Views
Triggers

Create a job that calls the this with the database name to enable regular backups. This also works cross server so that all of your code can be stored in one place.

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)

GO

CREATE CLUSTERED INDEX CDX_SQLModules ON [Utilities].[dbo].[SQLModules](ChangeDate,System,object_id)

GO

CREATE PROC dbo.SQLModules_Backup(@DB NVARCHAR(50),@Server NVARCHAR(50)=NULL) AS BEGIN

DECLARE @SQL NVARCHAR(MAX)='INSERT INTO SQLModules

SELECT '''+ISNULL(@Server+'.','')+@DB+''' System,s.name,o.name,

m.object_id,

GETDATE() ChangeDate,

m.definition

FROM '+ISNULL(@Server+'.','')+@DB+'.sys.all_sql_modules m

INNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.all_objects o ON o.object_id=m.object_id

INNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.schemas s ON s.schema_id=o.schema_id

LEFT JOIN (

SELECT * FROM (

SELECT *,ROW_NUMBER() OVER (PARTITION BY System,object_id ORDER BY ChangeDate DESC) RowNumber

FROM SQLModules) lt

WHERE 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_AS

WHERE m.object_id>0 AND l.object_id IS NULL AND m.definition IS NOT NULL'

EXEC sp_executesql  @SQL

END

GO

Using a DDL Trigger

While the manual/scheduled version works for some, others may need a more robust version. The code below uses the DDL trigger functionality introduced in SQL Server 2005, and has an optional synchronisation option that works across server if required. We have used our standard pattern of creating a utilities database, but it can work equally well in the master or custom databases.

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 DATABASE
FOR 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_SCHEMA
AS BEGIN
SET 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 Utilities.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].Utilities.dbo.SyncCode @DB,@SQL
--End Optional syncronisation option
END
GO
ENABLE TRIGGER [Code_Watch] ON DATABASE
GO

SQL Sync

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