SQL Server script to replicate all data from a remote database to a local database.
/* | |
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!'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment