AlwaysOn : Manually add a new replica node using a custom backup path with custom credentials

Management Studio wizards are powerful and can do many things automatically… but sometimes you have to do something manually.
In this case we have to do manually backup and restore steps before running the ‘Add node Wizard’ in Join mode.
This because the add process cannot impersonate a custom user to access a remote backup path.
In this procedure we’ll mount a backup path using custom credentials. Then we’ll perform a backup and restore no-recovery for every databases protected by alwayson on a new replication node.

--EXECUTE THIS ON THE PRIMARY NODE

--CONFIGURE HERE----------------------------------------- 

--SET DESTINATION BACKUP PATH
DECLARE @destpath nvarchar(255) = '\\REMOTE_PATH\DIR\'

--SET ‘YES’ TO RUN NOW STATEMENTS OR ‘NO’ TO GENERATE ONLY SCRIPTS
DECLARE @runnow nvarchar(3) = 'NO'

--SET THE MOUNT command (needs xp_cmdshell active)
DECLARE @mount_cmd as nvarchar(4000) = 'net use \\REMOTE_PATH /user:myuser mypassword'

--SET THE UNMOUNT command (needs xp_cmdshell active)
DECLARE @dismount_cmd as nvarchar(4000) = 'net use \\REMOTE_PATH /delete'
---------------------------------------------------------

DECLARE @DbName as VARCHAR(40)
DECLARE @primary as VARCHAR(256)
DECLARE @cmd as nvarchar(4000)
DECLARE @cmd2 as nvarchar(4000)
DECLARE @cmd3 as nvarchar(4000)

DECLARE @ext as char(4)= '.bak'
DECLARE @msgerror as varchar(30)

select @primary = primary_replica  from sys.dm_hadr_availability_group_states

–- list all database alwayson protected
-- Note: in ANY availability group

declare cursor_DatabasesToProtect CURSOR FOR select name from master.sys.sysdatabases  where name in (select db_name(database_id) from sys.dm_hadr_database_replica_states)

IF (@runnow='YES')
BEGIN
 exec xp_cmdshell(@mount_cmd)
END

OPEN cursor_DatabasesToProtect
FETCH NEXT FROM cursor_DatabasesToProtect INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
                                               
	set @cmd= 'BACKUP DATABASE [' +  @DbName + '] TO  DISK = ''' + @destpath +  @DbName + '_FULL'' WITH NOFORMAT, COMPRESSION, NOINIT,  NAME = N''' + @DbName + 
	'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
                   
	set @cmd2= 'BACKUP LOG [' +  @DbName + '] TO  DISK = ''' + @destpath +  @DbName + '_LOG1'' WITH NOFORMAT, COMPRESSION, NOINIT,  NAME = N''' + @DbName + 
	'-Log Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

	set @cmd3= 'BACKUP LOG [' +  @DbName + '] TO  DISK = ''' + @destpath +  @DbName + '_LOG2'' WITH NOFORMAT, COMPRESSION, NOINIT,  NAME = N''' + @DbName + 
	'-Log Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

	BEGIN TRY
		print '----------------------'
	
IF (@runnow='YES')
	BEGIN
			print @cmd
			exec (@cmd)
			print @cmd2
			exec (@cmd2)
			print @cmd3
			exec (@cmd3)
		END
		ELSE
		BEGIN
			print @cmd
			print @cmd2
			print @cmd3

		END
		print '----------------------'
		
       END TRY
	BEGIN CATCH
		set @msgerror='BACKUP FAILED FOR ' +  @DbName
		print @msgerror
	END CATCH

	FETCH NEXT FROM cursor_DatabasesToProtect INTO @DbName
END

close cursor_DatabasesToProtect
deallocate cursor_DatabasesToProtect

IF (@runnow='YES')
BEGIN
 exec xp_cmdshell(@dismount_cmd)
END

--EXECUTE THIS ON THE NEW SECONDARY NODE

--CONFIGURE HERE----------------------------------------- 

--SET DESTINATION BACKUP PATH
DECLARE @destpath nvarchar(255) = '\\REMOTE_PATH\DIR\'

