You can use SQL Server Managed Backup to Windows Azure and have SQL Server manage and automate SQL Server backups to Azure blob storage. With this option, you specify the blob storage location and the retention period and optionally customize backup at the database level. The maximum backup size is 12.8 TB. Managed backup supports point-in-time restore during the retention period. By default, managed backups are enabled at the instance level, with the same retention period for all databases (including newly added databases). You can change the retention period on an individual database, create a custom backup schedule, or disable managed backup for an individual database using Transact-SQL. You also have the option to encrypt the database backups for additional security.
Managed backup was introduced with SQL Server 2014, and the functionality increased substantially with SQL Server 2016. For the purposes of this ebook, I will focus on SQL Server 2016.
With SQL Server 2016, if you do not specify a custom schedule, the type of backups scheduled and the backup frequency is determined based on the workload of the database.
A full database for a database is taken in the following instances:
- When managed backup is enabled for the first time
- Log growth since the last full backup is 1 GB or more
- One week has passed since the last full backup
- The log chain is broken, such as through a manual backup
- No log backup history can be found. This usually is true when SQL Server Managed Backup to Windows Azure is enabled for the first time.
- The transaction log space used is 5 MB or larger.
- The maximum time interval of two hours since the last log backup is reached.
- Anytime the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.