gabrielstelmach/data_replication.sql

## data_replication.sql
/*
Replicates all data from the remote database to the local database. The local contents are removed, all current local data is erased and not recoverable.
You have to set the value for @LINKED_SERVER_NAME as your local Linked Server name for the remote SQL Server instance.
*/
print 'Starting replication...';
--Control for structure alterations over the process
declare @CONTROL_TABLES table
(
  TABLE_NAME varchar(255) NOT NULL,
	CONSTRAINT_NAME varchar(255) NULL,
	STATUS_ENABLED int NULL,
  HAS_IDENTITY int NULL
);
--Local linked server alias
declare @LINKED_SERVER_NAME varchar(255);
--Database name (both "from" and "to" must be same name)
declare @DATABASE_COPY varchar(255);

/**
Define controllers
**/
print 'Setting controllers...';
set @LINKED_SERVER_NAME = '127.0.0.1,11433';
--Using local database name
select @DATABASE_COPY = db_name();

--Identify all user tables with constraints
insert into @CONTROL_TABLES (TABLE_NAME, CONSTRAINT_NAME, STATUS_ENABLED, HAS_IDENTITY)
	select
			O2.NAME,
			O.NAME,
			case when ((C.STATUS & 0x4000)) = 0 then 1 else 0 end,
			null
	from SYS.SYSCONSTRAINTS C
			inner join SYS.SYSOBJECTS O on O.ID = C.CONSTID
			inner join SYS.SYSOBJECTS O2 on O2.ID = O.PARENT_OBJ
	where
		(O2.NAME in (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where (TABLE_TYPE = 'BASE TABLE')))
		and (O.XTYPE in ('C', 'F'))
;
--Identify all user tables with identity
update A
set
	HAS_IDENTITY = 1
from
	@CONTROL_TABLES as A
	inner join (select
			T.NAME as NAME
		from
			SYS.SCHEMAS as S
			inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID
		where exists
		(
		  select 1 from SYS.IDENTITY_COLUMNS
			where OBJECT_ID = T.OBJECT_ID
		)) as B on A.TABLE_NAME = B.NAME
;
insert into @CONTROL_TABLES (TABLE_NAME, HAS_IDENTITY)
	select
		T.NAME,
		1
	from
		SYS.SCHEMAS as S
		inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID
	where
		exists (select 1 from SYS.IDENTITY_COLUMNS where OBJECT_ID = T.OBJECT_ID)
		and (T.NAME not in (select TABLE_NAME from @CONTROL_TABLES))
;

/**
For each table, disable its constraints
**/
print 'Disabling constraints...';
declare NOCHECK_STATEMENTS_CURSOR cursor FAST_FORWARD for
	select
		distinct 'alter table ' + TABLE_NAME + ' nocheck constraint ' + CONSTRAINT_NAME + ';'
	from
		@CONTROL_TABLES
	where
		(STATUS_ENABLED = 1);
declare @NOCHECK_DISABLE_STATEMENT varchar(255)
open NOCHECK_STATEMENTS_CURSOR
fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		print @NOCHECK_DISABLE_STATEMENT
		exec(@NOCHECK_DISABLE_STATEMENT)
	end
	fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT
end
deallocate NOCHECK_STATEMENTS_CURSOR;

/**
For each table, disable its triggers
**/
print 'Disabling triggers...';
declare DISABLE_STATEMENTS_CURSOR cursor fast_forward for
	select
    'disable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';'
  from
    SYSOBJECTS
    inner join SYS.TABLES T on SYSOBJECTS.PARENT_OBJ = T.OBJECT_ID
    inner join SYS.SCHEMAS S on T.SCHEMA_ID = S.SCHEMA_ID
  where
    (SYSOBJECTS.TYPE = 'TR');
declare @DISABLE_STATEMENT varchar(255)
open DISABLE_STATEMENTS_CURSOR
fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		print @DISABLE_STATEMENT
		exec(@DISABLE_STATEMENT)
	end
	fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT
end
deallocate DISABLE_STATEMENTS_CURSOR;