--SET ‘YES’ TO RUN NOW STATEMENTS OR ‘NO’ TO GENERATE ONLY SCRIPTS
DECLARE @runnow nvarchar(3) = 'NO'

--SET THE MOUNT command (needs xp_cmdshell active)
DECLARE @mount_cmd as nvarchar(4000) = 'net use \\REMOTE_PATH /user:myuser mypassword'

--SET THE UNMOUNT command (needs xp_cmdshell active)
DECLARE @dismount_cmd as nvarchar(4000) = 'net use \\REMOTE_PATH /delete'
---------------------------------------------------------

DECLARE @DbName as VARCHAR(40)
DECLARE @primary as VARCHAR(256)
DECLARE @cmd as nvarchar(4000)
DECLARE @cmd2 as nvarchar(4000)
DECLARE @cmd3 as nvarchar(4000)

DECLARE @ext as char(4)= '.bak'
DECLARE @msgerror as varchar(30)

select @primary = primary_replica  from sys.dm_hadr_availability_group_states

–- list all database alwayson protected
-- Note: in ANY availability group

declare cursor_DatabasesToProtect CURSOR FOR select name from master.sys.sysdatabases  where name in (select db_name(database_id) from sys.dm_hadr_database_replica_states)

IF (@runnow='YES')
BEGIN
 exec xp_cmdshell(@mount_cmd)
END

OPEN cursor_DatabasesToProtect
FETCH NEXT FROM cursor_DatabasesToProtect INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
                                               
    set @cmd= 'RESTORE DATABASE [' +  @DbName + '] FROM  DISK = ''' + @destpath +  @DbName + '_FULL'',  STATS = 1, NORECOVERY'
    set @cmd2= 'RESTORE DATABASE [' +  @DbName + '] FROM  DISK = ''' + @destpath +  @DbName + '_LOG1'',  STATS = 1, NORECOVERY'
    set @cmd3= 'RESTORE DATABASE [' +  @DbName + '] FROM  DISK = ''' + @destpath +  @DbName + '_LOG2'',  STATS = 1, NORECOVERY'


	BEGIN TRY
		print '----------------------'
	
IF (@runnow='YES')
	BEGIN
			print @cmd
			exec (@cmd)
			print @cmd2
			exec (@cmd2)
			print @cmd3
			exec (@cmd3)
		END
		ELSE
		BEGIN
			print @cmd
			print @cmd2
			print @cmd3

		END
		print '----------------------'
		
       END TRY
	BEGIN CATCH
		set @msgerror='RESTORE FAILED FOR ' +  @DbName
		print @msgerror
	END CATCH

	FETCH NEXT FROM cursor_DatabasesToProtect INTO @DbName
END

close cursor_DatabasesToProtect
deallocate cursor_DatabasesToProtect

IF (@runnow='YES')
BEGIN
 exec xp_cmdshell(@dismount_cmd)
END

If you see no errors now you are ready to use management studio wizard to add the new node to alwayson replication

About Paolo Zaboi 44 Articles
Fermamente convinto che "I predatori dell'Arca Perduta" sia uno dei migliori film della storia del cinema, vive e passa il suo tempo in quel di Milano. Adora i film commercialissimi, tutto ciò che è Marvel, le vecchie avventure grafiche della Lucas e le serie TV. Giocatore (nel poco tempo libero) su Pc e Playstation, con fiducia e speranza, attende dagli anni '80 l'uscita della prossima console Atari. Ha creato questo blog un po' per spirito di condivisione... è un po' per scrivere in libertà di tutto quello che gli piace. Odia leggere i lunghi profili sui blog per questo il resto lo trovate solo su Linkedin: https://www.linkedin.com/in/paolozaboi ------ Firmly convinced that "Riders of the Lost Ark" is one of the best movies in cinema history, he lives and spend all his time in Milan. He loves movies, everything Marvel, Lucas's old graphic adventures and TV series. Player (in leisure time) on PC and Playstation, with confidence and hope, expects the release of the next Atari console from the 1980's. He created this blog (maybe...) for sharing spirit ... or simply to write free about anything he loves. He hates reading long profiles on blogs so all the rest is on Linkedin: https://www.linkedin.com/in/paolozaboi

Be the first to comment

Leave a Reply

Your email address will not be published.


*