--创建同步处理步骤 exec msdb.dbo.sp_add_jobstep @job_id=@jobid, @step_name=N'数据同步', @subsystem='TSQL', @command=N' --主数据库中进行日志备份 backup log db_test_primary to disk=''d:\test_log_primary.bak'' with format
--添加目录服务器 exec msdb.dbo.sp_add_jobserver @job_ID=@jobid, @server_name=N'(local)' go --通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成 --下面开始测试是否能实现同步 create table db_test_primary.dbo.db_test_primary3(id int) go insert db_test_primary.dbo.db_test_primary3 select top 5000 id from dbo.sysobjects waitfor delay '00:01:30' go--查询一下备用数据库,看看同步是否成功select * from db_test_second.dbo.db_test_primary3 drop table db_test_primary.dbo.db_test_primary3 --最后删除测试环境 drop database msdb.dbo.sp_delete_job @job_name=N'数据同步' 控制好时间!前提是先把日志备份传到远程服务器上!
/*
作者:邹建
*/ /*--同步两个数据库的示例 有数据
srv1.库名..author有字段:id,name,phone,
srv2.库名..author有字段:id,name,telphone,adress 要求:
srv1.库名..author增加记录则srv1.库名..author记录增加
srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新
--*/ --大致的处理步骤
--1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步
exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip'
exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'
go --2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动
我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动
go
--然后创建一个作业定时调用上面的同步处理存储过程就行了 企业管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句: exec p_process
--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.实现同步处理的方法2,定时同步 --在srv1中创建如下的同步处理存储过程
create proc p_process
as
--更新修改过的数据
update b set name=i.name,telphone=i.telphone
from srv2.库名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone) --插入新增的数据
insert srv2.库名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.库名.dbo.author where id=i.id) --删除已经删除的数据(如果需要的话)
delete b
from srv2.库名.dbo.author b
where not exists(
select * from author where id=b.id)
go
/*不同服务器数据库之间的数据操作*/--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例
select * from ITSV.数据库名.dbo.表名 --导入示例
select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset --查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表 --更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A --3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
连接服务器参考这个:
http://blog.csdn.net/sdhdy/archive/2009/05/25/4215311.aspx
数据库JOB参考这个:
http://blog.csdn.net/sdhdy/archive/2009/04/26/4126359.aspx
>>>>>>soft_wsx>>>>>>
--数据备份与还原>>同步备用服务器
--1、完全备份主数据库
--2、使用带SANDBY子句的RESTORE DATABASE语句从主数据库的完全备份中还原生成备用数据库
--3、创建作业,使用BACKUP LOG定时对主数据库时行日志备份
--4、创建作业,使用带STANDBY子句的RESTORE LOG语句定时从主数据库的日志备份中恢复数据到备用数据库
--5、主数据库不可用时,使用带RECOVERY子句的RESTORE LOG语句使备用数据库升级为主数据库
----具体代码及说明如下
---->>首先、创建一个淙用的数据库(主数据库)
create database db_test_primary
on
(
name=db_test_primary_data,
filename='d:\db_test_primary_data.mdf')
log on
(
name='db_test_primary_log',
filename='d:\db_test_primary_log.ldf'
)
go--在主数据库上创建表
create TABLE db_test_primary.dbo.db_test_primary1(id int)
insert db_test_primary.dbo.db_test_primary1 select top 1000 ID from dbo.sysobjects
go
--对主数据库进行备份
backup database db_test_primary
to disk='d:\db_test_primary_data.bak'
with format
go--通过主数据库备份文件创建备用数据库(演示主数据库与这个备用数据库之间的同步)
restore database db_test_second
from disk='d:\db_test_primary_data.bak'
with replace,
standby='d:\db_test_primary_bak.ldf',
move 'db_test_primary_data' to 'd:\db_test_second_data.mdf',
move 'db_test_primary_log' to 'd:\db_test_second_log.ldf'
--启动SQL AGENT服务(需要通过作业定时将主数据库同步到备用数据库)
exec master.dbo.xp_cmdshell 'net start sqlserveragent',no_output
go--创建主服务数据库与备用服务器数据库之间同步的作业
declare @jobid uniqueidentifier
exec msdb.dbo.sp_add_job
@job_id=@jobid output,
@job_name=N'数据同步'
--创建同步处理步骤
exec msdb.dbo.sp_add_jobstep
@job_id=@jobid,
@step_name=N'数据同步',
@subsystem='TSQL',
@command=N'
--主数据库中进行日志备份
backup log db_test_primary
to disk=''d:\test_log_primary.bak''
with format
--备用数据中还原主数据库的日志备份(应用主数据库中的最新变化)
--注:实际应该对主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立,
--并且备份文件应该放在主服务器和备用服务器都能访问的共享目录中
restore log db_test_second
from disk=''d:\test_log_primary.bak''
with standby=''d:\test_log.ldf''',
@retry_attempts=5,
@retry_interval=5
--创建调度
exec msdb.dbo.sp_add_jobschedule
@job_id=@jobid,
@name='时间安排',
@freq_TYPE=4,
@freq_interval=1,
@freq_subday_type=0x4,
@freq_subday_interval=1
--添加目录服务器
exec msdb.dbo.sp_add_jobserver
@job_ID=@jobid,
@server_name=N'(local)'
go
--通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成
--下面开始测试是否能实现同步
create table db_test_primary.dbo.db_test_primary3(id int)
go
insert db_test_primary.dbo.db_test_primary3 select top 5000 id from dbo.sysobjects
waitfor delay '00:01:30'
go--查询一下备用数据库,看看同步是否成功select * from db_test_second.dbo.db_test_primary3
drop table db_test_primary.dbo.db_test_primary3
--最后删除测试环境
drop database msdb.dbo.sp_delete_job
@job_name=N'数据同步'
控制好时间!前提是先把日志备份传到远程服务器上!