sqlserver数据库的实时备份

利用資料庫複製技術 實現資料同步更新 複製的概念 複製是將一組資料從一個資料源拷貝到多個資料源的技術，是將一份資料發佈到多個存儲站點上的有效方式。使用複製技術，用戶可以將一份資料發佈到多台伺服器上，從而使不同的伺服器用戶都可以在許可權的許可的範圍內共用這份資料。複製技術可以確保分佈在不同地點的資料自動同步更新，從而保證資料的一致性。 SQL複製的基本元素包括 出版伺服器、訂閱伺服器、分發伺服器、出版物、文章 SQL複製的工作原理 SQL SERVER 主要採用出版物、訂閱的方式來處理複製。源資料所在的伺服器是出版伺服器，負責發表資料。出版伺服器把要發表的資料的所有改變情況的拷貝複製到分發伺服器，分發伺服器包含有一個分發資料庫，可接收資料的所有改變，並保存這些改變，再把這些改變分發給訂閱伺服器 SQL SERVER複製技術類型 SQL SERVER提供了三種複製技術，分別是： 1、快照複製（呆會我們就使用這個） 2、事務複製 3、合併複製 只要把上面這些概念弄清楚了那麼對複製也就有了一定的理解。接下來我們就一步一步來實現複製的步驟。 第一先來配置出版伺服器 (1)選中指定[伺服器]節點 (2)從[工具]下拉功能表的[複製]子功能表中選擇[發佈、訂閱伺服器和分發]命令 (3)系統彈出一個對話方塊點[下一步]然後看著提示一直操作到完成。 (4)當完成了出版伺服器的設置以後系統會為該伺服器的樹形結構中添加一個複製監視器。同時也生成一個分發資料庫(distribution) 第二創建出版物 (1)選中指定的伺服器 (2)從[工具]功能表的[複製]子功能表中選擇[創建和管理發佈]命令。此時系統會彈出一個對話方塊 (3)選擇要創建出版物的資料庫，然後單擊[創建發佈] (4)在[創建發佈嚮導]的提示對話方塊中單擊[下一步]系統就會彈出一個對話方塊。對話方塊上的內容是複製的三個類型。我們現在選第一個也就是默認的快照發佈(其他兩個大家可以去看看幫助) (5)單擊[下一步]系統要求指定可以訂閱該發佈的資料庫伺服器類型，SQLSERVER允許在不同的資料庫如 ORACLE或ACCESS之間進行資料複製。但是在這裏我們選擇運行"SQL SERVER 2000"的資料庫伺服器 (6)單擊[下一步]系統就彈出一個定義文章的對話方塊也就是選擇要出版的表 (7)然後[下一步]直到操作完成。當完成出版物的創建後創建出版物的資料庫也就變成了一個共用資料庫。 第三設計訂閱 (1)選中指定的訂閱伺服器 (2)從[工具]下拉功能表中選擇[複製]子功能表的[請求訂閱] (3)按照單擊[下一步]操作直到系統會提示檢查SQL SERVER代理服務的運行狀態，執行複製操作的前提條件是SQL SERVER代理服務必須已經啟動。 (4)單擊[完成]。完成訂閱操作。 完成上面的步驟其實複製也就是成功了。但是如何來知道複製是否成功了呢？這裏可以通過這種方法來快速看是否成功。展開出版伺服器下面的複製——發佈內容——右鍵發佈內容——屬性——擊活——狀態然後點立即運行代理程式接著點代理程式屬性擊活調度把調度設置為每一天發生，每一分鐘，在0：00：00和23：59：59之間。接下來就是判斷複製是否成功了打開C:/Program Files/Microsoft SQL Server/MSSQL/REPLDATA/unc/XIAOWANGZI_database_database下面看是不是有一些以時間做為檔案名的檔夾差不多一分中就產生一個。要是你還不信的話就打開你的資料庫看在訂閱的伺服器的指定訂閱資料庫下看是不是看到了你剛才所發佈的表

