- USE master;
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mo%123';
- GO
- CREATE CERTIFICATE HOST_91_cert
- WITH SUBJECT = 'HOST_91 certificate for database mirroring';
- GO
- CREATE ENDPOINT Endpoint_Mirroring
- STATE = STARTED
- AS TCP (
- LISTENER_PORT=7024
- , LISTENER_IP = ALL
- )
- FOR DATABASE_MIRRORING (
- AUTHENTICATION = CERTIFICATE HOST_91_cert
- , ENCRYPTION = REQUIRED ALGORITHM AES
- , ROLE = ALL
- );
- GO
- BACKUP CERTIFICATE HOST_91_cert TO FILE = 'C:\HOST_91_cert.cer';
- GO
- USE master;
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = mo%123';
- GO
- CREATE CERTIFICATE HOST_95_cert
- WITH SUBJECT = 'HOST_95 certificate for database mirroring';
- GO
- CREATE ENDPOINT Endpoint_Mirroring
- STATE = STARTED
- AS TCP (
- LISTENER_PORT=7024
- , LISTENER_IP = ALL
- )
- FOR DATABASE_MIRRORING (
- AUTHENTICATION = CERTIFICATE HOST_95_cert
- , ENCRYPTION = REQUIRED ALGORITHM AES
- , ROLE = ALL
- );
- GO
- BACKUP CERTIFICATE HOST_95_cert TO FILE = 'C:\HOST_95_cert.cer';
- GO
- USE master;
- CREATE LOGIN HOST_95_LOGIN
- WITH PASSWORD = 'mo%123';
- GO
- USE master;
- CREATE USER HOST_95_USER FOR LOGIN HOST_95_LOGIN;
- GO
- CREATE CERTIFICATE HOST_95_cert
- AUTHORIZATION HOST_95_USER
- FROM FILE = 'C:\HOST_95_cert.cer';
- GO
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_95_LOGIN
- GO
- USE master;
- CREATE LOGIN HOST_91_LOGIN
- WITH PASSWORD = 'mo%123';
- GO
- USE master;
- CREATE USER HOST_91_USER FOR LOGIN HOST_91_LOGIN;
- GO
- CREATE CERTIFICATE HOST_91_cert
- AUTHORIZATION HOST_91_USER
- FROM FILE = 'C:\HOST_91_cert.cer';
- GO
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_91_LOGIN
- GO
- USE master;
- GO
- ALTER DATABASE livechat
- SET RECOVERY FULL;
- GO
- BACKUP DATABASE livechat
- TO DISK = 'e:\livechat.bak'
- WITH FORMAT
- GO
- BACKUP LOG livechat
- TO DISK = 'e:\livechat_log.bak'
- GO
- RESTORE DATABASE livechat
- FROM DISK = 'C:\livechat.bak'
- WITH NORECOVERY
- GO
- RESTORE LOG livechat
- FROM DISK = 'C:\livechat_log.bak'
- WITH FILE=1, NORECOVERY
- GO
这里经常会出问题,经常性的被恢复的服务器上是没有数据库的,通过恢复必须使用强制覆盖(REPLACE)形式的恢复,可是镜像要求的是NORECOVERY,因此覆盖恢复之后必须先备份下日志,BACKUP LOG livechat TO DISK = 'e:\livechat _log_Again.bak' WITH NORECOVERY
,然后执行上面的两个Restore,数据文件和日志都必须恢复。然后才能执行SET PARTNER镜像。
===========================================================================
- ALTER DATABASE livechat
- SET PARTNER = 'TCP://192.168.10.91:7024';
- GO
- ALTER DATABASE livechat
- SET PARTNER = 'TCP://192.168.10.95:7024';
- GO
- USE master;
- ALTER DATABASE livechat
- SET PARTNER FAILOVER
- GO
删除数据库镜像
-
在数据库镜像会话期间,连接到主体服务器实例,然后在对象资源管理器中,单击服务器名称以展开服务器树。
-
展开“数据库”并选择数据库。
-
右键单击数据库,选择“任务”,再单击“镜像”。这样便可打开“数据库属性”对话框的“镜像”页。
-
在“选择页”窗格中,单击“镜像”。
-
若要删除镜像,请单击“删除镜像”。此时,将显示一个提示,要求您进行确认。如果单击“是”,会话将停止,并从数据库中删除镜像。
有关删除镜像的影响的详细信息,请参阅删除数据库镜像。
-
或者,可以恢复以前的镜像数据库。在作为镜像服务器的服务器实例上,使用以下 Transact-SQL 语句:
otherRESTORE DATABASE <database_name> WITH RECOVERY;
重要提示 如果恢复此数据库,则两个同名的不同数据库处于联机状态。因此,必须确保客户端仅可访问其中一个数据库,通常为最新的主体数据库。
转载于:https://blog.51cto.com/cnming/558734