Skip to content

Latest commit

 

History

History
52 lines (37 loc) · 3.27 KB

restore-adventureworks-sample-db.md

File metadata and controls

52 lines (37 loc) · 3.27 KB
Raw
title description services ms.service ms.subservice author ms.author ms.reviewer ms.date ms.topic
Restore the AdventureWorks sample database into SQL Managed Instance
Restore the AdventureWorks sample database into SQL Managed Instance
azure-arc
azure-arc
azure-arc-data-sqlmi
twright-msft
twright
mikeray
07/30/2021
how-to

Restore the AdventureWorks sample database into SQL Managed Instance - Azure Arc

AdventureWorks is a sample database containing an OLTP database that is often used in tutorials, and examples. It is provided and maintained by Microsoft as part of the SQL Server samples GitHub repository.

This document describes a simple process to get the AdventureWorks sample database restored into your SQL Managed Instance - Azure Arc.

Download the AdventureWorks backup file

Download the AdventureWorks backup (.bak) file into your SQL Managed Instance container. In this example, use the kubectl exec command to remotely execute a command inside of the SQL Managed Instance container to download the .bak file into the container. Download this file from any location accessible by wget if you have other database backup files you want to pull to be inside of the SQL Managed Instance container. Once it is inside of the SQL Managed Instance container it is easy to restore using standard RESTORE DATABASE T-SQL.

Run a command like this to download the .bak file substituting the value of the pod name and namespace name before you run it.

Note

Your container will need to have internet connectivity over 443 to download the file from GitHub

kubectl exec <SQL pod name> -n <namespace name> -c arc-sqlmi -- wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak -O /var/opt/mssql/data/AdventureWorks2019.bak

Example

kubectl exec sqltest1-0 -n arc -c arc-sqlmi -- wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak -O /var/opt/mssql/data/AdventureWorks2019.bak

Restore the AdventureWorks database

Similarly, you can run a kubectl exec command to use the sqlcmd CLI tool that is included in the SQL Managed Instance container to execute the T-SQL command to RESTORE DATABASE.

Run a command like this to restore the database. Replace the value of the pod name, the password, and the namespace name before you run it.

kubectl exec <SQL pod name> -n <namespace name> -c arc-sqlmi -- /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -Q "RESTORE DATABASE AdventureWorks2019 FROM  DISK = N'/var/opt/mssql/data/AdventureWorks2019.bak' WITH MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks2019.mdf', MOVE 'AdventureWorks2017_Log' TO '/var/opt/mssql/data/AdventureWorks2019_Log.ldf'"

Example

kubectl exec sqltest1-0 -n arc -c arc-sqlmi -- /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P MyPassword! -Q "RESTORE DATABASE AdventureWorks2019 FROM DISK = N'/var/opt/mssql/data/AdventureWorks2019.bak' WITH MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks2019.mdf', MOVE 'AdventureWorks2017_Log' TO '/var/opt/mssql/data/AdventureWorks2019_Log.ldf'"