gt： /*--資料庫資料複製 將一個資料庫中的資料複製到另一個資料庫 如果某列在目標資料庫中為標識列,將不會被複製

適用範圍:資料庫結構發生了變化,想將舊資料庫進行升級 這樣就可以根據新的資料庫結構創建一個空庫,然後 將舊資料庫的所有資料複製到新庫中 /*--調用示例

exec p_copydb '源資料庫','目標資料庫' exec p_copydb 'acc_五醫','acc_演示資料8' --*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_copydb] GO

create proc p_copydb @o_dbname sysname, --要複製資料的資料庫--源資料庫 @n_dbname sysname, --接收資料的資料庫--目標資料庫 @cleardb bit=0 --清空目標資料庫 as declare @sql nvarchar(4000)

--禁用約束,防止複製時的資料衝突 set @sql='declare #tbc cursor for select name,tbname=object_name(parent_obj) from '+@n_dbname+'..sysobjects where xtype in(''C'',''F'')' exec(@sql) declare @name sysname,@tbname sysname open #tbc fetch next from #tbc into @name,@tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ['+@name+']' exec(@sql) fetch next from #tbc into @name,@tbname end close #tbc

--複製資料 declare @sql1 varchar(8000) set @sql='declare #tb cursor for select a.name from ' +@o_dbname+'..sysobjects a inner join ' +@n_dbname+'..sysobjects b on a.name=b.name where a.xtype=''U'' and b.xtype=''U''' exec(@sql) open #tb fetch next from #tb into @tbname while @@fetch_status=0 begin select @sql1='' ,@sql='select @sql1=@sql1+'',[''+a.name+'']'' from( select name from '+@o_dbname+'..syscolumns where id in (select id from '+@o_dbname+'..sysobjects where name='''+@tbname+''') ) a inner join ( select name from '+@n_dbname+'..syscolumns where status<>0x80 and id in (select id from '+@n_dbname+'..sysobjects where name='''+@tbname+''') ) b on a.name=b.name' exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out

select @sql1=substring(@sql1,2,8000) exec('insert into '+@n_dbname+'..['+@tbname+']('+@sql1 +') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']') if @@error<>0 print('insert into '+@n_dbname+'..['+@tbname+']('+@sql1 +') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']') fetch next from #tb into @tbname end close #tb deallocate #tb

--資料複製完成後啟用約束 open #tbc fetch next from #tbc into @name,@tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ['+@name+']' exec(@sql) fetch next from #tbc into @name,@tbname end close #tbc deallocate #tbc go

backup database sys to disk='//ip/共用目錄/a.bak' with init 許可權問題！！！ 如： SQLServer備到FileServer上 那你的SQLServer的啟動用戶必須在FileServer上有足夠的許可權! --------------------^^^--------------------------------- 1、SQLServer上新建一SQLUser用戶許可權大一點。 2、FileServer上建同一用戶對某一檔夾有足夠許可權。 3、兩機的SQLUser密碼相同(方便一點) 4、將SQLServer改為SQLUser啟動 (管理工具-->服務-->mssql項-->屬性-->指定用戶及密碼) 5、//192.168.*.*/文件夾/ShareBak.Bak就可以了 ----------------------------------------------------------- 簡單一點就是： 如果你兩台都是windows2ks 你的機器也用同樣的Administrator密碼,且sql伺服器用administrator啟動 那你寫設備時就可以寫://ip/.......

如果對方是98那你要完全共用一下！ 你寫設備時就可以寫://ip/.......

 

一 配置发布服务器

在发布服务器上执行以下步骤:

