Dockerized SQL Server 2022 with AdventureWorks Database

This Docker setup creates a complete SQL Server 2022 instance with the AdventureWorks sample database pre-installed.

Features

• ✅ SQL Server 2022 Developer Edition
• ✅ SA password set using .env file (see .env.example)
• ✅ AdventureWorks database automatically restored (requires manual download - see Prerequisites)
• ✅ Hands-off installation
• ✅ Data persists within the Docker image
• ✅ Configurable, external port to avoid conflict with host SQL Server
• ✅ SSMS compatible

Quick Start

Prerequisites

• Docker (Desktop recommended).
• A need for a temporary database instance for dev, test, and experimentation without having to install and configure it.

Download AdventureWorks Database Backup:

1. Visit the Microsoft AdventureWorks Sample Databases page
2. Download the AdventureWorksLT2022.bak file (lightweight version recommended for this setup)
3. Place the downloaded .bak file in the same directory as this Dockerfile
4. Ensure the filename matches exactly: AdventureWorksLT2022.bak

Note: Scripts assume AdventureWorks DB, but you can update the scripts to use your own DB backup file.

Option 1: Using Docker Compose (Recommended)

# Copy and configure environment variables
cp .env.example .env
# Edit .env file with your desired values

# Build and start the container
docker-compose up -d

# View logs
docker-compose logs -f

# Stop the container
docker-compose down

Option 2: Using Docker directly

# Build the image
docker build -t adventureworks-sql .

# Run the container (replace with your actual port and password)
docker run -d --name adventureworks-sql -p ${SQL_EXTERNAL_PORT}:1433 \
  -e SA_PASSWORD=${SQL_SA_PASSWORD} \
  -e ACCEPT_EULA=Y \
  -e MSSQL_PID=Developer \
  adventureworks-sql

# View logs
docker logs -f adventureworks-sql

# Stop the container
docker stop adventureworks-sql
docker rm adventureworks-sql

Connecting with SSMS

1. Open SQL Server Management Studio (SSMS)
2. Connect to server (use your configured SQL_EXTERNAL_PORT from .env file):
   • Server name: localhost,${SQL_EXTERNAL_PORT} or 127.0.0.1,${SQL_EXTERNAL_PORT}
   • Authentication: SQL Server Authentication
   • Login: sa
   • Password: ${SQL_SA_PASSWORD} (from your .env file)

Database Information

• Database Name: AdventureWorks
• Data File: /var/opt/mssql/data/AdventureWorks.mdf
• Log File: /var/opt/mssql/data/AdventureWorks.ldf

Container Details

• Image: Based on mcr.microsoft.com/mssql/server:2022-latest
• Internal Port: 1433
• External Port: ${SQL_EXTERNAL_PORT} (configured in .env file)
• SA Password: ${SQL_SA_PASSWORD} (configured in .env file)

Troubleshooting

Check if container is running

docker ps

View container logs (real-time)

# Using docker-compose
docker-compose logs -f

# Using docker directly
docker logs -f adventureworks-sql

Check initialization status

Look for these key messages in the logs:

• "SQL Server is ready!" - SQL Server has started successfully
• "Database restore completed successfully!" - AdventureWorks database has been restored
• "Initialization complete. SQL Server is ready for connections." - Container is fully ready

Manual database verification

# Connect to container and run test queries
docker exec -it adventureworks-sql sqlcmd -S localhost -U SA -P '${SQL_SA_PASSWORD}' -C -i /tmp/test-database.sql

# List all databases
docker exec -it adventureworks-sql sqlcmd -S localhost -U SA -P '${SQL_SA_PASSWORD}' -C -Q "SELECT name FROM sys.databases;"

# Check specifically for AdventureWorks
docker exec -it adventureworks-sql sqlcmd -S localhost -U SA -P '${SQL_SA_PASSWORD}' -C -Q "SELECT name FROM sys.databases WHERE name = 'AdventureWorks';"

Connect to container shell

docker exec -it adventureworks-sql /bin/bash

Common Issues

Container keeps showing "SQL Server starting..."

• Wait up to 2 minutes for initial startup
• Check if you have enough memory allocated to Docker (minimum 2GB recommended)

Database restore fails

• Ensure you have downloaded the AdventureWorksLT2022.bak file (see Prerequisites section)
• Verify the .bak file is not corrupted and matches the expected filename exactly
• Check container logs for specific error messages
• Ensure the container has sufficient disk space

Cannot connect from SSMS

• Verify the container is running: docker ps
• Check port mapping: should show 0.0.0.0:${SQL_EXTERNAL_PORT}->1433/tcp
• Try connecting with: localhost,${SQL_EXTERNAL_PORT} or 127.0.0.1,${SQL_EXTERNAL_PORT}
• Ensure Windows Firewall isn't blocking your configured external port

Rebuild container from scratch

# Stop and remove existing container
docker-compose down
docker rmi adventureworks-sql 2>/dev/null || true

# Rebuild and start fresh
docker-compose up -d --build

Down and remove container

Using Docker Compose (Recommended)

# Stop and remove containers, networks, and volumes created by docker-compose
docker-compose down

# Also remove the built image to free up disk space
docker-compose down --rmi all

# Remove everything including volumes (if any were created)
docker-compose down --rmi all --volumes --remove-orphans

Using Docker directly

# Stop the container
docker stop adventureworks-sql

# Remove the container
docker rm adventureworks-sql

# Remove the image to free up disk space
docker rmi adventureworks-sql

# Optional: Clean up unused Docker resources
docker system prune -f

# Optional: Remove all unused images, containers, and networks
docker system prune -a -f

Complete cleanup (all Docker resources)

# Warning: This will remove ALL unused Docker resources system-wide
# Stop all running containers
docker stop $(docker ps -q)

# Remove all containers
docker container prune -f

# Remove all images
docker image prune -a -f

# Remove all volumes
docker volume prune -f

# Remove all networks
docker network prune -f

# Or use the nuclear option (removes everything unused)
docker system prune -a --volumes -f

Notes

• The container automatically restores the AdventureWorks database on first startup.
• Data persists within the Docker image (no external volumes needed).
• The container uses the external port configured in your .env file to avoid conflicts with any existing SQL Server installation.
• Initial startup may take 30-60 seconds for database restoration to complete.