Microsoft SQL Server

This Protected Item type backs up a Microsoft SQL Server database. The underlying technology is VDI and is compatible with SQL Server 2005 and later, including SQL Server 2019 (the latest version at the time of writing).

No data is spooled to the local disk. As per the "Program Output" type, no progress bar or ETA appears during a Microsoft SQL Server backup.

Databases are backed up one-at-a-time. If you require point-in-time consistency across multiple databases, please use the "Application-Aware Writer" option instead.

Connection Details

Connection details should be supplied before selecting databases. Magnus Box will only connect to SQL Server running on the local machine. You must enter the instance name, or leave the field blank to use the default instance.

Address 

The address is always localhost, but Magnus Box does not use TCP addresses or TCP ports to connect to SQL Server instances. Magnus Box uses "Shared Memory" to connect to SQL Server instances.

Magnus Box's use of "Shared Memory" connection does improves performance for some operations, at the expense of only working on the local machine; but Magnus box's use of VDI requires it to run against the local machine anyway.

If you encounter issues connecting to your SQL Server, you must ensure that "Shared Memory protocol" is enabled in SQL Server Configuration Manager.

Driver 

 OLE DB and ODBC are data access methods that use pluggable "drivers" / "providers" for connecting to databases like SQL Server. The following drivers for OLE DB / ODBC support SQL Server:

Driver: MSOLEDBSQL
TLS 1.2 Support: Yes
Notes: Included with SQL Server 2016 and 2017; Optional download from https://www.microsoft.com/en-us/download/details.aspx?id=56730

Driver: SQLNCLI11
TLS 1.2 Support: Yes
Notes: Included with SQL Server 2012 and 2014; Optional download from https://www.microsoft.com/en-us/download/details.aspx?id=50402

Driver: SQLNCLI10
TLS 1.2 Support: No
Notes: Included with SQL Server 2008 

Driver: SQLNCLI
TLS 1.2 Support: No
Notes: Included with SQL Server 2005 

Driver: SQLOLEDB
TLS 1.2 Support: No
Notes: Included with SQL Server (all versions); Included with Windows since XP / Server 2003

Magnus Box has been upgraded over time to support trying additional drivers:

Magnus Box Version: >= 18.9.6, >= 18.8.6
Preferred Driver: MSOLEDBSQL
Fallback Driver(s): SQLNCLI11, SQLOLEDB 

Magnus Box Version: 18.9.5
Preferred Driver: MSOLEDBSQL
Fallback Driver(s): SQLOLEDB

Magnus Box Version: 18.9.4, 18.8.5 and older
Preferred Driver: SQLOLEDB
Fallback Driver(s): None

You can list your installed drivers

Authentication 

Magnus Box allows you to connect to SQL Server using either Windows authentication (running as the backup service account - usually NT SERVICE\backup.delegate or SYSTEM), or native SQL Server authentication.

  • If you are using Windows Authentication, the connection occurs as the backup service account.

    You can assign this Windows user account to have sysadmin rights within SQL Server.
  • If you are using SQL Server authentication, you must enter a valid username and password to connect to SQL Server.
Impersonation is not currently available for Windows authentication. Future versions of Magnus Box will support impersonation for Windows authentication.

Multiple instances 

Magnus Box supports backing up multiple instances from SQL Server. You can select an instance for backup, by entering the instance name in the "Instance Name" field. Leave this field blank to use the default instance.

Magnus Box automatically lists available instances for selection in the drop-down menu.

A future version of Magnus Box will make the instance dropdown list available for remote administration in the Magnus Box Server.

Backup Mode

By default, Magnus Box opts to make a full database export from SQL Server, and then uses its own deduplication system to optimize the stored/uploaded data.

This is the "Full (copy only)" option. It is equivalent to the BACKUP WITH COPY_ONLY T-SQL statement.

Because Magnus Box can efficiently deduplicate full image backups, it is normally sufficient to only take full backups of SQL Server in Magnus Box.

Base images 

You have the option to use SQL Server's own differential/log backup system. This may be more efficient, but it does require additional administrative work, and complicates the process of restoring data.

The SQL Server maintains one single point-in-time reference, from which it can produce differential backups and/or log-based backups. When you take a new "Full (base image)" backup, the point-in-time reference is moved forward, so that any future differential and/or log-based backups are based on the last base-image backup.

To use SQL Server's own differential/log backup system, you must create multiple Protected Items (each with a different schedule) in order to capture both a base image and a differential/log backups. By creating multiple Protected Items, you can individually schedule, report-on, and manage retention policies for both base and differential/log backups.

If you are using Magnus Box alongside another product for SQL server backups, you should ensure that only one product is taking base-image backups. Otherwise, it's possible that a chain of differential/log backups would be incomplete.

Differential 

Magnus Box can use SQL Server's own systems for differential backup. In this mode, you can regularly make "differential base" backups, and then a series of small "differential increment" backups, each containing the difference from the last base backup. These operations are equivalent to the BACKUP and BACKUP WITH DIFFERENTIAL T-SQL statements respectively. Magnus Box will still deduplicate multiple base backups that are sent to the same Storage Vault.

This is the "Differential increment" option.

Log 

You can opt to use SQL Server's own systems for log backup. In this mode, you must periodically take full (base image) backups, and regularly take log backups.

You have the choice of whether to apply log truncation. These operations are equivalent to the BACKUP LOG and BACKUP LOG WITH NO_TRUNCATE T-SQL statements respectively. Magnus Box will still deduplicate all data that is sent to the same Storage Vault.

To use SQL Server's own log system, you must create multiple Protected Items (each with a different schedule) in order to capture both full and log backups.

This mode requires that the database Recovery Model is set to "Full" or "Bulk Logged" in SQL Server. For more information, please see https://msdn.microsoft.com/en-us/library/ms189275.aspx .

Recommendations 

In general, we would recommend using the default "Full" backup technique.

SQL Server's native differential/log systems may be used if you experience performance issues with the default mode, however, you must ensure that

  1. No other backup systems are resetting the last base backup;
  2. Whenever the differential/log backs up successfully, that the base has also recently backed up successfully;
  3. Base backups are performed regularly to minimize differential overhead; and
  4. Retention is carefully managed to ensure that recovery is possible

Alternative ways to back up

You can use the "Application-Aware Writer" type to back up SQL Server using the VSS Writer. Compared to Magnus Box's standard VDI approach, this option enables more detailed progress information, and can take a consistent point-in-time snapshot of multiple databases at once; but offers more limited control over SQL Server features such as log truncation. The resulting files also must be restored in a different way.

You can use Magnus Box's "Commands" feature to call osql/sqlcmd to run a T-SQL BACKUP statement against the database, and then back up the resulting spooled file with the "Files and Folders" type. This option requires more temporary disk space than the built-in system above.

You can use the "Files and Folders" type to back up individual database files if the "Take filesystem snapshot" option is selected. However, the "Files and Folders" backup type does not invoke SQL Server's VSS writer, so this would (at best) produce a "crash-consistent" backup and is not recommended.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us