Quick Tip: Attaching an External Database to Your Sitecore Docker Solution

When working with Sitecore XM/XP in a Docker setup, you might encounter scenarios where you want to use an existing database instead of the one generated from scratch by the default SQL image.

When and Why Attach an External Database?

I see two possible scenarios where it might be helpful:

• Sitecore Upgrade:
  • Part of the Sitecore Upgrade includes transforming the database to the target version, for example, from 9.3 to 10.4. The database changes a lot, and you might want to test it locally first before going live.
• Fast set up of local environment to reproduce Production bugs:
  • Sometimes we need to troubleshoot issues with Content Items occurring in Production, and one strategy to quickly set up your environment is to attach the Production database instead of using Packages.

Understanding Sitecore’s Database Initialization

Sitecore XP/XM uses a specific architecture for database initialization in Docker:

• mssql container: Runs SQL Server and stores database files in c:\data directory
• mssql-init container: A one-time initialization container that deploys DACPAC files to create fresh Sitecore databases
• Volume mapping: The mssql-data folder on your host machine maps to c:\data inside the container

When you start your Sitecore Docker environment for the first time, the mssql-init container runs and creates all necessary databases. To use external databases, you need to bypass this initialization and provide your own database files.

Steps to Attach an External Database

Step 1: Export the Database Files

First, export the MDF (data) and LDF (log) files for the databases you want to attach.

1. Open SQL Server Management Studio (SSMS)
2. Connect to your SQL Server instance
3. Take the desired database offline to safely copy the files:

-- Replace [YourDatabaseName] with your actual database name
ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- Replace [YourDatabaseName] with your actual database name
ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

4. Locate the MDF and LDF files on your SQL Server instance. The default path is typically:
   • C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ (SQL Server 2022)
   • C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ (SQL Server 2019)
5. Copy the MDF and LDF files to another location
6. (Optional) Bring the database back online if you still need it:

ALTER DATABASE [YourDatabaseName] SET ONLINE;

ALTER DATABASE [YourDatabaseName] SET ONLINE;

Step 2: Stop and Clean Your Docker Environment

Before placing your external database files, ensure your Docker environment is completely stopped and cleaned:

1. Navigate to your Sitecore Docker solution folder (where docker-compose.yml is located)
2. Stop all containers:

docker-compose down

docker-compose down

3. Delete all existing database files in the mssql-data folder:

# From your solution root
Remove-Item -Path .\mssql-data\* -Recurse -Force

# From your solution root
Remove-Item -Path .\mssql-data\* -Recurse -Force

This step is important because any existing database files will conflict with your external databases.

Step 3: Place Files in Docker’s SQL Volume

Copy your MDF and LDF files to the Docker SQL Server volume directory. For Sitecore XP/XM 10.4, this is the mssql-data folder in your solution directory.

# Example structure after copying files:
mssql-data\
├── Sitecore.Master_Primary.mdf
├── Sitecore.Master_Primary.ldf
├── Sitecore.Core_Primary.mdf
├── Sitecore.Core_Primary.ldf
├── Sitecore.Web_Primary.mdf
└── Sitecore.Web_Primary.ldf

# Example structure after copying files:
mssql-data\
├── Sitecore.Master_Primary.mdf
├── Sitecore.Master_Primary.ldf
├── Sitecore.Core_Primary.mdf
├── Sitecore.Core_Primary.ldf
├── Sitecore.Web_Primary.mdf
└── Sitecore.Web_Primary.ldf

Step 4: Rename Files to Match Sitecore’s Naming Convention

Ensure your files follow Sitecore’s standard database naming conventions:

For XP topologies, you’ll also need:

• Sitecore.Xdb.Collection.ShardMapManager_Primary.mdf/ldf
• Sitecore.Xdb.Collection.Shard0_Primary.mdf/ldf
• Sitecore.Xdb.Collection.Shard1_Primary.mdf/ldf
• Sitecore.MarketingAutomation_Primary.mdf/ldf
• Sitecore.Messaging_Primary.mdf/ldf
• Sitecore.Processing.Pools_Primary.mdf/ldf
• Sitecore.Processing.Tasks_Primary.mdf/ldf
• Sitecore.ReferenceData_Primary.mdf/ldf
• Sitecore.Reporting_Primary.mdf/ldf

