What are the best practices for backing up and restoring Microsoft SQL databases off of a Datto SIRIS device?
- Datto SIRIS
- Microsoft SQL Server
Given that SQL (in this instance) is a Microsoft-leveraged service held within Windows, the Datto solution can backup 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, 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.
- Large backups remain a possibility if an SQL server is included on a backed-up partition
- 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, should the database need to be restored from our existing backups, we can leverage the virtualization capabilities of the Datto device to take SQL backups off of the running VM.
If the database is on an isolated partition that does not hold any other crucial files, the partition can be excluded from a 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:
- 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.
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.