SQL Server script to replicate all data from a remote database to a local database.

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!';