forked from chrisdamba/SQL-Server-Management-Studio
-
Notifications
You must be signed in to change notification settings - Fork 0
/
START AND STOP MIRRORING FAILOVER SCRIPTS.sql
96 lines (76 loc) · 2.37 KB
/
START AND STOP MIRRORING FAILOVER SCRIPTS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- START MIRRORING
DECLARE @DBName SYSNAME
DECLARE @JobName SYSNAME
DECLARE DatabaseListCursor CURSOR
FOR
-- SELECT QUERY FOR CURSOR (CUSTOMIZED FOR THIS SERVER)
SELECT 'Getty_Images_US_Inc_Custom' UNION ALL
SELECT 'Getty_Images_US_Inc__MSCRM' UNION ALL
SELECT 'Getty_Images_CRM_GENESYS'
BEGIN TRY
EXEC sp_addlinkedserver @server='DYN_DBA_RMT',@srvproduct='',@provider='SQLNCLI',@datasrc=@@SERVERNAME
END TRY
BEGIN CATCH
IF @@ERROR = 15028
RAISERROR(' Linked Server Already Exists',-1,-1) WITH NOWAIT
END CATCH
OPEN DatabaseListCursor;
FETCH DatabaseListCursor INTO @DBName;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
----------------------------
---------------------------- CURSOR LOOP TOP
insert into dbaadmin.dbo.Local_ServerEnviro values('mirror_failover_override', getdate())
EXEC [dbaadmin].[dbo].[dbasp_DBMirror_Control]
@Function = 'START'
,@DBName = @DBName
,@ServerName = 'ASHPCRMSQL11'
---------------------------- CURSOR LOOP BOTTOM
----------------------------
END
FETCH NEXT FROM DatabaseListCursor INTO @DBName;
END
CLOSE DatabaseListCursor;
DEALLOCATE DatabaseListCursor;
GO
-- STOP MIRRORING
DECLARE @DBName SYSNAME
DECLARE @JobName SYSNAME
DECLARE DatabaseListCursor CURSOR
FOR
-- SELECT QUERY FOR CURSOR (CUSTOMIZED FOR THIS SERVER)
SELECT 'Getty_Images_US_Inc_Custom' UNION ALL
SELECT 'Getty_Images_US_Inc__MSCRM' UNION ALL
SELECT 'Getty_Images_CRM_GENESYS'
BEGIN TRY
EXEC sp_addlinkedserver @server='DYN_DBA_RMT',@srvproduct='',@provider='SQLNCLI',@datasrc=@@SERVERNAME
END TRY
BEGIN CATCH
IF @@ERROR = 15028
RAISERROR(' Linked Server Already Exists',-1,-1) WITH NOWAIT
END CATCH
OPEN DatabaseListCursor;
FETCH DatabaseListCursor INTO @DBName;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
----------------------------
---------------------------- CURSOR LOOP TOP
insert into dbaadmin.dbo.Local_ServerEnviro values('mirror_failover_override', getdate())
EXEC [dbaadmin].[dbo].[dbasp_DBMirror_Control]
@Function = 'OFF'
,@DBName = @DBName
,@ServerName = 'ASHPCRMSQL11'
EXEC ('RESTORE DATABASE ['+@DBName+'] WITH RECOVERY')
EXEC ('DROP DATABASE ['+@DBName+']')
---------------------------- CURSOR LOOP BOTTOM
----------------------------
END
FETCH NEXT FROM DatabaseListCursor INTO @DBName;
END
CLOSE DatabaseListCursor;
DEALLOCATE DatabaseListCursor;
GO