anyThing is everyThing

--0
select * from master.sys.endpoints--查出原已有的站点
select * from master.sys.certificates--查出原已有的证书

DROP ENDPOINT 站点名--删除查出来的站点
DROP CERTIFICATE 主_cert--删除查出来的证书
DROP MASTER KEY --如果需要，则要删除掉原来的主密钥

--1在主数据库上创建站点，并创建证书然后把证书备份到D:\

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '证书强密码';
GO
CREATE CERTIFICATE [主_cert]
   WITH SUBJECT = '主 certificate for database mirroring';
GO
CREATE ENDPOINT 站点名
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE [主_cert]
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
BACKUP CERTIFICATE [主_cert] TO FILE = 'D:\主_cert.cer';
GO

--2在镜像数据库上创建站点，并创建证书然后把证书备份到D:\

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '证书强密码';
GO
CREATE CERTIFICATE [镜_cert]
   WITH SUBJECT = '镜 certificate for database mirroring';
GO
CREATE ENDPOINT 站点名
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE [镜_cert]
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
BACKUP CERTIFICATE [镜_cert] TO FILE = 'D:\镜_cert.cer';
GO

--这步成功后，将两台服务器的证书交叉复制到对方机器上。

--3在主数据库上创建镜像库的登录并将镜像库的证书添加到主数据库
USE master;
CREATE LOGIN [镜_LOGIN]
   WITH PASSWORD = '证书强密码';                
GO
USE master;
CREATE USER [镜_USER] FOR LOGIN [镜_LOGIN];
GO
CREATE CERTIFICATE [镜_cert]
   AUTHORIZATION [镜_USER]
   FROM FILE = 'D:\镜_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::站点名 TO [镜_LOGIN]
GO

--4在镜像数据库上创建主库的登录并将主库的证书添加到镜像数据库
USE master;
CREATE LOGIN [主_LOGIN]
   WITH PASSWORD = '证书强密码';                
GO
USE master;
CREATE USER [主_USER] FOR LOGIN [主_LOGIN];
GO
CREATE CERTIFICATE [主_cert]
   AUTHORIZATION [主_USER]
   FROM FILE = 'D:\主_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::站点名 TO [主_LOGIN]
GO

--5在主库上备份源数据库
USE master;
GO
ALTER DATABASE 数据库名
SET RECOVERY FULL;
GO
BACKUP DATABASE 数据库名
    TO DISK = 'd:\数据库名.bak'
    WITH FORMAT
GO
BACKUP LOG 数据库名
    TO DISK = 'd:\数据库名_log.bak'
GO

--6在镜像库上回复第5步中备份的库，恢复前不需要预先建立数据库,两台数据库服务器的SQL data目录必须一致，否则还原不了数据库
RESTORE DATABASE 数据库名
    FROM DISK = 'd:\数据库名.bak'
    WITH NORECOVERY
GO
RESTORE LOG 数据库名
    FROM DISK = 'd:\数据库名_log.bak'
    WITH FILE=1, NORECOVERY
GO

--7在镜像库上设置它的主库站点
ALTER DATABASE 数据库名
    SET PARTNER = 'TCP://192.168.169.75:5022';

--8在主库上设置它的镜像站点
ALTER DATABASE 数据库名
    SET PARTNER = 'TCP://192.168.169.74:5022';

参考文章：http://www.hftel.com/post/2010/08/18/e4bdbfe794a8e8af81e4b9a6e5889be5bbbaSQL-Server-2008e9959ce5838f.aspx

原文连接：http://dev.firnow.com/course/7_databases/sql/sqlServer/20100710/399659.html