Share via

How to "restore" AdventureWorks .bak into Azure SQL Database

Amy Elmer 0 Reputation points
2024-07-15T17:16:06.2433333+00:00

I'm trying to get the AdventureWorks dataset into a new Azure SQL Server Database, using the instructions here : https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

When I connect to the Azure SQL Server either via Azure Data Studio, or MS SQL Server Management Studio, I do not have the "Restore" button, as indicated in the instructions in the link above.
I've also tried using T-SQL (in both environments), and get an error that the RESTORE statement is not supported in this version of SQL Server.

I've found in the Azure portal, when I'm on the SQL Server Resource, there's an option to Import Database, from a file saved in Azure Blob container, so I moved the .bak file there, and I've gone through the wizard and tried importing the .bak file that way, to no avail. The wizard asks for Microsoft Entra Admin Username and Password, so I enter my own, but then after a long time of deploying/importing the database, I get an error:

Message: Failed to authenticate the user XXXXXXXXX@XXXXX.XXX in Active Directory (Authentication=ActiveDirectoryPassword).
Error code 0xinvalid_grant
AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access XXXXXXX...

but I can't find an option to set it up with MFA, only username and password.

I've since found documentation to suggest that you cannot import a .bak file into Azure SQL database, and it must be a .bacpak

  1. Why does the tutorial in the link above imply that you can
  2. How does one convert a .bak file to a .bacpak file? Why does Microsoft not supply the .bacpak file?

Does anyone have any suggestions how to proceed?

Thanks.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,323 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 33,421 Reputation points MVP
    2024-07-15T18:17:12.9333333+00:00

    You cannot directly restore a native SQL Server backup file into an Azure SQL Database. I can provide you 2 alternatives:

    1. Create a new Azure SQL Database and specify you want it to e sample database (a light version of AdventureWorks database). Read steps provided here.
    2. You can restore AdventureWorks on your local computer and then perform an assessment using DMA Tool of how compatible AdventureWorks is with Azure SQL and after resolving the incompatibilities you can use the same DMA tool to migrate the AdventureWorks database. AdventureWorks database is not fully compatible with Azure SQL requirements.
    0 comments
  2. ZoeHui-MSFT 35,381 Reputation points
    2024-07-16T08:14:39.0433333+00:00

    Hi @Amy Elmer,

    You may restore the bak file on the local server first and then extra datatier application.

    After this upload this .bacpac file to your storage account. Then use this storage account and file to import database. using Import feature in Azure SQL database.

    User's image

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments