SQL Server backup considerations

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 Recommendations for full recovery model 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 Recommendations for bulk-logged recovery model for details.

SQL System databases

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

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 and jobs and broker services for database mail. Records backup and restore history.

Uses 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 sessions connected to the SQL Server instance and is used to hold intermediate or temporary data.

For example, temporary tables, cursors, 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.