SQL remote backups

SQL Backup Master is capable of backing up remote databases hosted by SQL Server instances across the network.

Overview

SQL Backup Master uses a BACKUP DATABASE command to back up SQL Server databases.

This allows for backups across the network as long as the remote SQL Server instance and SQL Backup Master have read/write access to the same temporary backup storage folder.

If your remote SQL Server runs on Linux, the same shared-temp-folder model applies – configured via Samba on the Linux host. See Backing up SQL Server on Linux for the full walkthrough.

Setting up a remote backup

Two paths lead to a working remote backup. The Remote Setup script generator (new in v8.5) is the fastest correct configuration and is recommended for new setups. The manual walk-through that follows it covers pre-v8.5 releases and environments where the generated script can't be run.

Recommended: Remote Setup script generator (v8.5+)

1. In SQL Backup Master, create a backup job. Use the Choose SQL Server button to select the remote SQL Server instance, then select the databases you want to back up.
2. Add at least one backup destination.
3. Click the Backup job settings link in the lower left-hand corner of the Database Backup Editor window.
4. On the General tab, click the Remote Setup button next to the Temporary Backup Folder field. SBM auto-detects the SQL Server service identity and pre-fills the SBM identity. Adjust the folder path or share name if you want non-default values.
5. Click Generate script, then Copy script or Save as .ps1 to move the script to your SQL Server host.
6. Run the script from an elevated PowerShell prompt on the SQL Server host. It creates the folder, creates the share, and grants Modify permissions to both identities on both the share and NTFS layers.
7. Back in SQL Backup Master, set the Temporary Backup Folder field to the UNC path shown in the Remote Setup window (e.g. \\server\SQLBackupMaster). The dialog includes a Copy path button to copy it.
8. Save the backup job and run it manually to verify.

The generated script implements the Standard permissions configuration described in the Permissions section below. If your scenario calls for the Hardened (authenticated UNC) configuration instead, run the generated script first, then layer the authenticated-UNC step on top of it.

Manual setup (pre-v8.5 or advanced configurations)

IMPORTANT: When configuring remote backup scenarios, some steps take place on your Microsoft SQL Server host computer while others occur on the computer running SQL Backup Master.

On the SQL Server host machine:

1. Create a folder on the SQL Server host machine (for example, "c:\SQLBackupMaster") for holding temporary database backup files.
2. Create a share for the above folder, assigning it whatever name you prefer (e.g. "SQLBackupMaster").
3. For now, grant the "Everyone" group "Full Control" over the folder and the share created above — you'll lock this down after testing (see Permissions below).

On the SQL Backup Master host machine:

1. On the SQL Backup Master host machine, start SQL Backup Master and create a backup job and use the Choose SQL Server button to select the networked SQL Server instance, then select the databases you wish to back up.
2. Add at least one backup destination.
3. Click the Backup job settings link that appears in the lower left-hand corner of the Database Backup Editor window.
4. On the General tab of the Backup Job Settings window, enter the UNC path to the share created above (e.g. "\\server\SQLBackupMaster") into the Temporary Backup Folder field. If you'd like SQL Backup Master to authenticate to the share with dedicated credentials rather than its current service identity, click Advanced and supply them now — see the Permissions section below for when this is preferable.

Once the steps above are completed, save the backup job and then attempt to run it manually. After you've confirmed the job runs end-to-end, lock down the share permissions per the Permissions section below.

Permissions

The "Everyone: Full Control" baseline used by the manual walk-through is the simplest configuration and useful for first-run verification, but it's not the right end state for production because any local user on either host can read the in-flight .bak files, which contain full database contents. (The Remote Setup script generator skips this state entirely and applies the Standard configuration directly.)

Two distinct Windows identities need access to the share, for different reasons:

