SQLServer 鏡像功能完全實現(xiàn)

--創(chuàng)建主機(jī)數(shù)據(jù)庫主密鑰
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--在10.2上為數(shù)據(jù)庫實例創(chuàng)建證書
CREATE CERTIFICATE As_A_cert
WITH SUBJECT = 'As_A_cert',
START_DATE = '09/02/2011',
EXPIRY_DATE = '01/01/2099';
GO
--在10.2上使用上面創(chuàng)建的證書為數(shù)據(jù)庫實例創(chuàng)建鏡像端點
CREATE ENDPOINT Endpoint_As
STATE = STARTED
AS TCP (
LISTENER_PORT=5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE As_A_cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
);
GO

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
--在10.1 B上為數(shù)據(jù)庫實例創(chuàng)建證書
CREATE CERTIFICATE As_B_cert
WITH SUBJECT = 'As_B_cert',
START_DATE = '09/2/2011',
EXPIRY_DATE = '01/01/2099';
GO
--在10.1 B上使用上面創(chuàng)建的證書為數(shù)據(jù)庫實例創(chuàng)建鏡像端點
CREATE ENDPOINT Endpoint_As
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE As_B_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

CREATE LOGIN B_login WITH PASSWORD = 'password';

CREATE USER B_user FOR LOGIN B_login;

CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE = 'D:\As_B_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login];

--交換證書，
--同步 Login
CREATE LOGIN A_login WITH PASSWORD = 'password';

CREATE USER A_user FOR LOGIN A_login;

CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE = 'D:\As_A_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login];

--主機(jī)執(zhí)行完整備份
USE master;
ALTER DATABASE Test SET RECOVERY FULL;
GO
BACKUP DATABASE Test
TO DISK = 'D:\SQLServerBackups\Test.bak'
WITH FORMAT;
GO
BACKUP LOG Test TO DISK = 'D:\SQLServerBackups\Test.bak';
GO

RESTORE DATABASE Test
FROM DISK = 'D:\Back\Test.bak'
WITH NORECOVERY
GO
RESTORE LOG Test
FROM DISK = 'D:\Back\Test_log.bak'
WITH FILE=1, NORECOVERY
GO
[code]
可能會報：

消息 3154，級別 16，狀態(tài) 4，第 1 行
備份集中的數(shù)據(jù)庫備份與現(xiàn)有的 'Test'數(shù)據(jù)庫不同。
消息 3013，級別 16，狀態(tài) 1，第 1 行

可能是兩個數(shù)據(jù)庫的備份集名稱不同導(dǎo)致，找了半天原因未果，所以采用下面sp_addumpdevice方法來做。

用sp_addumpdevice來建立一個還原的設(shè)備。這樣就保證了改備份文件是數(shù)據(jù)這個數(shù)據(jù)庫的。
[code]
exec sp_addumpdevice 'disk','Test_backup',
'E:\backup\Test.bak'
exec sp_addumpdevice 'disk','Test_log_backup',
'E:\backup\Test_log.bak'
go

RESTORE DATABASE Test
FROM Test_backup
WITH DBO_ONLY,
NORECOVERY,STATS;
go
RESTORE LOG Test
FROM Test_log_backup
WITH file=1,
NORECOVERY;
GO

USE MASTER
Go
ALTER DATABASE Test SET PARTNER FAILOVER
Go

sp_addumpdevice [ @devtype = ] 'device_type'
, [ @logicalname = ] 'logical_name'
, [ @physicalname = ] 'physical_name'
]
其中參數(shù)有：
@devtype：設(shè)備類型，可以支持的值為disk和tape，其中disk為磁盤文件；tape為
windows支持的任何磁帶設(shè)備。
@logicalname：備份設(shè)備的邏輯名稱，設(shè)備名稱。
@physicalname：備份設(shè)備的物理名稱，路徑