Goal:

Deploy sample database to Azure SQL server from MS sample files.

Prerequisites:

Azure SQL server with Azure SQL database

CSV files with data which are for download here:  https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

Solution:

On MS documentation site there are descriptions how to do it. But for deploying to Azure SQL database you need on-premise (or already to Azure deployed) sample database from which you will be deploying (actually you will copy database).

Bak file from MS thus is not option. We have only blank cloud database and it is not possible to restore directly file there.

So the remaining option is csv dump files which can be found here:  https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms#creation-scripts

In zip archive are CSV files with data and sql file with creation scripts for database structure.

Advantage of csv is that it can be opened in excel and from excel it can be copied directly to a table in database (ctr + c and ctrl + v) via SSMS. This is in part 1.

And now what?

  1. We will create Azure SQL server and Azure SQL database (i will leave this step to you alone).
  2. We will deploy db structure to database (just run sql script).
  3. We will copy/paste files from Excel to sql tables.
  4. We can use Azure Data Factory to populate tables in a little more interesting way (in part 2).
  • Database structure
    • Run script instawdbdw.sql from cca row 125. You can skip sql statements for creating database. And around row 732 are statements for loading data from CSV files with bulk insert. They won’t work, so don’t worry.
  • Populate destination tables with data
    • Open CSV file from Excel and import csv.
  • Open destination table via “Edit top 200 rows” option from context menu.
  • Select all cells in excel and copy/paste them to destination table.
  • One table is done. Continue with the other tables in the same way.

Tags: , , ,