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