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

SQL Server Management Studio’s AlwaysOn Availability Groups wizard covers most common scenarios well — but it has a significant limitation: it cannot impersonate a custom user to access a remote backup path with non-default credentials. When your backup share requires a specific domain account or a custom username/password combination, you need to perform the initial backup and restore steps manually, then use the wizard in Join only mode.

This procedure automates that manual work: it mounts the backup path with custom credentials, creates a full backup plus two transaction log backups of every database protected by AlwaysOn, and generates (or executes) the corresponding restore statements on the new secondary node.

Prerequisites

xp_cmdshell must be enabled on both the primary and secondary nodes (used for net use mount/dismount)
The SQL Server service account needs appropriate permissions on the destination backup path
Databases must be in FULL recovery model to support log backups
Run the primary script on the primary node, the secondary script on the new secondary node

Step 1: Backup All AlwaysOn Databases (Run on Primary Node)

The script mounts the remote backup path, iterates over all databases currently protected by any AlwaysOn Availability Group, and generates (or executes) a full backup + two log backups per database. Set @runnow = 'YES' to execute immediately, or leave it as 'NO' to preview the generated statements first.

-- EXECUTE THIS ON THE PRIMARY NODE

-- CONFIGURE HERE ----------------------------------------
DECLARE @destpath    NVARCHAR(255)  = 'REMOTE_PATHDIR'
DECLARE @runnow      NVARCHAR(3)    = 'NO'   -- 'YES' to execute, 'NO' to preview only
DECLARE @mount_cmd   NVARCHAR(4000) = 'net use REMOTE_PATH /user:myuser mypassword'
DECLARE @dismount_cmd NVARCHAR(4000) = 'net use REMOTE_PATH /delete'
-- -------------------------------------------------------

DECLARE @DbName    VARCHAR(40)
DECLARE @primary   VARCHAR(256)
DECLARE @cmd       NVARCHAR(4000)
DECLARE @cmd2      NVARCHAR(4000)
DECLARE @cmd3      NVARCHAR(4000)
DECLARE @msgerror  VARCHAR(80)

SELECT @primary = primary_replica FROM sys.dm_hadr_availability_group_states

-- Mount the remote path with custom credentials
IF (@runnow = 'YES')
    EXEC xp_cmdshell(@mount_cmd)

-- Iterate over all AlwaysOn-protected databases
DECLARE cursor_DBs CURSOR FOR
    SELECT name FROM master.sys.sysdatabases
    WHERE name IN (SELECT DB_NAME(database_id) FROM sys.dm_hadr_database_replica_states)

OPEN cursor_DBs
FETCH NEXT FROM cursor_DBs 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'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
    SET @cmd2 = 'BACKUP LOG ['      + @DbName + '] TO DISK = ''' + @destpath + @DbName + '_LOG1'' WITH NOFORMAT, COMPRESSION, NOINIT, NAME = N''' + @DbName + '-Log1'',  SKIP, NOREWIND, NOUNLOAD, STATS = 10'
    SET @cmd3 = 'BACKUP LOG ['      + @DbName + '] TO DISK = ''' + @destpath + @DbName + '_LOG2'' WITH NOFORMAT, COMPRESSION, NOINIT, NAME = N''' + @DbName + '-Log2'',  SKIP, NOREWIND, NOUNLOAD, STATS = 10'

    BEGIN TRY
        PRINT '-- DB: ' + @DbName
        IF (@runnow = 'YES')
        BEGIN
            EXEC(@cmd);  EXEC(@cmd2);  EXEC(@cmd3)
        END
        ELSE
        BEGIN
            PRINT @cmd;  PRINT @cmd2;  PRINT @cmd3
        END
    END TRY
    BEGIN CATCH
        SET @msgerror = 'BACKUP FAILED FOR ' + @DbName + ': ' + ERROR_MESSAGE()
        PRINT @msgerror
    END CATCH

    FETCH NEXT FROM cursor_DBs INTO @DbName
END

CLOSE cursor_DBs
DEALLOCATE cursor_DBs

IF (@runnow = 'YES')
    EXEC xp_cmdshell(@dismount_cmd)

Step 2: Restore with NORECOVERY (Run on New Secondary Node)

On the new secondary, mount the same backup share and restore all databases with NORECOVERY — this leaves them in a restoring state, ready to be joined to the Availability Group. The structure mirrors the primary script; configure the same paths and credentials.

-- EXECUTE THIS ON THE NEW SECONDARY NODE

-- CONFIGURE HERE ----------------------------------------
DECLARE @destpath    NVARCHAR(255)  = 'REMOTE_PATHDIR'
DECLARE @runnow      NVARCHAR(3)    = 'NO'
DECLARE @mount_cmd   NVARCHAR(4000) = 'net use REMOTE_PATH /user:myuser mypassword'
DECLARE @dismount_cmd NVARCHAR(4000) = 'net use REMOTE_PATH /delete'
-- -------------------------------------------------------

DECLARE @DbName   VARCHAR(40)
DECLARE @cmd      NVARCHAR(4000)
DECLARE @cmd2     NVARCHAR(4000)
DECLARE @cmd3     NVARCHAR(4000)
DECLARE @msgerror VARCHAR(80)

IF (@runnow = 'YES')
    EXEC xp_cmdshell(@mount_cmd)

DECLARE cursor_DBs CURSOR FOR
    SELECT name FROM master.sys.sysdatabases
    WHERE name IN (SELECT DB_NAME(database_id) FROM sys.dm_hadr_database_replica_states)

OPEN cursor_DBs
FETCH NEXT FROM cursor_DBs INTO @DbName

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

    BEGIN TRY
        PRINT '-- DB: ' + @DbName
        IF (@runnow = 'YES')
        BEGIN
            EXEC(@cmd);  EXEC(@cmd2);  EXEC(@cmd3)
        END
        ELSE
        BEGIN
            PRINT @cmd;  PRINT @cmd2;  PRINT @cmd3
        END
    END TRY
    BEGIN CATCH
        SET @msgerror = 'RESTORE FAILED FOR ' + @DbName + ': ' + ERROR_MESSAGE()
        PRINT @msgerror
    END CATCH

    FETCH NEXT FROM cursor_DBs INTO @DbName
END

CLOSE cursor_DBs
DEALLOCATE cursor_DBs

IF (@runnow = 'YES')
    EXEC xp_cmdshell(@dismount_cmd)

Step 3: Join the Node via SSMS Wizard

Once both scripts complete without errors, all databases on the new secondary are in RESTORING state. Open SSMS on the primary, launch the Add Replica wizard, and choose Join only mode. The wizard will join the secondary to the Availability Group and begin synchronization without needing to perform its own backup/restore cycle.

Verify synchronization health in the AlwaysOn Dashboard or with:

SELECT
    ag.name                         AS availability_group,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    DB_NAME(drs.database_id)        AS database_name
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups   ag ON ar.group_id    = ag.group_id
ORDER BY ag.name, ar.replica_server_name;