1 -- The version of MSSQL must above 2005 SP1
2 -- Start SQL Service with same domain user
3
4 SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints
5
6 SELECT name, port FROM sys.tcp_endpoints
7
8 -- 1. DC - Create Endpoint
9 CREATE ENDPOINT Endpoint_Mirroring
10 STATE=STARTED
11 AS TCP (LISTENER_PORT=5022)
12 FOR DATABASE_MIRRORING (ROLE=PARTNER)
13
14 -- 2. BCC - Create Endpoint
15 CREATE ENDPOINT Endpoint_Mirroring
16 STATE=STARTED
17 AS TCP (LISTENER_PORT=5022)
18 FOR DATABASE_MIRRORING (ROLE=PARTNER)
19
20 -- 3. DC - Backup Dababase
21 BACKUP DATABASE CIM
22 TO DISK = 'E:\CIM_DB.bak'
23 WITH FORMAT
24
25 -- 4. BCC - Copy bak file from DC , Restore Database
26 RESTORE DATABASE CIM
27 FROM DISK='E:\CIM_DB.bak'
28 WITH NORECOVERY,
29 MOVE 'CIM' TO
30 'E:\CIM.mdf',
31 MOVE 'CIM_Log' TO
32 'E:\CIM_1.ldf';
33
34 -- 5. DC - Backup Log
35 BACKUP LOG CIM
36 TO DISK = 'E:\CIM_Log.bak'
37 GO
38
39 -- 6. BCC - Copy bak file from DC , Restore Log
40 RESTORE LOG CIM
41 FROM DISK = 'E:\CIM_Log.bak'
42 WITH FILE=1, NORECOVERY
43
44 -- 7. BCC - Set Partner
45 ALTER DATABASE CIM
46 SET PARTNER =
47 'TCP://CSZS19:5022';
48
49 -- 8. DC - Set Partner
50 ALTER DATABASE CIM
51 SET PARTNER = 'TCP://CSZS20:5022'
52
53
54 ---- 9 启用 BCC 数据库 begin
55 -- 1. 删除数据库镜像
56 ALTER DATABASE CIM SET PARTNER OFF
57 -- 2. BCC 恢复以前的镜像数据库
58 RESTORE DATABASE CIM WITH RECOVERY;
59 ---- 9 启用 BCC end
60
61 -- 1. BCC 在数据库镜像会话中强制服务
62 ALTER DATABASE CIM SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
63 -- 2. 暂停数据库镜像会话
64 ALTER DATABASE CIM SET PARTNER SUSPEND
65 -- 3. 恢复暂停的数据库镜像
66 ALTER DATABASE CIM SET PARTNER RESUME
67 -- 4. 删除数据库镜像
68 ALTER DATABASE CIM SET PARTNER OFF
69 -- 5. BCC 恢复以前的镜像数据库
70 RESTORE DATABASE CIM WITH RECOVERY;
