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

Be the first to comment

Leave a Reply

Your email address will not be published.


*