【整理】MSSQL 2005 Mirror

 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;