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

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

示例如下：

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

create endpoint db_mirroringep

as tcp (listener_port = 5022)

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

go

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

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ù)庫鏡像端點，用于見證通訊

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上，檢查端點配置

select * from sys.database_mirroring_endpoints

go

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

use master

go

grant connect on endpoint::db_mirroringep to somodesql\sqladmin

go

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

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

go

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

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

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

restore database adventureworks

from disk = 'c:\adventureworks.bak'

with norecovery

go

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

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

alter database adventureworks

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

go

--在mir-a上，指定伙伴端點

alter database adventureworks

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

go

--在mir-a上，指定見證服務(wù)器端點

alter database adventureworks

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

go

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

alter database adventureworks set safety full

go

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

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

-- 2.)通過management studio 對象資源管理器中地數(shù)據(jù)庫屬性查看狀態(tài)

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

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')

更多信息請查看IT技術(shù)專欄