ALERT: Datto Drive Cloud service is no longer available. For more information, see our end-of-life article. If you'd like to migrate your data to Datto Workplace, see our migration guide.

How Does the Datto Backup Solution Work With Microsoft SQL Databases?

Follow

Topic

What are the best practices for backing up and restoring Microsoft SQL databases off of a Datto SIRIS device?

Environment

  • Datto SIRIS
  • Microsoft SQL Server

Description

Given that SQL (in this instance) is a Microsoft-leveraged service held within Windows, the Datto solution can back up the 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 to a volume excluded from the Datto appliance's Volume-Level Backup Control. Otherwise, the machine's incremental backups will be more significant as a result of taking backups of backups.
  • Large backups remain a possibility if a backed-up partition includes a SQL server.
  • Methods of restoration may be different from a typical file restore, as our file restores do not preserve permissions.
  • Configure SQL backups separately from volume-based image backups, to allow for the most prudent database-level restoration methods.
  • Alternatively, if you need to restore a database from your existing backups, we can leverage the virtualization capabilities of the Datto device to take SQL backups off of the running VM.

Backup

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

  • Ensure the SQL writer is correctly configured 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 backup of a database. Full database backups represent the whole database at the time the backup finished. Differential database backups contain only changes made to the database since its most recent full database backup.

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 (the differential base), and that contains only the data extents that have changed since the differential base.

A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.

full backup

A data backup that 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

A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model).

file backup

A 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.

  • You can create and assign particular backup jobs given the space limitations of the Datto device.
  • Once the agent establishes the jobs, you can direct them to a Datto NAS share.
  • You can establish a restore through a network share depending on the selected recovery model.

Recovery

If a SQL Server goes down in a scenario where a system state backup is needed to leverage Microsoft recovery tools, the next step is to create a virtual machine of the protected server.

  • Once you have created the VM, set it up as a Firewalled with a Private Subnet.
  • To prevent live communication on the network, you may need to get the machine behind a KVM NAT.
  • Datto Technical Support is happy to assist you in bringing the machine online in a state that the Microsoft SQL backup system can perform a SQL backup.
  • The same practice of directing the backup to a NAS share should be employed.
  • After the restore's completion, you can stop and dismount the VM.

SQL Clustering

SQL clustering does not affect how the agent operates. The protected machine presents volumes to our driver as one logical volume, upon which we only deal in file system blocks. The physical storage, whether it be any means of clustering, shared storage, RAID, etc., is abstract and unknown to 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 of the active one.

Additional Resources


Was this article helpful?

4 out of 12 found this helpful

You must sign in before voting on this article.

Want to talk about it? Have a feature request?

Head on over to our Community Forum or get live help.