通过作业,定时同步两个数据库

本文介绍了一种在SQL Server环境中实现局域网内本机与远程服务器数据同步的方法。通过创建表、触发器及存储过程等,实现了数据的新增、修改和删除同步,并通过创建作业定时执行同步任务。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

None.gif -- 定时同步服务器上的数据
None.gif--
例子:
None.gif--
测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
None.gif--
服务器上的表(查询分析器连接到服务器上创建)
None.gif
create table [ user ](id int primary key, number varchar( 4),name varchar( 10))
None.gif go
None.gif -- 以下在局域网(本机操作)
None.gif--
本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
None.gif
if exists ( select * from dbo.sysobjects where id = object_id(N ' [user] ') and OBJECTPROPERTY(id, N ' IsUserTable ') = 1)
None.gif drop table [ user ]
None.gif GO
None.gif create table [ user ](id int identity( 1, 1), number varchar( 4),name varchar( 10),state bit)
None.gif go
None.gif -- 创建触发器,维护state字段的值
None.gif
create trigger t_state on [ user ]
None.gifafter update
None.gif as
None.gif update [ user ] set state = 1
None.gif from [ user ] a join inserted b on a.id =b.id
None.gif where a.state is not null
None.gif go
None.gif
None.gif -- 为了方便同步处理,创建链接服务器到要同步的服务器
None.gif--
这里的远程服务器名为:xz,用户名为:sa,无密码
None.gif
if exists( select 1 from master..sysservers where srvname = ' srv_lnk ')
None.gif exec sp_dropserver ' srv_lnk ', ' droplogins '
None.gif go
None.gif exec sp_addlinkedserver ' srv_lnk ', '', ' SQLOLEDB ', ' xz '
None.gif exec sp_addlinkedsrvlogin ' srv_lnk ', ' false ', null, ' sa '
None.gif go
None.gif
None.gif -- 创建同步处理的存储过程
None.gif
if exists ( select * from dbo.sysobjects where id = object_id(N ' [dbo].[p_synchro] ') and OBJECTPROPERTY(id, N ' IsProcedure ') = 1)
None.gif drop procedure [ dbo ]. [ p_synchro ]
None.gif GO
None.gif create proc p_synchro
None.gif as
None.gif -- set XACT_ABORT on
None.gif--
启动远程服务器的MSDTC服务
None.gif--
exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output
None.gif

None.gif -- 启动本机的MSDTC服务
None.gif--
exec master..xp_cmdshell 'net start msdtc',no_output
None.gif

None.gif -- 进行分布事务处理,如果表用标识列做主键,用下面的方法
None.gif--
BEGIN DISTRIBUTED TRANSACTION
None.gif
-- 同步删除的数据
None.gif
delete from srv_lnk.test.dbo. [ user ]
None.gif where id not in( select id from [ user ])
None.gif
None.gif -- 同步新增的数据
None.gif
insert into srv_lnk.test.dbo. [ user ]
None.gif select id, number,name from [ user ] where state is null
None.gif
None.gif -- 同步修改的数据
None.gif
update srv_lnk.test.dbo. [ user ] set
None.gif number =b. number,name =b.name
None.gif from srv_lnk.test.dbo. [ user ] a
None.gif join [ user ] b on a.id =b.id
None.gif where b.state = 1
None.gif
None.gif -- 同步后更新本机的标志
None.gif
update [ user ] set state = 0 where isnull(state, 1) = 1
None.gif -- COMMIT TRAN
None.gif
go
None.gif
None.gif -- 创建作业,定时执行数据同步的存储过程
None.gif
if exists( SELECT 1 from msdb..sysjobs where name = ' 数据处理 ')
None.gif EXECUTE msdb.dbo.sp_delete_job @job_name = ' 数据处理 '
None.gif exec msdb..sp_add_job @job_name = ' 数据处理 '
None.gif
None.gif -- 创建作业步骤
None.gif
declare @sql varchar( 800), @dbname varchar( 250)
None.gif select @sql = ' exec p_synchro ' -- 数据处理的命令
None.gif
, @dbname = db_name() -- 执行数据处理的数据库名
None.gif

None.gif exec msdb..sp_add_jobstep @job_name = ' 数据处理 ',
None.gif @step_name = ' 数据同步 ',
None.gif @subsystem = ' TSQL ',
None.gif @database_name = @dbname,
None.gif @command = @sql,
None.gif @retry_attempts = 5, -- 重试次数
None.gif
@retry_interval = 5 -- 重试间隔
None.gif

None.gif -- 创建调度
None.gif
EXEC msdb..sp_add_jobschedule @job_name = ' 数据处理 ',
None.gif @name = ' 时间安排 ',
None.gif @freq_type = 4, -- 每天
None.gif
@freq_interval = 1, -- 每天执行一次
None.gif
@active_start_time = 00000 -- 0点执行
None.gif
go
None.gif



本文转自高海东博客园博客,原文链接:http://www.cnblogs.com/ghd258/archive/2005/10/24/260784.html,如需转载请自行联系原作者
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值