/**
Remove all local contents
**/
print 'Removing local contents...';
declare DELETE_STATEMENTS_CURSOR cursor fast_forward for
	select
		'delete from ' + TABLE_NAME + ';'
	from
		INFORMATION_SCHEMA.TABLES
	where
		(TABLE_TYPE = 'BASE TABLE')
	order by TABLE_NAME
	;
declare @DELETE_STATEMENT varchar(255)
open DELETE_STATEMENTS_CURSOR
fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		print @DELETE_STATEMENT
		exec(@DELETE_STATEMENT)
	end
	fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT
end
deallocate DELETE_STATEMENTS_CURSOR;

/**
Copy data from remote server to local environment
**/
print 'Copying data from remote server...';
--All non identity tables
declare COPY_STATEMENTS_CURSOR cursor fast_forward for
	select
		'insert into ' + TABLE_NAME + ' select * from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + TABLE_NAME + ';'
	from
		INFORMATION_SCHEMA.TABLES
	where
		(TABLE_TYPE = 'BASE TABLE')
		and (TABLE_NAME not in (select TABLE_NAME from @CONTROL_TABLES where (HAS_IDENTITY = 1)))
	order by TABLE_NAME
	;
declare @COPY_STATEMENT varchar(max);
open COPY_STATEMENTS_CURSOR
fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		print @COPY_STATEMENT
		exec(@COPY_STATEMENT)
	end
	fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT
end
deallocate COPY_STATEMENTS_CURSOR;

--All identity tables
declare @TABLE_NAME varchar(255);
declare @COLUMNS_NAME varchar(max);
declare COPY_IDENTITY_STATEMENTS_CURSOR cursor fast_forward for
	select
		distinct(TABLE_NAME)
	from
		@CONTROL_TABLES
	where
		(HAS_IDENTITY = 1)
	order by TABLE_NAME
	;
declare @COPY_IDENTITY_STATEMENT varchar(max);
open COPY_IDENTITY_STATEMENTS_CURSOR
fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		set @COLUMNS_NAME = null;
		select @COLUMNS_NAME = coalesce(@COLUMNS_NAME + ',', '') + COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME = @TABLE_NAME);
		set @COPY_IDENTITY_STATEMENT = 'set identity_insert ' + @TABLE_NAME + ' on;';
		set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'insert into ' + @TABLE_NAME + ' (' + @COLUMNS_NAME + ') select ' + @COLUMNS_NAME + ' from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + @TABLE_NAME + ';'
		set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'set identity_insert ' + @TABLE_NAME + ' off;';

		print @COPY_IDENTITY_STATEMENT;
		exec(@COPY_IDENTITY_STATEMENT);
	end
	fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME
end
deallocate COPY_IDENTITY_STATEMENTS_CURSOR;

/**
For each table, enable its triggers
**/
print 'Enabling triggers...';
declare ENABLE_STATEMENTS_CURSOR cursor fast_forward for
	select
    'enable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';'
  from
    SYSOBJECTS
    inner join SYS.TABLES T on SYSOBJECTS.PARENT_OBJ = T.OBJECT_ID
    inner join SYS.SCHEMAS S on T.SCHEMA_ID = S.SCHEMA_ID
  where
    (SYSOBJECTS.TYPE = 'TR');
declare @ENABLE_STATEMENT varchar(255)
open ENABLE_STATEMENTS_CURSOR
fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		print @ENABLE_STATEMENT
		exec(@ENABLE_STATEMENT)
	end
	fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT
end
deallocate ENABLE_STATEMENTS_CURSOR;

/**
For each table, enable its constraints
**/
print 'Enabling constraints...';
declare CHECK_STATEMENTS_CURSOR cursor fast_forward for
	select
		'alter table ' + TABLE_NAME + ' with check check constraint ' + CONSTRAINT_NAME + ';'
	from
		@CONTROL_TABLES
	where
		(STATUS_ENABLED = 1)
	;
declare @CHECK_STATEMENT varchar(max);
open CHECK_STATEMENTS_CURSOR
fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
	begin
		print @CHECK_STATEMENT;
		exec(@CHECK_STATEMENT);
	end
	fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT
end
deallocate CHECK_STATEMENTS_CURSOR;

