SIRIS, ALTO, and NAS: Best-practices for backing up and restoring Microsoft SQL databases

Follow

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 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.
  • Required methods of restoration may be different from a typical file restore, as Datto file restores do not preserve permissions.
  • Configure SQL backups separately from volume-based image backups, to allow for more options when restoring a database.
  • If you need to restore a database from your existing backups, you can use the virtualization capabilities of the Datto device to take SQL backups off of a running virtual machine.

Backup

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.

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

In cases where an SQL server goes down and you need a system-state backup to use Microsoft Recovery Tools:

  1. Create a virtual machine of the server.
  2. Once you have created the virtual machine, set it up as a Firewalled with a Private Subnet.
  3. To prevent live communication on the network, you may need to get the machine behind a KVM NAT.
  4. Datto Technical Support can assist you in bringing the machine online in a state that will allow the Microsoft SQL backup system to perform a SQL backup.
  5. Datto recommends directing the backup to a NAS share. ISCSI is not supported for this use.
  6. Once your restore is complete, stop and unmount the virtual 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.

Additional Resources


Was this article helpful?

4 out of 13 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 Datto Community Forum or the Datto Community Online.

For more Business Management resources, see the Datto RMM Online Help and the Autotask PSA Online Help .

Still have questions? Get live help.

Datto Homepage