14/Apr/2019

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

By Gabriel


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

view raw

data_replication.sql

hosted with ❤ by GitHub

Advertisement
This entry was posted on Sunday, April 14th, 2019 at 8:23 pm and posted in Tips. You can follow any responses to this entry through the RSS 2.0 feed.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s