Create a gist now

Instantly share code, notes, and snippets.

SQL Database development automated scripts

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.

-- 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 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;
-- 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
@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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment