Automatic SQL Database Backups in the Cloud

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didn’t have proper backups in place.

Wouldn’t it be great if there was a way that DBAs and engineers didn’t have to worry about backups and SQL Server was smart enough to perform the backup based on workload pattern (data changes) or based on some defined recovery interval and the backups are auto-magically stored offsite without having to worry about putting them on tapes and then shipping them.

The SQL Server product team has done an awesome job in delivering this new feature that takes away the headache of managing and maintaining database backups. SQL Server Managed Backup automatically performs backups of your databases and stores them to Windows Azure Blob Storage Service.

SQL Server Managed Backups can be enabled at the database or Instance level. With the database level backup, you can only perform managed backup per database. However, if you enable instance level managed backups then you can perform managed backups for all user databases and for any new future databases. (Note: System databases are not currently supported with Managed backups) SQL Server Managed backups can be used for both on premise and hosted environments (ex: VM).

The good thing about SQL Server Managed Backups is that you do not have to specify the type of backup or frequency of the backup. You only specify the retention period which will take care of determining both the type and frequency of the database backup. You can also use the new backup encryption feature introduced in SQL Server 2014 along with SQL Server Managed Backups.

What is a Retention Period?

The retention period is used by SQL Server Managed Backup to determine what backup files should be retained (or left) in the storage so that you can perform a point-in-time recovery of the database. (Currently supported values for retention are 1 thru 30 days)

Important: Only FULL and LOG backups are currently supported by SQL Server Managed Backups.

Full database backup happens automatically if the following conditions are met:

1. SQL Server Managed Backup is enabled at the Database or Instance level.
2. T-log has grown >= 1 GB in size since the last full database backup.
3. The log chain is broken
4. T-Log backup file is deleted (by human / system error)
5. Backup files are overwritten with different backups

T-Log Backup happens automatically if the following conditions are met:

1. Database has had >=5 MB of T-Log change
2. It has been >= 2 hours since the last Log backup was taken
3. Anytime the T-Log backup is lagging behind a full database backup
4. T-Log backup history is deleted or is not found

Important things to keep in mind:

1. System databases cannot be backed up using SQL Server Managed Backup
2. T-SQL and SSMS are both supported for performing SQL Server Managed Backup
3. Only Windows Azure Blob Storage Service is currently supported as backup storage option
4. Backup size of the database cannot exceed 1 TB in size currently. (you can use backup compression to reduce the backup size)
5. Databases must be in either FULL or BULK-LOGGED Recovery model. SQL Server Managed Backup doesnt support databases with Simple Recovery model.
6. Enabling SQL Server Managed Backup at Instance level enables managed backups for new databases but doesnt enable that for existing databases. You must configure each databases specifically.
7. A Windows Azure storage account and a SQL Credential that stores the authentication information to the storage account should both be created before configuring SQL Server Managed Backup to Windows Azure.

Lets now look at an example on how to perform SQL Server Managed Backup:

Enable Instance Level Managed Backup

-- To Enable instance level managed backup
EXEC smart_admin.sp_set_instance_backup
   @retention_days=30
   ,@credential_name='dbClinictoURL'
   ,@encryption_algorithm ='AES_128'
   ,@encryptor_type= 'Certificate'
   ,@encryptor_name='dbClinicBackupCert'
   ,@enable_backup=1
Go

-- To Disable instance level managed backup
EXEC smart_admin.sp_set_instance_backup
   @enable_backup=0;

Let’s now look at enabling database level managed backup

-- To Enable Database Level Managed Backup
EXEC smart_admin.sp_set_db_backup
   @database_name='dbClinic'
   ,@enable_backup=1
   ,@retention_days =30
   ,@credential_name ='dbClinictoURL'
   ,@encryption_algorithm ='AES_256'
   ,@encryptor_type= 'Certificate'
   ,@encryptor_name='dbClinicBackupCert';

-- To Disable Database Level Managed Backup
EXEC smart_admin.sp_set_db_backup
   @database_name='dbClinic'
   ,@enable_backup=0;

Alternatively, you can also use SSMS for performing SQL Server Managed Backups as shown in the below screen.