forked from chrisdamba/SQL-Server-Management-Studio
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Generate Add DB Script for Availability Group 2.sql
206 lines (167 loc) · 6.85 KB
/
Generate Add DB Script for Availability Group 2.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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
--:SETVAR ScriptPath null
:OUT $(TEMP)\SetVars.sql
DECLARE @UID UniqueIdentifier = NEWID()
PRINT ':SETVAR ScriptPath ' + dbaadmin.[dbo].[dbaudf_getShareUNC]('backup') + '\RunAllAdds_'+CAST(@UID AS VarChar(50))+'.sql'
PRINT ':SETVAR TemplatePath ' + dbaadmin.[dbo].[dbaudf_getShareUNC]('backup') + '\AddDBToAG_'+CAST(@UID AS VarChar(50))+'.sql'
GO
:OUT stdout
:r $(TEMP)\SetVars.sql
GO
PRINT 'Script File Generated: $(ScriptPath)'
PRINT 'Template File Generated: $(TemplatePath)'
DECLARE @DBName SYSNAME
,@SourceServer SYSNAME = @@SERVERNAME
,@DestServer SYSNAME = 'ASHPSQLEDW01'
,@AGroup SYSNAME = NULL
,@ExcludeDBs VarChar(max) = 'DataWarehouse_Snapshot_AnalysisServices|ConsolidatedDataWarehouse|DWStage' -- Pipe Delimited Database List
,@InAG CHAR(1)
,@AllDBsJoined BIT
,@ScriptOutput VarChar(max)
,@TemplateOutput VarChar(max)
,@ScriptPath VarChar(max) = '$(ScriptPath)'
,@TemplatePath VarChar(max) = '$(TemplatePath)'
SET @TemplateOutput =
':Connect '+CHAR(36)+'(Source)
IF '''+CHAR(36)+'(InAG)'' = ''1''
BEGIN
RAISERROR (''Database: '+CHAR(36)+'(DBName) is already a part of the Availability Group.'',-1,-1) WITH NOWAIT
END
ELSE
BEGIN
RAISERROR (''Adding Database '+CHAR(36)+'(DBName) To the Availability Group.'',-1,-1) WITH NOWAIT
ALTER AVAILABILITY GROUP ['+CHAR(36)+'(AGroup)] ADD DATABASE ['+CHAR(36)+'(DBName)];
END
DECLARE @AgentJob SYSNAME = ''MAINT - TranLog Backup''
,@LockCount INT
IF dbaadmin.dbo.[dbaudf_GetJobStatus](@AgentJob) != -2
BEGIN
--INCREMENT "Lock_TranlogBackups" LOCK
SELECT @LockCount = dbaadmin.[dbo].[dbaudf_SetEV](''Lock_TranlogBackups'',ISNULL(CAST(dbaadmin.[dbo].[dbaudf_GetEV](''Lock_TranlogBackups'') AS INT),0)+1)
RAISERROR (''Agent Job: %s is being disabled.'',-1,-1,@AgentJob) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_Name=@AgentJob, @enabled=0
WHILE dbaadmin.dbo.[dbaudf_GetJobStatus](@AgentJob) = 4
BEGIN
RAISERROR (''Agent Job: %s is running, Waiting for it to finish.'',-1,-1,@AgentJob) WITH NOWAIT
WAITFOR DELAY ''00:01:00''
END
END
DECLARE @Backup_cmd nvarchar(max)
RAISERROR (''Generating Transaction Log Backup Command on Database '+CHAR(36)+'(DBName).'',-1,-1) WITH NOWAIT
EXEC [dbaadmin].[dbo].[dbasp_format_BackupRestore]
@DBName = '''+CHAR(36)+'(DBName)''
,@Mode = ''BL''
,@Verbose = 0
,@syntax_out = @Backup_cmd OUTPUT
SET @Backup_cmd = REPLACE(@Backup_cmd,''INSERT INTO'',''--INSERT INTO'')
RAISERROR (''Backing Up Transaction Log on Database '+CHAR(36)+'(DBName).'',-1,-1) WITH NOWAIT
EXEC (@Backup_cmd)
GO
'
+':Connect '+CHAR(36)+'(Dest)
DECLARE @Restore_cmd nvarchar(max)
RAISERROR (''Generating Restore Command for Database '+CHAR(36)+'(DBName).'',-1,-1) WITH NOWAIT
EXEC [dbaadmin].[dbo].[dbasp_format_BackupRestore]
@DBName = '''+CHAR(36)+'(DBName)''
,@Mode = ''RD''
,@FromServer = '''+CHAR(36)+'(Source)''
,@Verbose = 0
,@LeaveNORECOVERY = 1
,@syntax_out = @Restore_cmd OUTPUT
RAISERROR (''Restoring Database '+CHAR(36)+'(DBName).'',-1,-1) WITH NOWAIT
EXEC (@Restore_cmd)
BEGIN TRY
RAISERROR (''Setting Database '+CHAR(36)+'(DBName) HADR to Availability Group.'',-1,-1) WITH NOWAIT
ALTER DATABASE ['+CHAR(36)+'(DBName)] SET HADR AVAILABILITY GROUP = ['+CHAR(36)+'(AGroup)];
END TRY
BEGIN CATCH
SET @Restore_cmd = ''''
RAISERROR (''Generating Restore Command for Database '+CHAR(36)+'(DBName).'',-1,-1) WITH NOWAIT
EXEC [dbaadmin].[dbo].[dbasp_format_BackupRestore]
@DBName = '''+CHAR(36)+'(DBName)''
,@Mode = ''RD''
,@FromServer = '''+CHAR(36)+'(Source)''
,@Verbose = 0
,@LeaveNORECOVERY = 1
,@syntax_out = @Restore_cmd OUTPUT
RAISERROR (''Restoring Database '+CHAR(36)+'(DBName).'',-1,-1) WITH NOWAIT
EXEC (@Restore_cmd)
RAISERROR (''Setting Database '+CHAR(36)+'(DBName) HADR to Availability Group.'',-1,-1) WITH NOWAIT
ALTER DATABASE ['+CHAR(36)+'(DBName)] SET HADR AVAILABILITY GROUP = ['+CHAR(36)+'(AGroup)];
END CATCH
GO
'
+':Connect '+CHAR(36)+'(Source)
DECLARE @AgentJob SYSNAME = ''MAINT - TranLog Backup''
,@LockCount INT
IF dbaadmin.dbo.[dbaudf_GetJobStatus](@AgentJob) != -2
BEGIN
--DECREMENT "Lock_TranlogBackups" LOCK
SELECT @LockCount = dbaadmin.[dbo].[dbaudf_SetEV](''Lock_TranlogBackups'',nullif(ISNULL(CAST(dbaadmin.[dbo].[dbaudf_GetEV](''Lock_TranlogBackups'') AS INT)-1,0),0))
IF @LockCount IS NULL
BEGIN
RAISERROR (''Agent Job: %s is being enabled.'',-1,-1,@AgentJob) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_Name=@AgentJob, @enabled=1
END
ELSE
BEGIN
RAISERROR (''Agent Job: %s is Not being enabled because other sessions still have it locked.'',-1,-1,@AgentJob) WITH NOWAIT
END
END
GO
'
exec dbaadmin.dbo.dbasp_FileAccess_Write @TemplateOutput, @TemplatePath,0,1
IF @AGroup IS NULL
SELECT TOP 1
@AGroup = AG.name
FROM master.sys.availability_groups AS AG
DECLARE DBCursor CURSOR
FOR
-- SELECT QUERY FOR CURSOR
SELECT DB.Name
,CASE WHEN database_name IS NULL THEN '0' ELSE '1' END [InAG]
,CASE WHEN COALESCE(MIN(CAST(is_database_joined AS INT)),0) = 0 THEN 0 ELSE 1 END [AllDBsJoined]
FROM sys.databases db
LEFT JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON db.name = dbcs.database_name
WHERE db.name not in ('master','model','msdb','tempdb','dbaadmin','dbaperf','sqldeploy')
AND db.Name not in (SELECT DISTINCT [SplitValue] FROM dbaadmin.dbo.dbaudf_StringToTable(@ExcludeDBs,'|'))
GROUP BY DB.Name,database_name
ORDER BY DB.Name
SET @ScriptOutput = '--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.' +CHAR(13)+CHAR(10)
OPEN DBCursor;
FETCH DBCursor INTO @DBName,@InAG,@AllDBsJoined;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
----------------------------
---------------------------- CURSOR LOOP TOP
IF @AllDBsJoined = 1
BEGIN
RAISERROR ('--Database: %s already has All DB''s Joined to Availability Group.',-1,-1,@DBName) WITH NOWAIT
PRINT ''
END
ELSE
BEGIN
SET @ScriptOutput = @ScriptOutput + '' +CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + ':SETVAR DBName ' + @DBName + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + ':SETVAR AGroup ' + @AGroup + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + ':SETVAR Source ' + @SourceServer + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + ':SETVAR Dest ' + @DestServer + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + ':SETVAR InAG ' + @InAG + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + '' + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + ':R ' + @TemplatePath + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + 'GO' + CHAR(13)+CHAR(10)
SET @ScriptOutput = @ScriptOutput + '' + CHAR(13)+CHAR(10)
END
---------------------------- CURSOR LOOP BOTTOM
----------------------------
END
FETCH NEXT FROM DBCursor INTO @DBName,@InAG,@AllDBsJoined;
END
CLOSE DBCursor;
DEALLOCATE DBCursor;
exec dbaadmin.dbo.dbasp_FileAccess_Write @ScriptOutput, @ScriptPath,0,1
GO
:r $(ScriptPath)
GO