• The SQL Backup Master identity — the Windows account SBM runs the job under. In most installations this is an impersonation account configured during the SQL Server connection step (pass-through Windows authentication automatically enables impersonation at the job level). The backup job editor displays the current effective value adjacent to the SQL Server instance — that's the account that needs share access. If you're not using pass-through credentials and haven't configured a per-job impersonation account, this is the SBM Windows service account (LocalSystem by default).
• The SQL Server service identity on the remote host — the Windows account SQL Server runs under, which is what actually executes the BACKUP DATABASE command. Find it via services.msc on the SQL Server host → SQL Server (MSSQLSERVER or MSSQL$<instance>) → Properties → Log On. For virtual-account installs this appears as NT SERVICE\MSSQLSERVER; grant ACLs to that string the same way you would a domain user (note that Windows' Select User dialog doesn't list virtual accounts — you'll need to type the name in directly or use icacls from an elevated prompt).

Within a backup job's lifecycle, each identity does different work in the share:

• The SQL Server service identity writes the .bak file when BACKUP DATABASE runs.
• The SBM identity reads the .bak to upload to destinations, writes intermediate compressed and/or encrypted artifacts when those features are enabled, and deletes everything during cleanup.

Either identity that does both read and write operations needs Modify-or-better access. The three configurations below are presented from least to most locked-down:

Diagnostic configuration

The "Everyone: Full Control" baseline from the manual walk-through. Useful for first-run verification — especially when you're not sure whether share access is the failing step. Not recommended for production: any local user on either host can read the in-flight .bak files.

Standard configuration (explicit grants)

Remove the "Everyone" (or "Users") ACE from both the share permissions and the underlying NTFS folder, then add Modify or Full Control for two principals on both layers:

1. The SBM identity, as shown in the backup job editor.
2. The SQL Server service identity on the remote host.

Effective permission is the more restrictive of share and NTFS — both layers must allow access. Order matters: remove "Everyone" only after you've confirmed both explicit grants are in place on both layers, or you'll lock yourself out of the share momentarily.

The Remote Setup script generator (v8.5+) creates the share with this exact configuration in a single step — see Setting up a remote backup above.

Hardened configuration (authenticated UNC)

Click the Advanced button next to the Temporary Backup Folder field in the Backup Job Settings window and supply a dedicated low-privilege account's credentials. SBM authenticates to the share with these credentials rather than the SBM identity, so the share's ACL only needs that one account plus the SQL Server service identity. This is the cleanest option for cross-domain, workgroup, or hardened-network environments where you don't want the SBM service account (or the per-job impersonation account) to have direct share access.

Recovery scenarios

When you use SQL Backup Master to recover a database from a remote SQL Server backup, the same share is used in reverse: SBM writes the downloaded .bak file into the share, and SQL Server reads it to run RESTORE. The same Standard permission model covers both backup and recovery directions — no separate setup needed.

Relationship to v8.5 temp folder hardening

SQL Backup Master v8.5 hardened NTFS permissions on the default local temp folder. Remote backups by definition use a customized temp folder (a UNC path), which opts out of that managed-permissions model — for remote scenarios, you manage the share's permissions yourself using one of the three configurations above (the Remote Setup script generator does this for you for the Standard configuration). See Backup Temp Folder Permissions for details on which configurations trigger SBM-managed permissions and which are customer-managed.

Troubleshooting

If your remote database backup job isn't running to successful completion, start by checking the job's backup log files. The logs should contain an entry that explains the problem.

Some errors you may encounter include:

1. Login failed for user — A SQL Server authentication failure. The Windows account SBM is using to connect to SQL Server (visible in the backup job editor next to the SQL Server instance) does not have permission to back up the selected database(s). Verify that the account is a member of the sysadmin SQL Server role, or has at least the db_backupoperator role on each target database. Note that this is distinct from share-access problems below — "Login failed" specifically means the SQL Server login attempt was rejected, not the Windows share access.
2. Access to path denied — A Windows share or NTFS permission problem on the SBM side: SBM couldn't read or write a file in the temp share. Confirm both identities (SBM identity and SQL Server service identity, per the Permissions section above) have Modify or Full Control on both the share permissions and the NTFS folder ACL. For virtual accounts (NT SERVICE\MSSQLSERVER and similar), the Windows Select User dialog doesn't list them — you'll need to type the account name in directly or use icacls from an elevated prompt.
3. Operating system error 5 (Access is denied) — A SQL Server-side permission problem, distinct from "Access to path denied" above: the BACKUP DATABASE command failed because the SQL Server service identity couldn't write the .bak file to the share. Re-verify the SQL Server identity's permissions on the share per the Permissions section above. If you recently changed the SQL Server service account, restart the SQL Server service so the new identity is in effect for the share access.
4. Cannot open backup device — Please see our Troubleshooting Operating System Error 3 article for a thorough discussion of this problem.