Backing Up SQL Databases

Follow

1. Scope

This article discusses best practices for backing up and restoring SQL databases off of a Datto SIRIS device.

2. Background

Given that SQL (in this instance) is a Microsoft-leveraged service held within Windows, ShadowSnap can backup the databases with these considerations:

  • If you are performing local backups of your SQL database on your production machine in addition to Datto backups of the production machine itself, the local backups of the database should be saved to a volume excluded from the Datto appliance's Volume-Level Backup Control. Otherwise, the machine's incremental backups will be significantly larger as a result of taking backups of backups.
  • Methods of restoration may be different from a typical file restore as our file restores do not preserve permissions
  • SQL backups should be configured separately from volume-based image backups, to allow for the most prudent database-level restoration methods.
  • Alternatively, should the database need to be restored from our existing backups, we can leverage the virtualization capabilities of the datto to allow for SQL backups to be taken off of the running VM.

3. Backup

If the database is on an isolated partition that does not hold any other crucial files, the partition can be excluded from a ShadowSnap backup and the database can be backed up to a NAS share on the Datto device.

  • Ensure the SQL writer is correctly configured to take SQL backups: 
  • Microsoft's overview of SQL backups can be found here: http://msdn.microsoft.com/en-us/library/ms175477.aspx
  • Configuration of these backups is beyond the scope of Datto Technical Support.
  • Types of Backups as referenced from the SQL Backup Overview page:
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 is based on 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 jobs have been created, they can then be directed to the Datto NAS share.
  • Restores can be carried out through a network share depending on the selected recovery model.

4. Recovery

Should an SQL Server go 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 the VM is created, 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 a SQL backup can be performed within the Microsoft SQL backup system.
  • The same practice of directing the backup to a NAS share should be employed.
  • After the restore has been completed, the VM can be stopped and dismounted.

Was this article helpful?

4 out of 11 found this helpful

You must sign in before voting on this article.

Want to talk about it? Head on over to our Community Forum!