litera/ SQL code versioning.md

## SQL code versioning.md

      
    Raw
  

               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
	-- 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;
	-- 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);
	-- 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;
	@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