這篇論壇文章(賽迪網(wǎng)技術(shù)社區(qū))主要介紹了sql server 2005數(shù)據(jù)庫(kù)鏡像地配置腳本，詳細(xì)內(nèi)容請(qǐng)大家參考下文：

sql server 2005數(shù)據(jù)庫(kù)鏡像配置腳本:

示例如下：

--在mir-a上，創(chuàng)建數(shù)據(jù)庫(kù)鏡像端點(diǎn)

create endpoint db_mirroringep

as tcp (listener_port = 5022)

for database_mirroring (role = partner,encryption=supported);

go

--在mir-b上，創(chuàng)建數(shù)據(jù)庫(kù)鏡像端點(diǎn)，用于伙伴通訊

create endpoint db_mirroringep

as tcp (listener_port = 5022)

for database_mirroring (role = partner, encryption = supported);

go

alter endpoint db_mirroringep state = started

go

--在mir-w上，創(chuàng)建數(shù)據(jù)庫(kù)鏡像端點(diǎn)，用于見(jiàn)證通訊

create endpoint db_mirroringep

as tcp (listener_port = 5022)

for database_mirroring (role = witness, encryption = supported);

go

alter endpoint db_mirroringep state = started

go

--在mir-a，mir-b，mir-w上，檢查端點(diǎn)配置

select * from sys.database_mirroring_endpoints

go

--在mir-a，mir-b，mir-w上，配置數(shù)據(jù)庫(kù)鏡像安全性，somodesql.com為自己地域名

use master

go

grant connect on endpoint::db_mirroringep to somodesql\sqladmin

go

--在mir-a上，對(duì)adventureworks數(shù)據(jù)庫(kù)做完全備份

backup database adventureworks to disk = 'c:\adventureworks.bak'

go

--在mir-b上恢復(fù)adventureworks數(shù)據(jù)庫(kù).

--通過(guò)安全方法，將 c:\adventureworks.bak 復(fù)制到 mir-b.

--在 mir-b 地鏡像服務(wù)器實(shí)例上還原數(shù)據(jù)庫(kù)：

restore database adventureworks

from disk = 'c:\adventureworks.bak'

with norecovery

go

--啟動(dòng)數(shù)據(jù)庫(kù)鏡像，注意順序，需要在首先在鏡像服務(wù)器上配置伙伴

--在mir-b上，指定伙伴端點(diǎn),somodesql.com為自己地域名

alter database adventureworks

set partner = n'tcp://mir-a.somodesql.com:5022'

go

--在mir-a上，指定伙伴端點(diǎn)

alter database adventureworks

set partner = n'tcp://mir-b.somodesql.com:5022'

go

--在mir-a上，指定見(jiàn)證服務(wù)器端點(diǎn)

alter database adventureworks

set witness = n'tcp://mir-w.somodesql.com:5022'

go

--配置數(shù)據(jù)庫(kù)鏡像事務(wù)安全級(jí)別

alter database adventureworks set safety full

go

--=================查看數(shù)據(jù)庫(kù)鏡像地配置狀態(tài)=================

-- 1.)通過(guò)management studio 對(duì)象資源管理器，查看主體數(shù)據(jù)庫(kù)、鏡像數(shù)據(jù)庫(kù)狀態(tài)

-- 2.)通過(guò)management studio 對(duì)象資源管理器中地?cái)?shù)據(jù)庫(kù)屬性查看狀態(tài)

-- 3.)通過(guò)系統(tǒng)目錄視圖查看數(shù)據(jù)庫(kù)鏡像配置情況

use master

go

select * from sys.database_mirroring_endpoints

select * from sys.database_mirroring where database_id =

(select database_id from sys.databases where name = 'adventureworks')

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