SQL Database development automated scripts

SQL code versioning.md

Version following TSQL files

• 00 Backup Data.sql - automated
• 01 Drop Model.sql - automated
• 02 Create Model.sql
• 03 Functions.sql
• 04 Procedures.sql
• 05 Static Data.sql
• 06 Test Data.sql
• 07 Restore Data.sql - automated

This Gist includes only automated ones that can be used with any project.

00 Backup Data (SQL Azure).sql

-- Drop any existing Backup tables

set nocount on;

declare @sql varchar(max) = '';

select @sql = @sql + 'print ''Dropping backup table dbo.[' + o.name + ']'';drop table dbo.[' + o.name + '];'
from sys.objects o
where o.type = 'U' and left(o.name, 7) = 'Backup_'
order by o.create_date asc;

exec(@sql);


/*----------------------------------*/
/* BACKUP SCRIPT ADJUSTED FOR AZURE */
/*----------------------------------*/

	-- Create backup tables

	print '';
	set @sql = '';

	with
	TableColumns (TableName, ColumnName, ColumnType, Nullable, order1, order2)
	as (
		select
			o.name,
			c.name,
			case when c.max_length = t.max_length then t.name else t.name + '(' + iif(c.max_length = -1,'max',cast(c.max_length as varchar)) + ')' end as Type,
			c.is_nullable,
			o.create_date,
			c.column_id
		from sys.objects o
			join sys.columns c
				join sys.types t
				on (t.user_type_id = c.user_type_id)
			on (c.object_id = o.object_id)
		where
			o.type = 'U' and
			left(o.name, 7) != 'Backup_'
	),
	Concatenated (TableName, Columns, order1)
	as (
		select
			tcO.TableName,
			stuff((
				select ',' + ColumnName + ' ' + ColumnType + (case Nullable when 0 then ' not ' else ' ' end) + 'null'
				from TableColumns tcI
				where tcI.TableName = tcO.TableName
				order by order1, order2
				for xml path('')
			), 1, 1, ''),
			tcO.order1
		from TableColumns tcO
		group by tcO.TableName, tcO.order1
	)
	select @sql = @sql + 'print ''Creating backup table dbo.[Backup_' + TableName + ']'';create table dbo.[Backup_' + TableName +'] (' + Columns + ');'
	from Concatenated
	order by order1;

	exec(@sql);

	-- Create clustered indices on backup tables

	print '';
	set @sql = '';

	with
	IndexColumns (TableName, ColumnName, order1, order2)
	as (
		select o.name, c.name, o.create_date, ic.index_column_id
		from sys.indexes i
			join sys.objects o
			on (o.object_id = i.object_id)
			join sys.columns c
			on (c.object_id = o.object_id)
			join sys.index_columns ic
			on ((ic.column_id = c.column_id) and (ic.object_id = o.object_id) and (ic.index_id = i.index_id))
			join sys.types t
			on (t.user_type_id = c.user_type_id)
		where
			o.type = 'U' and
			left(o.name, 7) != 'Backup_' and
			i.type = 1 -- CLUSTERED
	),
	Concatenated (TableName, Columns, order1)
	as (
		select
			icO.TableName,
			stuff((
				select ',' + icI.ColumnName
				from IndexColumns icI
				where icI.TableName = icO.TableName
				order by order1, order2
				for xml path('')
			), 1, 1, ''),
			icO.order1
		from IndexColumns icO
		group by icO.TableName, icO.order1
	)
	select @sql = @sql + 'print ''Creating clustered index on dbo.[Backup_' + TableName + ']'';create clustered index IDX_Backup_' + TableName + '_Clustered on dbo.[Backup_' + TableName + '] (' + Columns + ');'
	from Concatenated
	order by order1;

	exec(@sql);

	-- Backup data

	print '';
	set @sql = '';

	select @sql = @sql + 'print ''Backing up data for dbo.[' + o.name + ']'';insert dbo.[Backup_' + o.name + '] select * from dbo.[' + o.name + '];'
	from sys.objects o
	where
		o.type = 'U' and
		left(o.name, 7) != 'Backup_'
	order by o.create_date;

	exec(@sql);

set nocount off;

00 Backup Data (SQL Server).sql

-- Drop any existing Backup tables

set nocount on;

declare @sql varchar(max) = '';

select @sql = @sql + 'print ''Dropping backup table dbo.[' + o.name + ']'';drop table dbo.[' + o.name + '];'
from sys.objects o
where o.type = 'U' and left(o.name, 7) = 'Backup_'
order by o.create_date asc;

exec(@sql);


-- Backup all existing data into Backup tables

