Configure advanced options for SQL Server managed backup to Microsoft Azure
Applies to:
SQL Server
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.
Warning
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.
Configure Encryption
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.
AES_128
AES_192
AES_256
TRIPLE_DES_3KEY
NO_ENCRYPTION
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
MyDB
database for encryption using a certificate namedMyTestDBBackupEncryptCert
and theAES_128
encryption algorithm.USE msdb; GO EXEC managed_backup.sp_backup_config_advanced @database_name = 'MyDB' ,@encryption_algorithm ='AES_128' ,@encryptor_type = 'CERTIFICATE' ,@encryptor_name = 'MyTestDBBackupEncryptCert'; GO
Warning
If
@database_name
is 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
MyDB
database. Full backups are taken weekly onMonday
at17:30
. Log backups are taken every5
minutes. 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
Next Steps
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.
See Also
Opinia
https://aka.ms/ContentUserFeedback.
Dostępne już wkrótce: W 2024 r. będziemy stopniowo wycofywać zgłoszenia z serwisu GitHub jako mechanizm przesyłania opinii na temat zawartości i zastępować go nowym systemem opinii. Aby uzyskać więcej informacji, sprawdź:Prześlij i wyświetl opinię dla