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_PATHDIR'
--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_PATHDIR'
--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
zabolab 