About SQL backups

SQL differential performance enhancements were introduced in Unitrends version 8.0. Starting with Unitrends version 8.0, SQL differential backups use VSS snapshots, as opposed to pre-8.0 versions which use VDI. With VSS snapshots, general performance is greatly enhanced, and features such as deduplication and replication are improved.

If you are upgrading from a pre-8.0 version, the following requirements must be met to utilize these performance enhancements:

•

Unitrends system must be running version 8.0 or higher.

•

Windows agent on your SQL client must be version 8.0 or higher (8.0.0-4 or higher for SQL Server 2014 protection).

Note:  After upgrading from a pre-8.0 Unitrends version, the next differential is automatically promoted to a full backup. If your backup schedule only consists of full and transaction log backups, your schedule is not affected.

The following are considerations for upgrading from a pre-8.0 Unitrends version:

•

If agent push is used to update the agent, the next differential is automatically updated to a full with no additional action required.

•

If you manually update the agent, you must re-save the client. Do this by going to Settings > Clients, Networking, and Notifications > Clients. Select the upgraded client and then Save. This forces the system to recognize the update to the client, and causes the next differential to be promoted to a full.

The recovery model of your SQL databases determines what type of Unitrends backups are supported. See the table below for descriptions of the SQL recovery models that are supported by Unitrends. See the Microsoft article Recovery Models (SQL) for additional information on recovery models and how to choose the best recovery model for your environment.

Recovery Model	Backups Supported	Considerations
Simple	• Full • Differential	No SQL logs created.
Full	• Full • Differential • Transaction log	Schedule weekly transaction log backups to truncate logs. See SQL backup strategies and recommendations for details.
Bulk-Logged	• Full • Differential	Run a transaction log backup before switching from the full recovery model to the bulk-logged recovery model. See SQL backup strategies and recommendations for details.

The following table provides brief descriptions of the SQL system databases and how they can be protected with Unitrends.

Database	Description	Compatible recovery model and strategy
master	Stores all system-level information, such as logon accounts, configuration settings, and metadata.	Only uses the simple recovery model and must be protected with full backups. Before restoring this database, all other databases must be stopped.
msdb	Used to schedule alerts, jobs, and broker services for database mail. Records backup and restore history.	Uses the simple recovery model by default, but can be configured to use the full recovery model. (Recommended only if msdb history is used when restoring backups.)
model	Acts as a template for any new databases that are created. Content of the model is copied to each new database.	By default it is configured to use the full recovery model, and new databases inherit this setting. It is only backed up when settings are changed.
resource	Contains internal system objects. (Read-only)	This database cannot be backed up or restored.
tempdb	A temporary workspace used by any session connected to the SQL Server instance and is used to hold intermediate or temporary data. For example, temporary tables, cursors, and data for sorting.	Every time SQL Server starts, this database is re-created. There is no reason to preserve this database by backing up or restoring.
distribution	Stores metadata and history data in support of SQL Server replication.	Present only if replication is configured.

This section provides example strategies and recommendations for protecting your SQL databases with Unitrends software.

Database	Backup Strategy
System databases	Weekly full backups
User databases using the full recovery model	Weekly full, daily differential, and hourly transaction logs When using the SQL full recovery model, transaction log backups must be performed to truncate log files. If not truncated, log files continue to grow until the space on your disk is full, resulting in system failure. To prevent runaway transaction log files, make sure that you create a schedule with frequent transaction log backups.
User databases using the simple recovery model	Bi-weekly full backups with daily differentials
User databases using the bulk-logged recovery model	The SQL bulk-logged recovery model is used as a temporary recovery model to enhance performance when running bulk jobs. Unitrends does not support log backups while a database is in the bulk-logged recovery model because they are unnecessarily large. For compliance with Unitrends best practices, perform the following steps: 1 Run a log backup while the database is still in full recovery model. 2 Switch to the bulk-logged model. 3 Perform the bulk operation. (For example, importing new labels, copying data from one table to another, or creating an index.) 4 Switch back to the full recovery model.

Once you register the SQL Server to the backup system, the SQL icon displays in the Navigation pane beneath the client name with which it is associated. If you do not see the SQL icon, click the reload arrows at the bottom to refresh the view. For details on registering the SQL Server, see About adding clients.

If you have added SQL to a Windows server after the server has been registered to the backup system, the agent software must rescan to detect and display the newly added SQL application. To rescan, highlight the SQL Server in the Navigation pane and select Settings > Clients, Networking, and Notifications > Clients. On the Clients page, click Save.

During file-level backups of a Windows client hosting a SQL server, certain SQL data is excluded from backup:

•

The following extensions are excluded from SQL user databases if the SQL VSS component is running on the Windows client, .mdf, .ldf, and .ndf.

•

Files in SQL database/log directories are excluded.

To protect SQL databases, use the procedures described in Executing SQL backups.