Installing AdventureWorks Sample Database

This section describes how to install AdventureWorks as a sample database on SQL Server.

To practice JDBC interface with SQL Server, you need some tables and data on the database server. Microsoft has a nice sample database called AdventureWorks available free for you to use. This tutorial shows you how to download and install AdventureWorks on your local SQL Server.

1. Go to the "AdventureWorks installation and configuration" Website at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure.

2. Click "AdventureWorks2019.bak" in the OLTP section start download and save it to the C:\temp folder.

3. Open command line window as an Adminstrator, and move the downloaded file, AdventureWorks2019.bak, to "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup".

4. Run SQLCMD command line tool to restore AdventureWorks2019 database to the local SQL Server:

herong> sqlcmd -S localhost\SQLEXPRESS -E

1> RESTORE DATABASE AdventureWorks2019
2> FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS
\MSSQL\Backup\AdventureWorks2019.bak'
3> WITH 
4>   MOVE 'AdventureWorks2017' 
5>     TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS
\MSSQL\DATA\AdventureWorks2019.mdf',
6>   MOVE 'AdventureWorks2017_log' 
7>     TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS
\MSSQL\DATA\AdventureWorks2019.ldf',
8> REPLACE
9> GO

Processed 26344 pages for database 'AdventureWorks2019', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks2019', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 26346 pages in 3.215 seconds (64.019 MB/sec).

When the restore process is completed, two physical database files are installed in the data directory: "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA".

AdventureWorks2019.mdf  276,824,064 Bytes
AdventureWorks2019.ldf   75,497,472 Bytes

The sample database AdventureWorks2019 is ready for you to play now.

By the way, don't follow the instructions given on the Microsoft Website. You will get errors:

4> USE [master]
5> RESTORE DATABASE [AdventureWorks2019]
6> FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER
\MSSQL\Backup\AdventureWorks2019.bak'
7> WITH  FILE = 1,  NOUNLOAD,  STATS = 5
8> go

Changed database context to 'master'.
Msg 5133, Level 16, State 1, Server localhost\SQLEXPRESS, Line 5 
Directory lookup for the file "C:\Program Files\Microsoft SQL Server
\MSSQL15.SQL2019\MSSQL\DATA\AdventureWorks2019.mdf" failed with the 
operating system error 3(The system cannot find the path specified).

Msg 3156, Level 16, State 3, Server localhost\SQLEXPRESS, Line 5
File 'AdventureWorks2017' cannot be restored to 'C:\Program Files
\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\AdventureWorks2019.mdf'. 
Use WITH MOVE to identify a valid location for the file.

...

As a reference, here is what I did to install AdventureWorks 2014 database on my Windows computer.

1. Go to the "AdventureWorks installation and configuration" Website at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure.

2. Download the "Adventure Works 2014 Full Database Backup.zip" file in C:\temp folder.

3. Download the "How to install Adventure Works 2014 Sample Databases.pdf" file. Read the "Install Adventure Works 2014 OLTP database from a backup" section.

4. Unzip "C:\temp\Adventure Works 2014 Full Database Backup.zip" and copy the extracted file AdventureWorks2014.bak to "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup"

5. Run SQLCMD command line tool to restore AdventureWorks2014 database to the local SQL Server:

herong> sqlcmd -S localhost\SQLEXPRESS -U sa -P HerongY@ng

1> RESTORE DATABASE AdventureWorks2014
2> FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS
   \MSSQL\Backup\AdventureWorks2014.bak'
3> WITH MOVE 'AdventureWorks2014_data' TO 'C:\Program Files\Microsoft
   SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf',
4> MOVE 'AdventureWorks2014_Log' TO 'C:\Program Files\Microsoft SQL
   Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.ldf',
5> REPLACE
6> GO

Processed 24248 pages for database 'AdventureWorks2014', file
'AdventureWorks2014_Data' on file 1.
Processed 4 pages for database 'AdventureWorks2014', file
'AdventureWorks2014_Log' on file 1.
RESTORE DATABASE successfully processed 24252 pages
in 4.911 seconds (38.579 MB/sec).

When the restore process is completed, two physical database files are installed in the data directory: "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA".

AdventureWorks2014.mdf  210,176 KB
AdventureWorks2014.ldf    2,048 KB

The sample database AdventureWorks2014 is ready for you to play now.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

Microsoft SQL Server Express Edition

 What Is Microsoft SQL Server Express Edition

 Installing Microsoft SQL Server Express Edition

 Installing Microsoft SQL Server 2014 Express Edition

 SQLCMD SQL Server Command Line Tool

Installing AdventureWorks Sample Database

 Create Login User in SQL Server

 Microsoft JDBC Driver for SQL Server

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Archived Tutorials

 References

 Full Version in PDF/EPUB