双机镜像

  --主体
 USE master ;

GO
  --创建主密钥
   DROP ENDPOINT Endpoint_do
   DROP CERTIFICATE MASTER_Cert
   DROP MASTER  KEY
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456' ;
 GO
  --创建证书
 
 CREATE CERTIFICATE MASTER_Cert WITH SUBJECT = 'MASTER_Certificate', START_DATE = '01/01/2011' ;
  GO
  --创建端点
 
 CREATE ENDPOINT Endpoint_do STATE = STARTED AS TCP ( LISTENER_PORT=6666,
    LISTENER_IP = ALL ) FOR
    DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MASTER_Cert, ENCRYPTION =
    REQUIRED ALGORITHM AES, ROLE = ALL ) ;
 
 --备份证书，用于主备证书互换，并拷贝证书到镜像机 C:\share 目录
 BACKUP CERTIFICATE MASTER_Cert TO FILE = 'C:\share\MASTER_Cert.cer' ;
 
 --主体
 
 --添加登陆用户
 
 --镜像
 DROP LOGIN MIRROR_Login
 DROP  CERTIFICATE MIRROR_Cert
 DROP USER MIRROR_User
 
 CREATE LOGIN MIRROR_Login WITH PASSWORD = '123456' ;
 CREATE USER MIRROR_User FOR LOGIN MIRROR_Login ;
 CREATE CERTIFICATE MIRROR_Cert AUTHORIZATION MIRROR_User FROM FILE = 'C:\share\MIRROR_Cert.cer' ;
 GRANT CONNECT ON ENDPOINT::Endpoint_do TO MIRROR_Login ;
 
 
 
 
 
 --激活端点
ALTER ENDPOINT Endpoint_do STATE=STARTED
 --设置伙伴
 --ALTER DATABASE test SET PARTNER off
 ALTER DATABASE test SET PARTNER = 'TCP://192.168.1.170:6666';
 

return
--备份，并拷贝到镜像机c:\share
BACKUP DATABASE test TO DISK ='c:\share\db'

BACKUP log test TO DISK ='c:\share\log' 
 

 

--------------------------------------------------------------------------------------------------------------------

 

  --镜像
  USE master ;
 GO
  --创建主密钥
  DROP ENDPOINT Endpoint_do
  DROP CERTIFICATE MIRROR_Cert
  DROP MASTER  KEY
 
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456' ;
 GO
  --创建证书
  --
  CREATE CERTIFICATE MIRROR_Cert WITH SUBJECT = 'MIRROR_Certificate', START_DATE = '01/01/2011' ;
  GO
  --创建端点 
  CREATE ENDPOINT Endpoint_do STATE = STARTED AS TCP ( LISTENER_PORT=6666,
    LISTENER_IP = ALL ) FOR
    DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MIRROR_Cert, ENCRYPTION =
    REQUIRED ALGORITHM AES, ROLE = ALL ) ;
 
 --备份证书，用于主备证书互换，并拷贝证书到主体机 C:\share 目录
  BACKUP CERTIFICATE MIRROR_Cert TO FILE = 'C:\share\MIRROR_Cert.cer' ;
 
 --镜像
 --添加登陆用户
--主体
  DROP LOGIN master_Login
 DROP  CERTIFICATE master_Cert
 DROP USER master_User
 
 CREATE LOGIN master_Login WITH PASSWORD = '123456' ;
 CREATE USER master_User FOR LOGIN master_Login ;
 CREATE CERTIFICATE master_Cert AUTHORIZATION master_User FROM FILE = 'C:\share\master_Cert.cer' ;
 GRANT CONNECT ON ENDPOINT::Endpoint_do TO master_Login ;
  
  
    
 
 --激活端点
ALTER ENDPOINT Endpoint_do STATE=STARTED
 
  --设置伙伴
  --ALTER DATABASE test SET PARTNER off
 ALTER DATABASE test SET PARTNER = 'TCP://192.168.1.169:6666';
  return
--还原镜像数据库
/ 
RESTORE DATABASE [test] FROM  DISK = N'C:\share\db' WITH  FILE = 1,  MOVE N'test'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test.mdf', 
MOVE N'test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_1.LDF', 
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [test] FROM  DISK = N'C:\share\log' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

 return