Step 5: Disable Database Initialization

To prevent the mssql-init container from recreating databases, you need to modify your Docker Compose configuration.

Create or modify your docker-compose.override.yml file:

version: "2.4"

services:
  # Comment out or remove the mssql-init service entirely
  # mssql-init:
  #   image: ${SITECORE_DOCKER_REGISTRY}sitecore-xp0-mssql-init:${SITECORE_VERSION}
  #   ...
  
  mssql:
    volumes:
      - type: bind
        source: .\mssql-data
        target: c:\data

version: "2.4"

services:
  # Comment out or remove the mssql-init service entirely
  # mssql-init:
  #   image: ${SITECORE_DOCKER_REGISTRY}sitecore-xp0-mssql-init:${SITECORE_VERSION}
  #   ...
  
  mssql:
    volumes:
      - type: bind
        source: .\mssql-data
        target: c:\data

💡 By removing or commenting out the mssql-init service, you’re telling Docker not to run the database initialization container. The mssql container will automatically attach any MDF/LDF files it finds in the c:\data directory when it starts.

Step 6: Start the Docker Container

Start your Docker containers:

docker-compose up -d

docker-compose up -d

The SQL Server container (mssql) will automatically attach the MDF and LDF files found in the c:\data directory as part of its startup process. You can monitor the startup by checking the container logs:

docker-compose logs -f mssql

docker-compose logs -f mssql

Look for messages indicating that databases are being attached.

Step 7: Verify and Configure Database Access

After the containers are running, verify the databases are properly attached and configure access:

1. Connect to the SQL Server container:

# Get the SQL SA password from your .env file
# Connect using: localhost,14330 (note the comma, not colon)
# Username: sa
# Password: <your SQL_SA_PASSWORD from .env>

# Get the SQL SA password from your .env file
# Connect using: localhost,14330 (note the comma, not colon)
# Username: sa
# Password: <your SQL_SA_PASSWORD from .env>

2. Verify databases are online:

SELECT name, state_desc, user_access_desc 
FROM sys.databases 
WHERE name LIKE 'Sitecore%';

SELECT name, state_desc, user_access_desc 
FROM sys.databases 
WHERE name LIKE 'Sitecore%';

3. If databases show as “RECOVERY_PENDING”, you may need to take them offline and online again:

ALTER DATABASE [Sitecore.Master] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [Sitecore.Master] SET ONLINE;

ALTER DATABASE [Sitecore.Master] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [Sitecore.Master] SET ONLINE;

4. Verify and recreate SQL users if necessary:

-- Check if Sitecore users exist
SELECT name FROM sys.database_principals WHERE type = 'S';

-- If needed, recreate users (password from your .env file)
CREATE LOGIN [sitecoreuser] WITH PASSWORD = '<your-password>';

-- For each database:
USE [Sitecore.Master];
CREATE USER [sitecoreuser] FOR LOGIN [sitecoreuser];
ALTER ROLE [db_owner] ADD MEMBER [sitecoreuser];

-- Check if Sitecore users exist
SELECT name FROM sys.database_principals WHERE type = 'S';

-- If needed, recreate users (password from your .env file)
CREATE LOGIN [sitecoreuser] WITH PASSWORD = '<your-password>';

-- For each database:
USE [Sitecore.Master];
CREATE USER [sitecoreuser] FOR LOGIN [sitecoreuser];
ALTER ROLE [db_owner] ADD MEMBER [sitecoreuser];

5. Update connection strings if you’re using custom database names or credentials in your Sitecore configuration files.

That’s it, you should be good!

Attaching an external database to your Sitecore Docker solution is a quick and straightforward way to leverage existing data without starting from scratch. 😉