Migrating a SQL Server database to Azure SQL Server is a critical task for many organizations as they move towards cloud-based services. In this blog post, we’ll provide a step-by-step guide on how to import a .bak (backup) file into Azure SQL Server.

Note: Azure SQL Database does not support traditional SQL Server backups using .bak files. Instead, we’ll export the SQL Server database to a .bacpac file and then import that .bacpac file into Azure SQL Server.

Export SQL Server Database to .bacpac File

To export the on-premises SQL Server database to a .bacpac file, follow these steps:

Using SQL Server Management Studio (SSMS):

1. Open SSMS and connect to your SQL Server instance.
2. In the Object Explorer, right-click the database you want to export.
3. Select Tasks > Export Data-tier Application.
4. In the Export Data-tier Application wizard, select Save to local disk and choose a folder path to save the .bacpac file.
5. Click Next and review your settings. Click Finish to start the export process.
6. Wait for the process to complete, and then you’ll find the .bacpac file in your chosen directory.

Using SQLPackage.exe (command-line utility):

1. Install SQLPackage.exe if it’s not already installed. It’s included with SQL Server Management Studio (SSMS) and the SQL Server Data Tools (SSDT).
2. Run the following command, modifying the SourceConnectionString and TargetFile arguments as needed:SqlPackage.exe /Action:Export /SourceConnectionString:"Server=YourServer;Database=YourDatabase;User ID=YourUsername;Password=YourPassword;" /TargetFile:"C:\path\to\your\YourDatabase.bacpac"
3. Wait for the process to complete, and then you’ll find the .bacpac file in the specified directory.

Upload .bacpac File to Azure Blob Storage

To upload the .bacpac file to Azure Blob Storage, follow these steps:

1. If you don’t have one already, create an Azure storage account.
2. Create a container in your storage account to store the .bacpac file. Follow the instructions here.
3. Upload the .bacpac file to the container in your storage account.

Import .bacpac File to Azure SQL Server

To import the .bacpac file into Azure SQL Server, follow these steps:

1. Navigate to the Azure Portal and sign in.
2. Go to your Azure SQL Server (not the database) and click Import database in the toolbar.
3. In the Import Database blade, select your Subscription, Resource group, and provide a New database name.
4. In the Source section, click the folder icon to open the Storage Explorer. Navigate to and select the .bacpac file you uploaded earlier.
5. In the New server admin login section, provide the Server admin login and Password.
6. Click on Advanced Settings to configure optional settings like database size, edition, and/or SQL collation.
7. Select Next to review your settings and click OK to start the import process.
8. Keep an eye on the Notifications section for progress updates. The new database will appear in your Azure SQL Server once the import is complete.

Troubleshooting Common Issues

If you encounter issues during the export or import process, consider these common problems and their solutions:

1. Error with SQLPackage.exe: Ensure you have the latest version of SQLPackage.exe installed or use the appropriate version compatible with the source and target SQL Server instances.
2. Connectivity issues during import: Verify the correct settings for the storage account, container, and .bacpac file, as well as the server admin login details.

By following these steps, you’ll be able to import your .bak file into Azure SQL Server and have a fully migrated database in the cloud.