set @sql = '';

select @sql = @sql + 'print ''Backing up table dbo.[' + o.name + ']'';select * into dbo.[Backup_' + o.name + '] from dbo.[' + o.name + '];'
from sys.objects o
where o.type = 'U' and left(o.name, 7) != 'Backup_'
order by o.create_date asc;

exec(@sql);

set nocount off;

01 Drop Model.sql

-- Drop all user defined objects in reverse order they were created

print '';
declare @sql varchar(max);

with types (type, name) as
(
	select 'FN', 'function' union all	-- scalar function
	select 'IF', 'function' union all	-- inline table function
	select 'TF', 'function' union all	-- table function
	select 'P', 'procedure' union all	-- stored procedure
	select 'TR', 'trigger' union all	-- SQL DML trigger
	select 'U', 'table' union all		-- user table
	select 'V', 'view'					-- view
)
select @sql = isnull(@sql, '') + 'print ''Dropping ' + t.name + ' dbo.[' + o.name + ']'';drop ' + t.name + ' dbo.[' + o.name + '];'
from sys.objects o
	join types t
	on (t.type = o.type)
where left(o.name, 7) != 'Backup_'
order by o.create_date desc

-- drop all
exec (@sql);

07 Restore Data.sql

-- Restore backup data

set nocount on;

print '';
declare @sql varchar(max) = '';
declare @error bit = 0;

-- check that no columns have been removed from existing tables
with
OldColumns (B, TableName, ColumnName)
as (
	select 1, substring(o.name, 8, 100), c.name
	from sys.objects o
		join sys.columns c
		on c.object_id = o.object_id
	where o.type = 'U' and left(o.name, 7) = 'Backup_'
),
NewColumns (TableName, ColumnName)
as (
	select o.name, c.name
	from sys.objects o
		join sys.columns c
		on c.object_id = o.object_id
	where o.type = 'U' and left(o.name, 7) != 'Backup_'
)
select @sql = @sql + 'print ''Columns removed in table dbo.[' + o.TableName + ']'';'
from OldColumns o
	left join NewColumns n
	on ((n.TableName = o.TableName) and (n.ColumnName = o.ColumnName))
where n.ColumnName is null

select @error = iif(@@rowcount > 0, 1, 0);

select @sql = @sql + iif(@error = 1, 'throw 50000, ''Data not restored due to table differences.'', 0;', 'print ''Starting data restore...'';');
exec(@sql);

print '';
select @sql = iif(@error = 1, null, ''); -- Prevents restoring data in case of missing columns

-- restore data
with
TableColumns (TableName, ColumnName, TableOrder, ColumnOrder)
as (
	select substring(o.name, 8, 100), c.name, o.create_date, c.column_id
	from sys.objects o
		join sys.columns c
		on (c.object_id = o.object_id)
	where o.type = 'U' and left(o.name, 7) = 'Backup_'
),
ConcatenatedColumns (TableName, AllColumns)
as (
	select
		tc.TableName,
		stuff((
			select ',' + tc1.ColumnName
			from TableColumns tc1
			where tc1.TableName = tc.TableName
			order by tc1.TableOrder asc, tc1.ColumnOrder asc
			for xml path('')
		), 1, 1, '')
	from TableColumns tc
	group by tc.TableName
)
select @sql = @sql + 'print ''Restoring data for table dbo.[' + o.name + ']'';begin try set identity_insert dbo.[' + o.name + '] on end try begin catch end catch;insert dbo.[' + o.name + '] (' + cc.AllColumns + ') select * from dbo.[Backup_' + o.name + '];begin try set identity_insert dbo.[' + o.name + '] off end try begin catch end catch;'
from sys.objects o
	join ConcatenatedColumns cc
	on cc.TableName = o.name
where o.type = 'U'
order by o.create_date asc;

set @sql = isnull(@sql, '');
exec(@sql);

set nocount off;

Recreate Database.bat

@cd "c:\path\to\folder\with\db\scripts"
@sqlcmd -S SERVER -d DATABASENAME -U SQLUSER -P PASSWORD -i "01 Drop Model.sql","02 Create Model.sql","03 Functions.sql","04 Procedures.sql","05 Static Data.sql","06 Test Data.sql"
@pause

Upgrade Database.bat

@cd "c:\path\to\folder\with\db\scripts"
@sqlcmd -S SERVER -d DATABASENAME -U SQLUSER -P PASSWORD -i "00 Backup Data.sql","01 Drop Model.sql","02 Create Model.sql","03 Functions.sql","04 Procedures.sql","07 Restore Data.sql"
@pause