This article provides insights into the backup and restore methods used for protecting your Azure SQL resources. Before backing up your SQL resources, you must configure your resources for backup and restore. For more information, see Quick reference guide.
Backup methods
We support the following backup types:
Full backup
After you configure your Azure SQL resources for backup, Druva performs a full backup of the databases on your server hosts based on the backup policy.
The Druva's Quantum Bridge executes a full scan of all the databases included in the backup. For FULL backups, the complete database is backed up everytime and then deduplication is applied. When the backup job is successful, Druva creates a recovery point in your storage.
Change Data Capture (CDC) backup
When we take a FULL backup, the backed-up data might not be transactionally consistent. This means that if any transactions happen on the database during the backup, those changes will not get captured in the backup and we might lose out on that data.
To overcome this issue, our data protection solution utilizes Microsoft’s Change Data Capture (CDC) feature to track changes made to the database without modifying the original schema. CDC records changes made to the database such as inserts, updates, and deletes, and stores this information in special CDC tables, making it easily consumable for further use. You can use this information for taking incremental backups and syncing data across systems. Using CDC, you can have transactionally consistent copies of data for backup. For more information, see What is change data capture (CDC)?
Important:
Before taking CDC backups, configure the settings mentioned here. Failure to do so might result in the database being skipped from backups or failed backups.
CDC backup scenarios
This section describes different scenarios that might occur for CDC backups, the action Druva takes, and recommended actions required if any:
First backup scenarios
Scenario | Action taken | Recommended action |
CDC is disabled on the database. | Druva enables CDC on the database. For more information, see Backup Prerequisites. | Make sure you have the db_owner role for CDC to be enabled. For more information, see Backup Prerequisites. |
CDC is enabled on the database but there is a mismatch due to the following reasons:
| Druva skips that database from the backup and the backup completes with errors. For more information, see <TS/Error doc>. | Configure the recommended settings for CDC backups and try again. For more information, see Backup Prerequisites. |
CDC is enabled manually | The database is considered for backup. However, Druva does not perform cleanup of CDC data post backup. | Cleanup the database by configuring recommended cleanup job settings. Set the retention parameter to minimum 1 day. For more information, see Backup Prerequisites. |
Subsequent backup scenarios
Scenario | Action taken |
CDC is disabled between backups. |
|
CDC is enabled on the database by Druva but does not match the required configuration. |
|
Enable CDC on database and tables
Run the following T-SQL commands to enable CDC for your database and tables. Make sure you have the db_owner role.
Enable CDC for database:
EXEC sys.sp_cdc_enable_db;
Enable CDC for all tables:
DECLARE @TableName VARCHAR(100)
DECLARE @TableSchema VARCHAR(100)
DECLARE CDC_Cursor CURSOR FOR
SELECT *
FROM (
SELECT Name,SCHEMA_NAME(schema_id) AS TableSchema
FROM sys.objects
WHERE type = 'u'
AND is_ms_shipped <> 1
AND LOWER(SCHEMA_NAME(schema_id)) not in ('cdc','sys','guest','information_schema')
) CDC
OPEN CDC_Cursor
FETCH NEXT FROM CDC_Cursor INTO @TableName,@TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(1000)
DECLARE @CDC_Status TINYINT
SET @CDC_Status=(SELECT COUNT(*)
FROM cdc.change_tables
WHERE Source_object_id = OBJECT_ID(@TableSchema+'.'+@TableName))
--IF CDC Already Enabled on Table , Print Message
IF @CDC_Status = 1
PRINT 'CDC is already enabled on ' +@TableSchema+'.'+@TableName
+ ' Table'
--IF CDC is not enabled on Table, Enable CDC and Print Message
IF @CDC_Status <> 1
BEGIN
SET @SQL='EXEC sys.sp_cdc_enable_table
@source_schema = '''+@TableSchema+''',
@source_name = ''' + @TableName
+ ''',
@role_name = null;'
EXEC sp_executesql @SQL
PRINT 'CDC enabled on ' +@TableSchema+'.'+ @TableName
+ ' Table successfully'
END
FETCH NEXT FROM CDC_Cursor INTO @TableName,@TableSchema
END
CLOSE CDC_Cursor
DEALLOCATE CDC_Cursor
Considerations
We do not support backups of sys database.
During backup, if one of the databases is not in READY state :
The backup job completes with errors
The database will be skipped and it's recovery points will not be available until the next FULL backup.
The recovery points created till the time the database goes down will be available for restore.
For Azure SQL databases, we do not support basic s0, s1, and s2 tiers.
If no table exists in the database, backup will fail.
If you have assigned Service Principal for authentication, make sure you map it in the Azure environment, else backup will fail. For more information, see How to map service principal in Azure.
If a backupset has multiple databases and one of them is down:
Full backup on the backupset completes with successful with errors status.
Next CDC also completes with successful with errors status, with error as DBs skipped or down log
Next CDC gets marked as successful
Restore methods
After you back up your databases, you can restore the complete database to a consistent state from a recovery point. Currently, we support recovery point restore only.
Recovery Points
To restore the databases, you select a recovery point.
When you trigger a restore job, you can see the available warm recovery points. A warm recovery point is a point-in-time image of the backup data. Druva directly backs up the data to the warm storage and is referred to as "warm recovery points". For more information, see Enterprise Workloads key concepts and terms.
📝Note:
The account used for SQL backups and restores must also have the CREATE DATABASE permission for successful restores from recovery points. If the account used for SQL backups and restores does not have the CREATE DATABASE permission, the restores may fail.