Azure SQL to Azure SQL Migration using Full & Differential Backup Feasible?

Question

I have to migrate Azure SQL Database completely from one environment to another Azure SQL Database environment. There is no direct network connection between the two environments.

Can Azure SQL Database Server backups (Full & Differential Backup) helpful in migrating in above scenario? Is it feasible to perform an online full backup restore from the source to the target and then apply the differential backup from the source to the target? If this approach is possible, what are the specific steps involved? If it is not feasible, what are the reasons, and what is the purpose of having differential backups if they cannot be utilized for restoration?

Please provide any relevant Azure SQL Database documentation for reference.

Answer 1

Hi @ Rajeskumar S LThanks for the question and using MS Q&A platform.

As per my understanding you need to migrate Azure SQL Database completely from one environment to another Azure SQL Database environment and there is no direct network connection between the two environments.

Can Azure SQL Database Server backups (Full & Differential Backup) helpful in migrating in above scenario?

Azure SQL Database server backups (Full & Differential backups) are not suitable for direct migration. Here are the reasons:

• Azure SQL Database backups are managed internally by Azure and are not accessible for direct download or restoration as with traditional SQL Server.
• Full backups in Azure SQL are automatically handled by Azure and cannot be manually triggered, downloaded, or restored to another environment. The platform manages full, differential, and transaction log backups automatically for point-in-time restore within the retention period, which can be up to 35 days.
• Differential backups are also not available for manual management. The concept of differential backups does not apply to Azure SQL Database as it does in SQL Server. Azure's internal backup mechanism automatically handles changes to your database through transaction log backups, which is a different process.

Is it feasible to perform an online full backup restore from the source to the target and then apply the differential backup from the source to the target? If this approach is possible, what are the specific steps involved? If it is not feasible, what are the reasons, and what is the purpose of having differential backups if they cannot be utilized for restoration?

Unfortunately, it is not feasible to perform an online full backup restore followed by applying differential backups for migration between Azure SQL Database environments. Here are the reasons:

• Unlike on-premises SQL Server, where you can explicitly perform full backups, create differential backups, and restore them to another server, Azure SQL Database does not support the manual application of differential backups or the restoration of full backups to different environments. Consequently, you cannot restore a full backup to a new environment and then apply a differential backup to update the new environment with changes from the source.

what is the purpose of having differential backups if they cannot be utilized for restoration?  

Although differential backups cannot be directly used for manual restoration, they play a critical role in the Azure SQL Database environment:

 Transactional Consistency: Azure SQL Database ensures transactional consistency through log backups and point-in-time restore, maintaining your database with minimal data loss.

Automated Management: The platform takes care of backup retention and restoration, eliminating the need for users to manually manage differential backups. Users can depend on point-in-time restore for recovery within the platform’s retention period.

Internal Backup Process: While manual download or restoration of differential backups is not possible, Azure employs these internal backups to optimize storage of transaction logs and incremental changes, ensuring efficient recovery mechanisms.

As the full backup restore and differential backup application method is not feasible in Azure SQL, here are some alternative methods that are supported for migration:

1.Azure Database Migration Service (DMS):

DMS offers an online migration path, allowing you to migrate your database with minimal downtime.

Steps:

• Configure Azure Database Migration Service in both the source and target environments.
• Execute an online migration to sync data from the source to the target, ensuring minimal downtime.
• After migration, switch over to the target environment.

Please refer to this documentation: Azure Database Migration Service (DMS) Overview

2.. BACPAC Export/Import:

You can export your source Azure SQL Database to a BACPAC file, which is a snapshot containing both the schema and data and import it into the target environment.

Steps:

• Export the database from the source environment to a BACPAC file.
• Upload the BACPAC file to Azure Storage (if needed) and import it into the target environment.

Please refer to this documentation: Export and Import Database to BACPAC

Hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.