(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导 (2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器 (3) [下一步] 设置快照文件夹 一般采用默认//servername/d$/Program Files/Microsoft SQL Server/MSSQL/ReplData **(4) [下一步] 自定义配置 可以选择: 是,让我设置分发数据库属性启用发布服务器或设置发布设置 否,使用下列默认设置

建议采用自定义设置 (5) [下一步] 设置分发数据库名称和位置 采用默认值 (6) [下一步] 启用发布服务器 选择作为发布的服务器 (7) [下一步] 选择需要发布的数据库和发布类型 (8) [下一步] 选择注册订阅服务器 (9) [下一步] 完成配置

二 创建出版物

发布服务器上 (1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令 (2)选择要创建出版物的数据库，然后单击[创建发布] (3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。 对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)

(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型, SQLSERVER允许在不同的数据库如 ORACLE或ACCESS之间进行数据复制。 但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器

(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表

注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表

(6)选择发布名称和描述

**(7)自定义发布属性

向导提供的选择: 是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性 否 根据指定方式创建发布

建议采用自定义的方式

(8)[下一步] 选择筛选发布的方式 (9)[下一步] 可以选择是否允许匿名订阅

1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器 方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加

否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅 如果仍然需要匿名订阅则用以下解决办法 [企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅

2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示

(10)[下一步] 设置快照 代理程序调度 (11)[下一步] 完成配置

当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。

三 订阅服务器的配置

(一) 强制订阅的配置 在发布服务器上 [企业管理器]->[复制]->[发布内容]->[属性]->[订阅]->[强制新建] 然后出现强制订阅向导 选择订阅服务器->订阅数据库名->设置分发代理程序调度->初始化订阅->启动要求的服务 (发布服务器的agent要启动)->完成即可

(二) 请求订阅的配置

订阅服务器上执行以下操作

(1) 从[工具]下拉菜单中选择[复制]子菜单的[请求订阅] (2) 选择[请求新订阅] 打开请求订阅向导 (3) [下一步]选择已注册的发布 (4) [下一步]选择创建订阅的数据库 (5) [下一步]选择匿名订阅或署名订阅 (6) [下一步] 初始化订阅 选择初始化架构和数据 (7) [下一步]选择快照文件的存放位置 一般采用发布服务器的默认快照文件夹 **(8) [下一步]设置分发代理程序调度 可以选择订阅更新内容的间隔 (9) [下一步]系统会提示检查发布服务器的代理服务的运行状态 (10) [下一步] 完成配置

在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->属性-->启动

在windows控制面版-->管理工具-->服务-->SQLSERVERAGENT-->属性-->启动

--数据较少时,可以手工同步,更方便控制

--用触发器即时同步两个表的实例:

--测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test

--创建测试表,不能用标识列做主键,因为不能进行正常更新 --在远程主机上建表 if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [test]

create table test(id int not null constraint PK_test primary key  ,name varchar(10)) go

--以下操作在本机进行 --在本机上建表及做同步处理的工作 if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [test]

create table test(id int identity(1,1) primary key  ,name varchar(10)) go

--创建同步的触发器 create trigger t_test on test for insert,update,delete as set  XACT_ABORT on --启动远程服务器的MSDTC服务 exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output

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

--进行分布事务处理,如果表用标识列做主键,用下面的方法 BEGIN DISTRIBUTED TRANSACTION delete from openrowset('sqloledb','xz';'sa';'',test.dbo.test)  where id in(select id from deleted) insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test)  select * from inserted commit tran go

--插入数据测试 insert into test select 1,'aa' union all select 2,'bb' union all select 3,'c' union all select 4,'dd' union all select 5,'ab' union all select 6,'bc' union all select 7,'ddd'

--删除数据测试 delete from test where id in(1,4,6)

--更新数据测试 update test set name=name+'_123' where id in(3,5)

--显示测试的结果 select * from test a full join openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id

--定时同步服务器上的数据(本地的数据修改同步到服务器)

--例子: --测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test --服务器上的表(查询分析器连接到服务器上创建) if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  drop table [user]

create table [user](id int primary key,number varchar(4),name varchar(10)) go

--以下在局域网(本机操作) --state字段为辅助更新而设置的附加字段,字段值说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录 if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  drop table [user] GO create table [user](id int identity(1,1) primary key,number varchar(4),name varchar(10),state bit) go --创建触发器,维护state字段的值 create trigger t_state on [user] after update as update [user] set state=1 from [user] a join inserted b on a.id=b.id where a.state is not null go

--为了方便同步处理,创建链接服务器到要同步的服务器 --这里的远程服务器名为:xz,用户名为:sa,无密码 if exists(select 1 from master..sysservers where srvname='srv_lnk')  exec sp_dropserver 'srv_lnk','droplogins' go exec sp_addlinkedserver  'srv_lnk','','SQLOLEDB','xz' exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa' go

--创建同步处理的存储过程 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)  drop procedure [dbo].[p_synchro] GO create proc p_synchro as /*--因为MSDTC服务(分布式事务处理需要)工作不稳定,所以下面部分为选用内容 --set  xact_abort on --启动远程服务器的MSDTC服务 --exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output

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

--进行分布事务处理,如果表用标识列做主键,用下面的方法 --BEGIN DISTRIBUTED TRANSACTION --*/  --同步删除的数据  delete from srv_lnk.test.dbo.[user]   where id not in(select id from [user])

 --同步新增的数据  insert into srv_lnk.test.dbo.[user]  select id,number,name from [user] where state is null    --同步修改的数据  update srv_lnk.test.dbo.[user] set   number=b.number,name=b.name  from srv_lnk.test.dbo.[user] a   join [user] b on a.id=b.id  where b.state=1    --同步后更新本机的标志  update [user] set state=0 where isnull(state,1)=1 --COMMIT TRAN  --如果启用分布式事务处理,加上此句 go

--创建作业,定时执行数据同步的存储过程 if exists(SELECT 1 from msdb..sysjobs where name='数据处理')  EXECUTE msdb.dbo.sp_delete_job @job_name='数据处理' exec msdb..sp_add_job @job_name='数据处理'

--创建作业步骤 declare @sql varchar(800),@dbname varchar(250) select @sql='exec p_synchro'   --数据处理的命令  ,@dbname=db_name()   --执行数据处理的数据库名

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

--创建调度 EXEC msdb..sp_add_jobschedule @job_name = '数据处理',  @name = '时间安排',  @freq_type=4,       --4 每天,8 每周,16 每月  @freq_interval=1,     --作业执行的天数  @freq_subday_type=0,    --是否重复执行,0x1 在指定的时间, 0x4 分钟, 0x8 小时  @freq_subday_interval=1,    --重复周期  @freq_recurrence_factor=0,   --重复执行,则设置为1,否则设置为0  @active_start_time = 00000   --0点开始执行 go