Back

Topic

[KB868]REPLICATION WITH 1 SQL SERVER EXPRESS AND 1 SQL SERVER STANDARD EDITION

Tags: Replication, SQL

Historical data
6 years ago
By RM
Options
Print
Applies to:

PcVue all versions. SQL Server Express 2008 R2
Summary:

One possible and tested way to backup historical data from SQL Express to SQL Standard.
Originator:AC
Details:

The following script must run in the SQL Standard instance with a job every 30 minutes. The script replicates data from DATABASE2 to DATABASE1.

INSERT INTO [DATABASE1].[dbo].[TRENDTABLE1]
([Chrono]
,[Name]
,[Value]
,[Quality]
,[TS])

(SELECT [Chrono]
,[Name]
,[Value]
,[Quality]
,[TS]

FROM [DATABASE2].[dbo].[TRENDTABLE1]
WHERE (([Chrono]>=[dbo].[fn_GetUTCFiletimeFromLocalDatetime](CONVERT(datetime,GETDATE(),121))-72000000000)
AND([Chrono]<=[dbo].[fn_GetUTCFiletimeFromLocalDatetime](CONVERT(datetime,GETDATE(),121))-36000000000)
AND [Name]<>’System.HDS.PendingRecords’)
EXCEPT
SELECT [Chrono]
,[Name]
,[Value]
,[Quality]
,[TS]

FROM [DATABASE1].[dbo].[TRENDTABLE1]
WHERE (([Chrono]>=[dbo].[fn_GetUTCFiletimeFromLocalDatetime](CONVERT(datetime,GETDATE(),121))-72000000000)
AND([Chrono]<=[dbo].[fn_GetUTCFiletimeFromLocalDatetime](CONVERT(datetime,GETDATE(),121))-36000000000)
AND [Name]<>’System.HDS.PendingRecords’))

This script uses the library ‘AIHdsTSConversion.dll’ to convert Date to Chrono format. More details, and a zip file containing the library, are available in article KB492.

Script performance : 1400 records per second (environment dependent)

Additional Info

Created on: 08 Jun 2018 Last update: 30 May 2024