Microsoft SQL Server

This Protected Item type backs up one or more Microsoft SQL Server databases using VDI (Virtual Device Interface) technology. It is compatible with SQL Server 2005 and later, including SQL Server 2022. No data is spooled to local disk during the backup, and databases are backed up one at a time rather than simultaneously. If you need a consistent point-in-time snapshot across multiple databases at once, use the Application-Aware Writer Protected Item type instead (see below).

Connection details

Magnus Box connects only to a SQL Server instance on the local machine. The address is always localhost using the Shared Memory protocol, not TCP. Make sure the Shared Memory protocol is enabled in SQL Server Configuration Manager, or the backup job cannot connect.

In the Instance name field, enter the name of a specific instance, or leave it blank to connect to the default instance.

Driver support

Magnus Box connects to SQL Server through an OLE DB or ODBC driver. The available drivers differ in their support for TLS 1.2 and in which SQL Server versions ship them:

MSOLEDBSQL - TLS 1.2: Yes. Ships with SQL Server 2016 and 2017, and is available for other versions as an optional download from Microsoft.
SQLNCLI11 - TLS 1.2: Yes. Ships with SQL Server 2012 and 2014, and is available as an optional download from Microsoft.
SQLNCLI10 - TLS 1.2: No. Ships with SQL Server 2008.
SQLNCLI - TLS 1.2: No. Ships with SQL Server 2005.
SQLOLEDB - TLS 1.2: No. Available on all versions.

Recent versions of Magnus Box prefer the MSOLEDBSQL driver when it is available, falling back to SQLNCLI11 and then SQLOLEDB. Because MSOLEDBSQL supports TLS 1.2, installing it is recommended for encrypted connections.

[SCREENSHOT: New Protected Item dialog, Microsoft SQL Server type selected, showing Instance name and driver options]

Authentication

You can authenticate to SQL Server in one of two ways:

Windows authentication - the backup job connects using the Windows service account it runs as.
SQL Server authentication - the backup job connects using a SQL Server username and password that you supply.

Whichever method you choose, the account must have sufficient permissions to perform backups of the target databases.

Backup modes

Each Protected Item uses a single backup mode. To combine full plus differential or log backups, create a separate Protected Item for each mode.

Full (copy only) - the default mode. It takes a full backup and relies on Magnus Box deduplication for storage efficiency. This is equivalent to the BACKUP WITH COPY_ONLY T-SQL statement, so it does not disturb SQL Server's own differential base or log backup chain.
Differential increment - uses SQL Server's native full and differential system, equivalent to the BACKUP and BACKUP WITH DIFFERENTIAL T-SQL statements respectively.
Log - backs up the transaction log. The database must use the Full or Bulk Logged recovery model. You can choose whether to truncate the log after backup, equivalent to the BACKUP LOG and BACKUP LOG WITH NO_TRUNCATE T-SQL statements respectively.

Restore

When restoring a SQL Server database, you can choose how the database is left afterward:

Restore with recovery - the database is brought online and made ready to use immediately.
Restore without recovery - the database is left in a restoring state so you can apply additional differential or log restores on top of it before bringing it online.

You can restore a database back to its original instance, or restore the underlying backup files to a folder for manual handling.

[SCREENSHOT: Restore dialog for a Microsoft SQL Server Protected Item showing the recovery options]

Alternative approaches

Besides the dedicated SQL Server Protected Item type, there are three other ways to protect SQL Server data:

Application-Aware Writer - uses the SQL Server VSS Writer. It enables more detailed progress information and can take a consistent point-in-time snapshot of multiple databases at once.
Commands - call osql or sqlcmd to run a T-SQL BACKUP statement that writes .bak files, then back up those files with a Files and Folders Protected Item.
Files and Folders with a filesystem snapshot - this captures the live database files but produces only a crash-consistent backup, so it is not recommended for SQL Server.

Advanced: VDI timeout

The SQL VDI operation timeout can be adjusted with the SQL_VDI_TIMEOUT environment variable. The default is 15 minutes. Increase it if backups of very large databases time out before SQL Server responds.