Configure advanced options for SQL Server managed backup to Microsoft Azure
Applies to: SQL Server (all supported versions)
The following tutorial describes how to set advanced options for SQL Server Managed Backup to Microsoft Azure. These procedures are only necessary if you require the features they offer. Otherwise, you can enable SQL Server Managed Backup to Microsoft Azure and depend on the default behavior.
In each scenario, the backup is specified using the
database_name parameter. When
database_name is NULL or *, then the changes affect the default settings at an Instance level. Instance level settings also affect new databases created after the change.
Once you have specified these settings, you can then enable managed backup for the database or instance using the system stored procedure managed_backup.sp_backup_config_basic (Transact-SQL). For more information, see Enable SQL Server managed backup to Microsoft Azure.
You should always configure the advanced options and custom scheduling options before enabling SQL Server Managed Backup to Microsoft Azure with managed_backup.sp_backup_config_basic (Transact-SQL). Otherwise, it is possible that unwanted backup operations will occur during the window of time between enabling SQL Server Managed Backup to Microsoft Azure and configuring these settings.
The following steps describe how to specify encryption settings using the stored procedure managed_backup.sp_backup_config_advanced (Transact-SQL).
Determine the Encryption Algorithm: First determine the name of the encryption algorithm to use. Select from one of the following algorithms.
Create a Database Master Key: Choose a password for encrypting the copy of the master key that will be stored in the database.
-- Creates a database master key. -- The key is encrypted using the password "<master key password>" USE Master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>'; GO
Create a Backup Certificate or Asymmetric Key: You can use either a certificate or an asymmetric key for use with the encryption. The following example creates a backup certificate to use for the encryption.
USE Master; GO CREATE CERTIFICATE MyTestDBBackupEncryptCert WITH SUBJECT = 'MyTestDBBackupEncryptCert'; GO
Set Managed Backup Encryption: Call the managed_backup.sp_backup_config_advanced stored procedure with the corresponding values. For example, the following example configures the
MyDBdatabase for encryption using a certificate named
USE msdb; GO EXEC managed_backup.sp_backup_config_advanced @database_name = 'MyDB' ,@encryption_algorithm ='AES_128' ,@encryptor_type = 'CERTIFICATE' ,@encryptor_name = 'MyTestDBBackupEncryptCert'; GO
@database_nameis NULL in the previous example, the settings apply to the SQL Server Instance.
Configure a Custom Backup Schedule
The following steps describe how to set a custom schedule with the stored procedure managed_backup.sp_backup_config_schedule (Transact-SQL).
Determine the frequency for full backups: Determine how often to take full backups of the database. You can choose between 'Daily' and 'Weekly' full backups.
Determine the frequency for log backups: Determine how often to take a log backup. This value is in minutes or hours.
Determine the day of the week for weekly backups: If the backup is weekly, choose a day of the week for the full backup.
Determine the start time for the backup: Using 24-hour notation, choose a time for the backup to start.
Determine the length of time to allow for the backup: This specifies the amount of time that a backup has to complete.
Set the custom backup schedule: The following stored procedure defines a custom schedule for the
MyDBdatabase. Full backups are taken weekly on
17:30. Log backups are taken every
5minutes. Backups have two hours to complete.
USE msdb; GO EXEC managed_backup.sp_backup_config_schedule @database_name = 'MyDB' ,@scheduling_option = 'Custom' ,@full_backup_freq_type = 'Weekly' ,@days_of_week = 'Monday' ,@backup_begin_time = '17:30' ,@backup_duration = '02:00' ,@log_backup_freq = '00:05' GO
After configuring advanced options and custom schedules, you must enable SQL Server Managed Backup to Microsoft Azure on the target database or SQL Server instance. For more information, see Enable SQL Server Managed Backup to Microsoft Azure.