I am migrating a local application to the cloud and have a standard .bak backup file from my on-premise SQL Server. However, when I try to use the "Restore Database" option in Azure SQL, it doesn't seem to recognize the .bak format. Is there a specific compatibility issue here, and what are the recommended steps or tools needed to successfully move my data into a managed Azure SQL instance?
3 answers
The most important thing to understand is that Azure SQL Database (the PaaS version) does not support the standard T-SQL RESTORE command from a .bak file because it doesn't have access to the underlying file system. Instead, you need to use a logical backup called a BACPAC file. You can export your local database as a BACPAC using SQL Server Management Studio (SSMS) by selecting "Export Data-tier Application." Once exported, you upload it to an Azure Blob Storage account and use the "Import" function in the Azure Portal. For larger databases, I highly recommend using the Azure Data Migration Assistant (DMA), which performs a compatibility check to ensure your local schema is fully supported by the cloud engine before the transfer begins.
Does your database utilize any cross-database queries or specific Windows Authentication logins that might fail once the .bak data is migrated to the restricted environment of an Azure SQL single database?
If the database is small, the easiest way is to use the "Deploy Database to Microsoft Azure SQL Database" wizard in SSMS. It handles the conversion and upload in one go.
I agree with Amanda; the SSMS wizard is a lifesaver for smaller dev environments. It’s essentially a GUI wrapper for the BACPAC process and makes the transition feel very seamless for someone new to Azure!
David’s point is critical for Cloud Technology architects. Azure SQL Database has a "contained" database model, meaning it doesn't support features like SQL Agent jobs or cross-database joins that a standard .bak might contain. If your application relies on those server-level features, you should consider migrating to an Azure SQL Managed Instance instead. Managed Instances do support direct restores from .bak files stored in URL-based Azure Storage, providing a much higher degree of compatibility with legacy on-premise configurations while still offering the benefits of a managed cloud service.