Best-practices for backing up and restoring Microsoft SQL databases

Topic

This article describes best practices for backing up and restoring Microsoft SQL databases with a Datto backup appliance.

Environment

  • Datto SIRIS
  • Datto ALTO
  • Microsoft SQL Server

Description

Your Datto device can back up SQL databases with these considerations:

  • If you are performing local backups of the SQL database on your production machine in addition to Datto backups of the production machine itself, you should save the local backups of the database, including transactional logs, to a volume that is not being backed up by the Datto device. Otherwise, your incremental backups will be much larger as a result of taking redundant backups of backups.
  • Large backups can occur if a backed-up partition includes a SQL server.
  • Granular restores of SQL databases can be performed from a file restore with the included license for Kroll OnTrack PowerControls
  • Required methods of restoration may be different from a typical file restore, as Datto file restores do not preserve permissions.

Backup methods

Datto offers two methods for backing up MS SQL servers, using agent-based backups or leveraging SQL Server's native backup function to back up to a NAS share hosted on the Datto device.

Agent-based backups

To guarantee a healthy SQL database backup using the Datto backup solution, it must be application-aware. This means the SQL software needs an available VSS writer. The VSS writer tells the Datto device how to back up the application and its data.

A VSS writer must be present for the Volume Shadow Copy Service to quiesce (freeze) the program in order to take an application-aware backup. Without a VSS Writer present, the Datto device will attempt to take a backup while transactions are actively being written. This could result in a corrupt or unhealthy backup of the database.

Datto agents do not offer the functionality to inject pre or post-backup scripts in order to pause database activity on Windows and Windows Server operating systems. Datto does not endorse third-party tools providing VSS-like functionality.

When utilizing agent-based backups, you may want to change your backup engine to only utilize VSS. Be aware that, with this option, backups will fail if the VSS writers fail. For further information on VSS, see: VSS: Description, Compatibilities, and Troubleshooting Resources.

Backing up to a NAS share

If the database is on a separate partition that does not hold any other crucial files, you can exclude the partition from the backup, then back up the database onto a NAS share hosted by the Datto device.

IMPORTANT  Datto does not support hosting or backup of MSSQL databases to an iSCSI share. MSSQL expects a strict physical block size to be reported by the disk, which is incompatible with Datto's iSCSI shares and will cause the backup to fail. If you need to backup databases directly to a share, we recommend using a NAS share instead.

  • Ensure you've correctly configured the SQL writer to take SQL backups.
  • The configuration of these backups is beyond the scope of Datto Technical Support.

The following sections are from the Microsoft document Backup Overview (SQL Server)(external link) and describe various types of backups the SQL Server can take. All links in these sections are external:

  • Copy-only backup
    • A special-use backup that is independent of the regular sequence of SQL Server backups
  • Data backups
    • A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup)
  • Database backup
    • A full database backup that represents the whole database at the time the backup finished.
  • Differential backup
    • A data backup that uses the latest full backup of a complete or partial database, or a set of data files or filegroups that contains only the data that has changed since the differential base.
    • Only records the data that has changed in the filegroups since the previous partial backup
  • Full backup
    • Contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data
  • Log backup
    • Backup of transaction logs that includes all log records not contained in a previous log backup. (full recovery model)
  • File backup
    • Backup of one or more database files or filegroups
  • Partial backup
    • Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files
    • Create and assign particular backup jobs given the space limitations of the Datto device
    • Once the agent establishes jobs, you can direct to a Datto NAS share
    • Establish a restore through a network share depending on the selected recovery model

Recovery

Agent-based VSS backups will contain a healthy copy of the database. Datto Continuity appliances include a license for Kroll OnTrack PowerControls, a tool that can be utilized for granular restores of SQL databases, including those backed up to NAS Shares.

Local and cloud virtualization is also supported.

In some instances, another recovery method which may be utilized is to create a sandboxed local virtualization and run a database backup from SQL Server to a NAS share, which can then be restored back to the production machine.

SQL Clustering

SQL clustering does not affect how the agent operates. The protected machine presents volumes to our driver as one logical volume that only recognizes file system blocks. The physical storage, whether it be clustering, shared storage, or RAID is not recognized by the driver.

The SQL Writer handles all operations between the Volume Shadow Copy Service and the agent, including all interactions with the cluster. An application-aware backup should not impact the cluster or related processes such as log truncation.

As a best practice, back up the passive machines that are part of the cluster to prevent performance or service disruptions on the active machine.

Please note that Datto does not support backing up cluster shared volumes (CSVs).

Additional Resources