print '...all good!';
	/*
	Replicates all data from the remote database to the local database. The local contents are removed, all current local data is erased and not recoverable.
	You have to set the value for @LINKED_SERVER_NAME as your local Linked Server name for the remote SQL Server instance.
	*/
	print 'Starting replication...';
	--Control for structure alterations over the process
	declare @CONTROL_TABLES table
	(
	TABLE_NAME varchar(255) NOT NULL,
	CONSTRAINT_NAME varchar(255) NULL,
	STATUS_ENABLED int NULL,
	HAS_IDENTITY int NULL
	);
	--Local linked server alias
	declare @LINKED_SERVER_NAME varchar(255);
	--Database name (both "from" and "to" must be same name)
	declare @DATABASE_COPY varchar(255);

	/**
	Define controllers
	**/
	print 'Setting controllers...';
	set @LINKED_SERVER_NAME = '127.0.0.1,11433';
	--Using local database name
	select @DATABASE_COPY = db_name();

	--Identify all user tables with constraints
	insert into @CONTROL_TABLES (TABLE_NAME, CONSTRAINT_NAME, STATUS_ENABLED, HAS_IDENTITY)
	select
	O2.NAME,
	O.NAME,
	case when ((C.STATUS & 0x4000)) = 0 then 1 else 0 end,
	null
	from SYS.SYSCONSTRAINTS C
	inner join SYS.SYSOBJECTS O on O.ID = C.CONSTID
	inner join SYS.SYSOBJECTS O2 on O2.ID = O.PARENT_OBJ
	where
	(O2.NAME in (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where (TABLE_TYPE = 'BASE TABLE')))
	and (O.XTYPE in ('C', 'F'))
	;
	--Identify all user tables with identity
	update A
	set
	HAS_IDENTITY = 1
	from
	@CONTROL_TABLES as A
	inner join (select
	T.NAME as NAME
	from
	SYS.SCHEMAS as S
	inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID
	where exists
	(
	select 1 from SYS.IDENTITY_COLUMNS
	where OBJECT_ID = T.OBJECT_ID
	)) as B on A.TABLE_NAME = B.NAME
	;
	insert into @CONTROL_TABLES (TABLE_NAME, HAS_IDENTITY)
	select
	T.NAME,
	1
	from
	SYS.SCHEMAS as S
	inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID
	where
	exists (select 1 from SYS.IDENTITY_COLUMNS where OBJECT_ID = T.OBJECT_ID)
	and (T.NAME not in (select TABLE_NAME from @CONTROL_TABLES))
	;

	/**
	For each table, disable its constraints
	**/
	print 'Disabling constraints...';
	declare NOCHECK_STATEMENTS_CURSOR cursor FAST_FORWARD for
	select
	distinct 'alter table ' + TABLE_NAME + ' nocheck constraint ' + CONSTRAINT_NAME + ';'
	from
	@CONTROL_TABLES
	where
	(STATUS_ENABLED = 1);
	declare @NOCHECK_DISABLE_STATEMENT varchar(255)
	open NOCHECK_STATEMENTS_CURSOR
	fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	print @NOCHECK_DISABLE_STATEMENT
	exec(@NOCHECK_DISABLE_STATEMENT)
	end
	fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT
	end
	deallocate NOCHECK_STATEMENTS_CURSOR;

	/**
	For each table, disable its triggers
	**/
	print 'Disabling triggers...';
	declare DISABLE_STATEMENTS_CURSOR cursor fast_forward for
	select
	'disable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';'
	from
	SYSOBJECTS
	inner join SYS.TABLES T on SYSOBJECTS.PARENT_OBJ = T.OBJECT_ID
	inner join SYS.SCHEMAS S on T.SCHEMA_ID = S.SCHEMA_ID
	where
	(SYSOBJECTS.TYPE = 'TR');
	declare @DISABLE_STATEMENT varchar(255)
	open DISABLE_STATEMENTS_CURSOR
	fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	print @DISABLE_STATEMENT
	exec(@DISABLE_STATEMENT)
	end
	fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT
	end
	deallocate DISABLE_STATEMENTS_CURSOR;

	/**
	Remove all local contents
	**/
	print 'Removing local contents...';
	declare DELETE_STATEMENTS_CURSOR cursor fast_forward for
	select
	'delete from ' + TABLE_NAME + ';'
	from
	INFORMATION_SCHEMA.TABLES
	where
	(TABLE_TYPE = 'BASE TABLE')
	order by TABLE_NAME
	;
	declare @DELETE_STATEMENT varchar(255)
	open DELETE_STATEMENTS_CURSOR
	fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	print @DELETE_STATEMENT
	exec(@DELETE_STATEMENT)
	end
	fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT
	end
	deallocate DELETE_STATEMENTS_CURSOR;

	/**
	Copy data from remote server to local environment
	**/
	print 'Copying data from remote server...';
	--All non identity tables
	declare COPY_STATEMENTS_CURSOR cursor fast_forward for
	select
	'insert into ' + TABLE_NAME + ' select * from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + TABLE_NAME + ';'
	from
	INFORMATION_SCHEMA.TABLES
	where
	(TABLE_TYPE = 'BASE TABLE')
	and (TABLE_NAME not in (select TABLE_NAME from @CONTROL_TABLES where (HAS_IDENTITY = 1)))
	order by TABLE_NAME
	;
	declare @COPY_STATEMENT varchar(max);
	open COPY_STATEMENTS_CURSOR
	fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	print @COPY_STATEMENT
	exec(@COPY_STATEMENT)
	end
	fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT
	end
	deallocate COPY_STATEMENTS_CURSOR;

	--All identity tables
	declare @TABLE_NAME varchar(255);
	declare @COLUMNS_NAME varchar(max);
	declare COPY_IDENTITY_STATEMENTS_CURSOR cursor fast_forward for
	select
	distinct(TABLE_NAME)
	from
	@CONTROL_TABLES
	where
	(HAS_IDENTITY = 1)
	order by TABLE_NAME
	;
	declare @COPY_IDENTITY_STATEMENT varchar(max);
	open COPY_IDENTITY_STATEMENTS_CURSOR
	fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	set @COLUMNS_NAME = null;
	select @COLUMNS_NAME = coalesce(@COLUMNS_NAME + ',', '') + COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME = @TABLE_NAME);
	set @COPY_IDENTITY_STATEMENT = 'set identity_insert ' + @TABLE_NAME + ' on;';
	set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'insert into ' + @TABLE_NAME + ' (' + @COLUMNS_NAME + ') select ' + @COLUMNS_NAME + ' from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + @TABLE_NAME + ';'
	set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'set identity_insert ' + @TABLE_NAME + ' off;';

	print @COPY_IDENTITY_STATEMENT;
	exec(@COPY_IDENTITY_STATEMENT);
	end
	fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME
	end
	deallocate COPY_IDENTITY_STATEMENTS_CURSOR;

	/**
	For each table, enable its triggers
	**/
	print 'Enabling triggers...';
	declare ENABLE_STATEMENTS_CURSOR cursor fast_forward for
	select
	'enable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';'
	from
	SYSOBJECTS
	inner join SYS.TABLES T on SYSOBJECTS.PARENT_OBJ = T.OBJECT_ID
	inner join SYS.SCHEMAS S on T.SCHEMA_ID = S.SCHEMA_ID
	where
	(SYSOBJECTS.TYPE = 'TR');
	declare @ENABLE_STATEMENT varchar(255)
	open ENABLE_STATEMENTS_CURSOR
	fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	print @ENABLE_STATEMENT
	exec(@ENABLE_STATEMENT)
	end
	fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT
	end
	deallocate ENABLE_STATEMENTS_CURSOR;

	/**
	For each table, enable its constraints
	**/
	print 'Enabling constraints...';
	declare CHECK_STATEMENTS_CURSOR cursor fast_forward for
	select
	'alter table ' + TABLE_NAME + ' with check check constraint ' + CONSTRAINT_NAME + ';'
	from
	@CONTROL_TABLES
	where
	(STATUS_ENABLED = 1)
	;
	declare @CHECK_STATEMENT varchar(max);
	open CHECK_STATEMENTS_CURSOR
	fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT
	while (@@fetch_status <> -1)
	begin
	if (@@fetch_status <> -2)
	begin
	print @CHECK_STATEMENT;
	exec(@CHECK_STATEMENT);
	end
	fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT
	end
	deallocate CHECK_STATEMENTS_CURSOR;

	print '...all good!';