· 8 years ago · Feb 27, 2018, 06:40 PM
1DECLARE @name VARCHAR(50) -- database name
2DECLARE @path VARCHAR(256) -- path for backup files
3DECLARE @fileName VARCHAR(256) -- filename for backup
4
5-- specify database backup directory
6SET @path = '\atel-web-be2backups'
7
8DECLARE db_cursor CURSOR FOR
9select name from sys.databases
10where group_database_id is null and replica_id is null
11 and name not in('master','model','msdb','tempdb')
12
13OPEN db_cursor
14FETCH NEXT FROM db_cursor INTO @name
15WHILE @@FETCH_STATUS = 0
16BEGIN
17 SET @fileName = @path + @name + '.BAK'
18 BACKUP DATABASE @name TO DISK = @fileName
19 FETCH NEXT FROM db_cursor INTO @name
20END
21
22CLOSE db_cursor
23DEALLOCATE db_cursor
24
25IF EXISTS (SELECT * FROM sys.server_triggers
26 WHERE name = 'ddl_trig_database')
27DROP TRIGGER ddl_trig_database
28ON ALL SERVER;
29GO
30CREATE TRIGGER ddl_trig_database
31ON ALL SERVER
32FOR CREATE_DATABASE
33AS
34 PRINT 'Database Created.'
35 SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
36GO
37DROP TRIGGER ddl_trig_database
38ON ALL SERVER;
39GO
40
41-- Move each database into the Availability Group
42-- Change database name and Group as per your environment.
43ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG
44ALTER DATABASE Test2 SET HADR AVAILABILITY GROUP = TestAG
45GO
46
47-- create a driver table
48create table AlwaysON_Candidates (
49 DatabaseName sysname
50 ,createdate datetime default getdate()
51 ,IsAlwaysOnMember bit default 0 -- 0 = Not a part of AG
52 ) -- 1 = Is part of AG
53go
54-- below insert will be governed by the server level trigger
55insert into AlwaysON_Candidates (DatabaseName)
56values ('Test1')
57--- check the values in the driver table
58select *
59from AlwaysON_Candidates
60--- add database to AG
61alter database Test1
62set HADR AVAILABILITY group = TestAG
63-- update the bit in the driver table AlwaysON_Candidates
64update AlwaysON_Candidates
65set IsAlwaysOnMember = 1
66where DatabaseName = 'Test1'
67
68/************************************************************************************
69Author : Kin Shah
70Version : 1.0.0 for dba.stackexchange.com
71
72Note: This script does not have ERROR handling and is not tested.
73 Use at your own risk, It will print out the sql statements, but wont execute it
74 unless the print statements have been modified to use "exec"
75
76 UNDERSTAND the script and then test it on a TEST environment !!!!!!!!
77
78*************************************************************************************/
79-- create table
80set ansi_nulls on
81go
82
83set quoted_identifier on
84go
85
86create table AlwaysON_Candidates (
87 ID int identity(1, 1)
88 ,EventType nvarchar(128) null
89 ,DatabaseName nvarchar(128) null
90 ,LoginName nvarchar(128) null
91 ,UserName nvarchar(128) null
92 ,AuditDateTime datetime null
93 ,IsAlwaysOnMember bit default 0
94 )
95go
96
97alter table [dbo].[AlwaysON_Candidates] add default((0))
98for [IsAlwaysOnMember]
99go
100
101-- create server trigger
102if exists (
103 select *
104 from master.sys.server_triggers
105 where parent_class_desc = 'SERVER'
106 and name = N'ddl_trig_database'
107 )
108 drop trigger [ddl_trig_database] on all server
109go
110
111set ansi_nulls on
112go
113
114set quoted_identifier on
115go
116
117create trigger [ddl_trig_database] on all server
118for CREATE_DATABASE as
119
120insert into NewDatabases
121select EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') as EventType
122 ,EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') as DatabaseName
123 ,EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') as LoginName
124 ,EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)') as UserName
125 ,GETDATE() as AuditDateTime
126 ,0 as IsAlwaysOnMember
127go
128
129set ansi_nulls off
130go
131
132set quoted_identifier off
133go
134
135ENABLE trigger [ddl_trig_database] on all server
136go
137
138--- PREREQUISITE ... CREATE A LINKED SERVER FROM PRIMARY TO SECONDARY SERVER !!!
139--- fill in *** CHANGE HERE values
140--- test it on a TEST server
141--- Not tested and not responsible for any dataloss. UNDERSTAND it and test it before implementing it.
142declare @databasename varchar(max)
143declare @sqlbackup varchar(max)
144declare @sqlrestore varchar(max)
145declare @PrimaryAG varchar(max)
146declare @SecondaryAG varchar(max)
147declare @backupPath varchar(max)
148
149set @backupPath = '\servernamesharedfolder' --- *** CHANGE HERE
150
151declare @group sysname
152
153set @group = N'your_group_name' --- *** CHANGE HERE
154
155declare @remotesql1 varchar(max)
156declare @remotesql2 varchar(max)
157declare @linkedserverName sysname
158
159set @linkedserverName = 'kin_test_AG_LS' --- *** CHANGE HERE
160
161select @databasename = min(DatabaseName)
162from AlwaysON_Candidates
163where IsAlwaysOnMember = 0
164
165while @databasename is not null
166begin
167 -- ** BACKUP HAPPENS HERE **
168 select @sqlbackup = N'BACKUP DATABASE ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
169 BACKUP LOG ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.TRN'' WITH INIT, COMPRESSION;'
170 from AlwaysON_Candidates
171 where IsAlwaysOnMember = 0
172
173 print @sqlbackup --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
174
175 -- ** RESTORE HAPPENS HERE **
176 select @sqlrestore = N'RESTORE DATABASE ' + QUOTENAME(@databasename) + ' FROM DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH REPLACE, NORECOVERY;
177 RESTORE LOG ''' + @backupPath + @databasename + '_forAG.TRN'' WITH NORECOVERY;'
178
179 print @sqlrestore
180
181 select @remotesql1 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'
182
183 print @remotesql1 --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
184
185 -- join the AG group on primary
186 select @PrimaryAG = N'ALTER AVAILABILITY GROUP ' + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(@databasename) + ';'
187
188 print @PrimaryAG --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
189
190 -- join the AG group on secondary
191 select @SecondaryAG = 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' SET HADR AVAILABILITY GROUP = ' + QUOTENAME(@group) + ' ;'
192
193 print @SecondaryAG
194
195 select @remotesql2 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'
196
197 print @remotesql2 --- *** CHANGE HERE for EXEC master..sp_executesql @SecondaryAG
198
199 -- finally update the table
200 update AlwaysON_Candidates
201 set IsAlwaysOnMember = 1
202 where DatabaseName = @databasename
203
204 -- go to another database if it is added newly
205 select @databasename = min(DatabaseName)
206 from AlwaysON_Candidates
207 where IsAlwaysOnMember = 0
208 and DatabaseName > @databasename
209end
210
211CREATE PROCEDURE dbo.AddNewDBsToGroup
212 @group SYSNAME = N'your_group_name', -- *** SPECIFY YOUR GROUP NAME HERE ***
213 @path SYSNAME = N'\atel-web-be2backups',
214 @debug BIT = 1
215AS
216BEGIN
217 SET NOCOUNT ON;
218
219 DECLARE
220 @sql NVARCHAR(MAX) = N'',
221 @remote_sql NVARCHAR(MAX) = N'';
222
223 DECLARE @t TABLE(db SYSNAME);
224
225 INSERT @t SELECT name FROM sys.databases
226 WHERE replica_id IS NULL AND database_id > 4;
227
228 DECLARE @r TABLE(s NVARCHAR(512));
229
230 -- get the *healthy* replicas available for this group
231 -- you'll need error handling to handle cases where any
232 -- of the replicas is currently *not* healthy. This
233 -- script does not tell you this happened.
234
235 INSERT @r SELECT r.replica_server_name
236 FROM sys.availability_groups AS g
237 INNER JOIN sys.dm_hadr_availability_group_states AS s
238 ON g.group_id = s.group_id
239 INNER JOIN sys.availability_replicas AS r
240 ON g.group_id = r.group_id
241 AND r.replica_server_name <> @@SERVERNAME
242 WHERE g.name = @group
243 AND s.primary_replica = @@SERVERNAME
244 AND s.primary_recovery_health_desc = 'ONLINE'
245 AND s.synchronization_health_desc = 'HEALTHY';
246
247 -- add the database to the group on the primary:
248
249 SELECT @sql += N'ALTER AVAILABILITY GROUP '
250 + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
251 FROM @t;
252
253 IF @debug = 1
254 BEGIN
255 PRINT @sql;
256 END
257 ELSE
258 BEGIN
259 EXEC master..sp_executesql @sql;
260 END
261
262 -- back up the database locally:
263 -- this assumes your database names don't have characters illegal for paths
264
265 SET @sql = N'';
266
267 SELECT @sql += N'BACKUP DATABASE ' + QUOTENAME(db) -- ** BACKUP HAPPENS HERE **
268 + ' TO DISK = ''' + @path + db + '.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
269 BACKUP LOG ' + QUOTENAME(db) +
270 ' TO DISK = ''' + @path + db + '.TRN'' WITH INIT, COMPRESSION;'
271 FROM @t;
272
273 IF @debug = 1
274 BEGIN
275 PRINT @sql;
276 END
277 ELSE
278 BEGIN
279 EXEC master..sp_executesql @sql;
280 END
281
282 -- restore the database remotely:
283 -- this assumes linked servers match replica names, security works, etc.
284 -- it also assumes that each replica has the exact sime data/log paths
285 -- (in other words, your restore doesn't need WITH MOVE)
286
287 SET @sql = N'';
288
289 SELECT @sql += N'RESTORE DATABASE ' + QUOTENAME(db) -- ** RESTORE HAPPENS HERE **
290 + ' FROM DISK = ''' + @path + db + '.BAK'' WITH REPLACE, NORECOVERY;
291 RESTORE LOG ''' + @path + db + '.TRN'' WITH NORECOVERY;
292 ALTER DATABASE ' + QUOTENAME(db) + ' SET HADR AVAILABILITY GROUP = '
293 + QUOTENAME(@group) + ';'
294 FROM @t;
295
296 SET @remote_sql = N'';
297
298 SELECT @remote_sql += N'EXEC ' + QUOTENAME(s) + '.master..sp_executesql @sql;'
299 FROM @r;
300
301 IF @debug = 1
302 BEGIN
303 PRINT @sql;
304 PRINT @remote_sql;
305 END
306 ELSE
307 BEGIN
308 EXEC sp_executesql @remote_sql, N'@sql NVARCHAR(MAX)', N'SELECT @@SERVERNAME;';
309 END
310END
311GO
312
313EXEC dbo.AddNewDBsToGroup @debug = 1;
314
315EXEC dbo.AddNewDBsToGroup @debug = 0;
316
317/**
318 * AlwaysOn Self-Population Script
319 * By: Steve Gray / steve@mostlyharmful.net
320 * Usage: Free, but buy me a beer if you're ever in Brisbane.
321 **/
322USE [master]
323GO
324IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
325 BEGIN
326 DROP TABLE hadr_pending_replicate;
327 END;
328GO
329CREATE TABLE hadr_pending_replicate (database_name VARCHAR(512) PRIMARY KEY NOT NULL, availability_group_name VARCHAR(2048) NOT NULL);
330GO
331IF EXISTS (SELECT * FROM sys.server_triggers WHERE name='ddl_hadr_autoreplicate')
332 BEGIN
333 DROP TRIGGER ddl_hadr_autoreplicate ON ALL SERVER;
334 END;
335GO
336CREATE TRIGGER ddl_hadr_autoreplicate ON ALL SERVER
337 FOR CREATE_DATABASE
338AS
339BEGIN
340 SET NOCOUNT ON;
341
342 DECLARE @DatabaseName NVARCHAR(2048)
343
344 -- Find the availability group, if the CREATE DATABASE is occuring on an AG listener.
345 DECLARE @AddToGroupName VARCHAR(512);
346 SELECT TOP 1
347 @AddToGroupName = [AG].[name]
348 FROM
349 sys.availability_groups AS [AG]
350 INNER JOIN sys.availability_group_listeners AS [LS] ON [LS].[group_id] = [AG].[group_id]
351 INNER JOIN sys.availability_group_listener_ip_addresses AS [IP] ON [IP].[listener_id] = [LS].[listener_id]
352 INNER JOIN sys.dm_exec_connections AS [CN] ON [CN].[local_net_address] = [IP].[ip_address] AND [CN].[local_tcp_port] = [LS].[port]
353 WHERE
354 [CN].[session_id] = @@SPID;
355
356 SET @DatabaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'));
357
358 -- We have to use a queue since initial backups cant happen during the CREATE DATABASE trigger firing.
359 IF @AddToGroupName IS NOT NULL
360 BEGIN
361 PRINT 'Database is queueing for HADR replication';
362 DELETE FROM hadr_pending_replicate WHERE database_name = @DatabaseName;
363 INSERT INTO hadr_pending_replicate SELECT @DatabaseName, @AddToGroupName;
364 END
365END;
366GO
367IF EXISTS (SELECT * FROM sys.procedures WHERE name='hadr_process_replicate')
368 BEGIN
369 DROP PROCEDURE hadr_process_replicate;
370 END;
371GO
372/**
373 * This script automatically performs a few tasks when a database is created via a connection
374 * to a SQL Server availability group listener:
375 *
376 * 1) Switch the database from 'SIMPLE' to 'FULL' recovery.
377 * 2) Perform a full backup to the default backup path for the server.
378 * 3) Connect to other servers in the availability group and stage a WITH NO RECOVERY restore.
379 * 4) Add the database to the availability group and initialize AlwaysOn.
380 *
381 * For this to work, you must have linked servers on all your nodes with the same name as the Windows
382 * host name (i.e. SERVERNAME). If in doubt, look at replica_server_name from sys.availability_replicas.
383 * Linked servers must have RPC and RPC OUT options set to true. Script assumes that the backup destination
384 * is accessible to every other server too.
385 **/
386CREATE PROCEDURE hadr_process_replicate
387 @AddToGroupName VARCHAR(2048),
388 @DatabaseName VARCHAR(2048)
389AS
390BEGIN
391 DECLARE @AvailabilityGroupID UNIQUEIDENTIFIER;
392 DECLARE @BackupDestination VARCHAR(2048);
393 DECLARE @BackupSuffix VARCHAR(2048) = '_Initial.bak';
394
395 SET @AvailabilityGroupID = (SELECT group_id FROM sys.availability_groups WHERE name=@AddToGroupName);
396
397 -- Switch the database to FULL recovery if it was created without it.
398 IF (SELECT recovery_model FROM sys.databases WHERE name=@DatabaseName) <> 1
399 BEGIN
400 PRINT 'Changing recovery model to FULL';
401 DECLARE @ModeChange NVARCHAR(512) = 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY FULL WITH NO_WAIT';
402 EXEC sp_executesql @ModeChange;
403 END;
404 ELSE
405 BEGIN
406 PRINT 'Database is already in FULL recovery mode.'
407 END;
408
409
410 -- Read the default backup path from the SQL Server configuration here. This path needs to be accessible to all the servers in
411 -- the availability group.
412 EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SoftwareMicrosoftMSSQLServerMSSQLServer',N'BackupDirectory', @BackupDestination OUTPUT, 'no_output';
413 DECLARE @TargetFile VARCHAR(2048) = @BackupDestination + '' + @DatabaseName + @BackupSuffix;
414
415 -- Perform initial backup of the database - Will overwrite any existing file.
416 PRINT 'Backing up initial database to ' + @TargetFile;
417 DECLARE @BackupCommand NVARCHAR(2048) = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = N''' + @TargetFile + ''' WITH INIT, NOFORMAT, NAME = N''Initial backup for HADR seeding'', SKIP, NOREWIND, NOUNLOAD, STATS = 100';
418 PRINT ' Command: ' + @BackupCommand
419 EXEC (@BackupCommand)
420
421 PRINT 'Joining database to availability group'
422 DECLARE @JoinToAG NVARCHAR(2048) = 'ALTER AVAILABILITY GROUP [' + @AddToGroupName + '] ADD DATABASE [' + @DatabaseName + ']';
423 EXEC sp_executesql @JoinToAG;
424
425 -- Loop through all availability replicas
426 DECLARE @Replicas TABLE (ReplicaName VARCHAR(512))
427 INSERT INTO @Replicas -- Have to use a table, since T-SQL wasnt giving me
428 -- all replicas when I did this straight via the cursor.... (Bug?)
429 SELECT replica_server_name
430 FROM
431 sys.availability_replicas
432 WHERE group_id=CAST(@AvailabilityGroupID AS VARCHAR(512));
433
434 DECLARE cur_Replicas CURSOR FOR SELECT ReplicaName FROM @Replicas INNER JOIN sys.servers [SV] ON [SV].[name] = [ReplicaName] AND [SV].[is_linked] = 1 ORDER BY [ReplicaName];
435 OPEN cur_Replicas;
436 DECLARE @CurrentReplica VARCHAR(255)
437 FETCH NEXT FROM cur_Replicas INTO @CurrentReplica;
438 WHILE @@FETCH_STATUS >= 0
439 BEGIN
440 PRINT 'Restoring initial backup to ' + @CurrentReplica;
441 DECLARE @RestoreCommand VARCHAR(2048) = 'RESTORE DATABASE [' + @DatabaseName + '] FROM DISK = N''' + @TargetFile + ''' WITH NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
442 -- The 'inception' moment.
443 DECLARE @DoubleDynamicRestore NVARCHAR(2048) = 'EXEC (''' + REPLACE(@RestoreCommand, '''', '''''') + ''') AT [' + @CurrentReplica + ']';
444 PRINT @DoubleDynamicRestore
445 EXEC sp_executesql @DoubleDynamicRestore;
446
447 PRINT 'Bringing replica online'
448 DECLARE @DynamicAddHADR NVARCHAR(2048) = 'EXEC (''ALTER DATABASE [' + @DatabaseName + '] SET HADR AVAILABILITY GROUP = [' + @AddToGroupName +'];'') AT [' + @CurrentReplica +']';
449 EXEC sp_executesql @DynamicAddHADR
450
451 FETCH NEXT FROM cur_Replicas INTO @CurrentReplica;
452 END;
453 CLOSE cur_Replicas;
454 DEALLOCATE cur_Replicas;
455END;
456GO
457IF EXISTS (SELECT * FROM sys.procedures WHERE name='hadr_replicate_queue')
458 BEGIN
459 DROP PROCEDURE hadr_replicate_queue;
460 END;
461GO
462/**
463 * Process all pending HADR replicates.
464 **/
465CREATE PROCEDURE hadr_replicate_queue
466AS
467BEGIN
468 DECLARE cur_ReplicationTask CURSOR FOR
469 SELECT database_name, availability_group_name FROM hadr_pending_replicate WITH(HOLDLOCK)
470 OPEN cur_ReplicationTask;
471
472 DECLARE @DB VARCHAR(512), @AG VARCHAR(2048)
473 FETCH NEXT FROM cur_ReplicationTask INTO @DB, @AG
474 WHILE @@FETCH_STATUS = 0
475 BEGIN
476 EXEC hadr_process_replicate @AddToGroupName = @AG, @DatabaseName = @DB
477 FETCH NEXT FROM cur_ReplicationTask INTO @DB, @AG
478 END;
479
480 DELETE FROM hadr_pending_replicate;
481
482 CLOSE cur_ReplicationTask;
483 DEALLOCATE cur_ReplicationTask
484END;
485GO
486
487xp_cmdshell 'del /F /Q /S \atel-webbe1backups*.*'
488
489exec xp_cmdshell 'del /F /Q /S \atel-webbe2backups*.*'
490
491USE master
492
493DECLARE @secondaryservername nvarchar(50)
494
495DECLARE @primaryservername nvarchar(50)
496
497DECLARE @availabilitygroup nvarchar(50)
498
499SET @secondaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states
500 , sys.dm_hadr_availability_replica_cluster_states
501where role_desc = 'SECONDARY' AND sys.dm_hadr_availability_replica_states.replica_id =
502sys.dm_hadr_availability_replica_cluster_states.replica_id)
503
504SET @primaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states
505 , sys.dm_hadr_availability_replica_cluster_states
506where role_desc = 'PRIMARY' AND sys.dm_hadr_availability_replica_states.replica_id =
507sys.dm_hadr_availability_replica_cluster_states.replica_id)
508
509
510SET @availabilitygroup = (SELECT name FROM [sys].[availability_groups])
511
512
513IF OBJECT_ID('dbo.#dbs', 'U') IS NOT NULL
514 DROP TABLE dbo.#dbs
515
516IF (SELECT CURSOR_STATUS('global','adddbs')) >=0
517BEGIN
518DEALLOCATE adddbs
519END
520
521create table #dbs(a int primary key identity, dbname varchar(100))
522declare @nextdb varchar(100)
523declare @restorestring varchar(400)
524
525--Populate temp table
526insert into #dbs(dbname)
527
528select name from sys.databases
529where group_database_id is null and replica_id is null
530 and name not in('master','model','msdb','tempdb')
531
532--Create a cursor to
533declare adddbs cursor for
534select dbname from #dbs
535
536open adddbs
537
538FETCH NEXT FROM adddbs
539INTO @nextdb
540
541WHILE @@FETCH_STATUS = 0
542BEGIN
543EXEC ('ALTER DATABASE' + '[' + @nextdb + ']' + 'set RECOVERY FULL')
544EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\' +@primaryservername+'backups' + '[' + @nextdb + ']' + 'initialize.bak''')
545
546EXEC ('ALTER AVAILABILITY GROUP ['+ @availabilitygroup +'] ADD DATABASE ' + '[' + @nextdb + ']')
547
548
549EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\'@primaryservername+'backups' + '[' + @nextdb + ']' + '.bak''')
550
551EXEC ('BACKUP LOG ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\'@primaryservername+'backups' + '[' + @nextdb + ']' + '_log.bak''')
552
553set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE DATABASE ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\' +@primaryservername +'backups' + '[' + @nextdb + ']' + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"'
554exec xp_cmdshell @restorestring
555
556set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE LOG ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\' +@primaryservername+'backups' + '[' + @nextdb + ']' + '_log.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"'
557exec xp_cmdshell @restorestring
558
559set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"ALTER DATABASE ' + '[' + @nextdb + ']' + ' SET HADR AVAILABILITY GROUP = [' + @availabilitygroup +']"'
560exec xp_cmdshell @restorestring
561
562FETCH NEXT FROM adddbs
563 INTO @nextdb
564END
565
566CLOSE adddbs
567DEALLOCATE adddbs