SQL Server Backup

Overview

BackupEngine integrates with Microsoft SQL Server to provide application-consistent database backups. The agent communicates with SQL Server through the VDI (Virtual Device Interface) to create reliable backups without stopping the database engine or interrupting queries.

• •Supports SQL Server 2016, 2017, 2019, and 2022 (Express, Standard, and Enterprise editions).
• •Full, differential, and transaction log backups are all supported.
• •Backups are compressed, encrypted, and deduplicated before upload, just like file backups.
• •Available on Server plans only. The SQL Server tab appears automatically when a SQL instance is detected.

Backup Types

BackupEngine supports three SQL Server backup types. Use them together for a comprehensive recovery strategy.

• •Full Backup: Captures the entire database, including all data and schema. Run weekly or daily depending on database size.
• •Differential Backup: Captures only data that has changed since the last full backup. Faster and smaller than full backups. Run daily or every few hours.
• •Transaction Log Backup: Captures all transactions since the last log backup. Enables point-in-time recovery to any moment. Run every 15 to 60 minutes for critical databases.

Setting Up a SQL Server Backup

SQL Server backup-set creation is GUI-only — the wizard handles instance detection, authentication, database selection, multi-destination fan-out, and per-backup-type scheduling. The CLI doesn't yet expose a SQL-specific create/restore subcommand; once a SQL set exists, you can run it from the CLI like any other backup set via backupengine backup start "<set-name>".

• •Step 1: SQL Instance — Select the SQL Server instance to protect.
• •Step 2: Authentication — Pick Windows or SQL Server Authentication. For SQL auth, enter the username + password and click Test Connection (runs SELECT 1 against the instance to verify before save). Credentials encrypt with AES-256-GCM and live in the per-machine credential vault — backup sets store only the credential ID, never the password.
• •Step 3: Databases & Types — Select which databases to back up. For each, choose backup types: Full, Differential, and/or Transaction Log.
• •Step 4: Destination — Choose one or more destinations: iDrive e2 (cloud), Local PC, Network PC, Google Drive, OneDrive. Multi-destination fan-out is supported.
• •Step 5: Schedule — Set when each backup type runs. Example: Full weekly Sunday 01:00, Differential daily 23:00, Logs every 30 minutes.
• •Step 6: Review — Verify your choices and click Create Backup Set.

SQL Authentication & the Credential Vault

SQL Server Authentication is first-class in v1.4.0. The wizard's Authentication step lets you pick Windows or SQL auth, enter a username + obscured password (with show/hide toggle), and run a Test Connection probe before saving. Saved credentials are bound to the device they were saved on.

• •Encryption: AES-256-GCM. Per-device random salt, key derived as SHA-256(salt || hostname || pepper). Credentials cannot be lifted off the machine and decrypted on a different host — the hostname binding is by design.
• •Storage: %APPDATA%\net.techbench.backupengine\Backup Engine\credentials\vault.enc — an opaque encrypted blob. The companion .salt file holds the per-device salt.
• •Backup sets store only the credential ID (e.g. "sql:MYSERVER\MSSQLSERVER"). The username and password never leave the vault.
• •Logging: sqlcmd argument logger redacts -P <password> to -P *** in every diagnostic log entry. Passwords from the SQL auth flow never hit disk in plaintext anywhere.
• •Management: Settings → Security → Manage SQL Credentials lists every saved credential, lets you edit (re-encrypt with a new password) or delete entries.

Running and Inspecting SQL Backup Sets

Once a SQL backup set exists, both the GUI and the CLI can trigger ad-hoc runs and inspect recent jobs.

backupengine sets list                          # Includes SQL sets with their databases + backup type
backupengine backup start "Production SQL"      # Run a SQL set by its name
backupengine backup list 50                     # 50 most recent runs (all set types)
backupengine logs list                          # Per-run diagnostic logs

Chain Restore (Full + Differential + Log)

The SQL Restore screen lets you pick a "restore point" backed by a chain of manifests — typically a full backup, optionally a differential, and any number of transaction log backups up to the moment you want to recover to.

• •Pick a tail manifest in the Restore screen. The runner walks back through the chain to the most recent full backup.
• •RESTORE … WITH NORECOVERY is issued for every entry except the tail; the tail uses RECOVERY to bring the database online.
• •Optional STOPAT picker enables point-in-time recovery when the tail is a transaction log backup. STOPAT is ignored on full and differential tails (no-op).
• •Validation up front: every entry in the chain must share the same source instance and the same database set. A mismatch throws a StateError before the first sqlcmd call — the partial restore would have left the database stuck in NORECOVERY otherwise.

RESTORE DATABASE [MyDb] FROM DISK = N'<full.bak>'
  WITH NORECOVERY
RESTORE DATABASE [MyDb] FROM DISK = N'<diff.bak>'
  WITH NORECOVERY
RESTORE LOG      [MyDb] FROM DISK = N'<log.trn>'
  WITH RECOVERY, STOPAT = '2026-04-22 14:30:45'

WITH MOVE — Cross-Machine Restore

Restoring a database to a host whose data/log volumes don't match the source machine? Open the "Advanced: file moves" panel in the Restore screen.

• •The runner queries RESTORE FILELISTONLY against the source .bak to enumerate every logical file (data + log + filestream) and its original physical path.
• •Map each logical file to a new physical path on the target machine. Generated T-SQL emits one MOVE clause per remap.
• •Validation: target paths must match drive-letter prefix + .mdf/.ldf/.ndf extension. Parent-directory traversal (..) is rejected. Logical-name typos (a name not in FILELISTONLY) are silently dropped with a warning rather than emitted as a MOVE clause that would fail at restore time.
• •MOVE is a no-op for RESTORE LOG, so the panel is hidden when the tail is a log entry.

Scheduling SQL Backups

A typical SQL Server backup schedule combines all three backup types for the best balance of recovery granularity and storage efficiency.

• •Weekly full backup (e.g., Sunday at 01:00 AM).
• •Daily differential backup (e.g., every night at 01:00 AM except Sunday).
• •Transaction log backup every 30 minutes during business hours.
• •Adjust frequency based on your RPO (Recovery Point Objective) requirements.