Sitecore Docker: How to Restore Databases Using Backups in a SQL Container

I have been experimenting with running Sitecore on Docker containers for a couple of months now and I am having a lot of fun with it. If you work with many clients at the same time like I do, running Sitecore on Docker brings the big benefit to setup and spin up a client environment in few minutes, simplifying the local environment setup process and eliminating the conflicts that might rise hosting multiple versions of Sitecore instances on a single host machine. If you haven’t tried it yet, I highly recommend to do it.

In this blog post I will describe how to override the default command executed in a Docker container running a sitecore-<topology>-sql image to restore Sitecore databases using SQL database backups generated from a non-Docker SQL server.

The need to use a database backup

During project development, developers usually share Sitecore items with their team members using serialization and synchronization tools, like Unicorn or TDS. This approach is used not only for templates and layout items, but also for content and media library items. Serialized items are included in the source code repository of their project.

While this approach works pretty well during project development where the content items are not too many, it becomes quickly unmanageable when maintaining a live production website and having the need to setup a local development environment with content similar to the one in production. This is an important requirement when trying to reproduce an issue on a non-production environment. Having the same content helps to reduce differences between environments and improves the ability to reproduce reported issues.

A common solution to have the same content on a non-production environment is to use database backups to restore Sitecore databases, mainly the Core, Master and Web databases. In the next section I will describe how to automatically restore these Sitecore databases using database backups when working with Docker containers.

The Sitecore SQL container executing behavior

The Dockerfile used to build a sitecore-<topology>-sql image ends with a CMD command. The CMD command specifies the command that will be executed as soon as the image runs in a Docker container, making it an executing container.

For the sitecore-<topology>-sql image, the CMD command executes the Boot.ps1 powershell script stored in the image:

CMD C:/Boot.ps1 -InstallPath $env:INSTALL_PATH -DataPath $env:DATA_PATH -SqlHostname $env:SQL_HOSTNAME;

Let’s review in details what the Boot.ps1 script does when executed.

First, the script checks if there are any existing Sitecore .mdf database data files in a data volume (DataPath parameter). If there are no existing database files, clean Sitecore databases files (both logs and data) are copied in the data volume. These clean database files are stored in a local folder (InstallPath parameter) in the image and added to the image when the image was built.

Then the script attaches any database files found in the data volume in the local SQL server instance.

Finally, the script invokes few sql commands. It sets the sa user as db_owner for each database that contains the word “Sitecore” in its name. It renames the server name in the Xdb collection shard databases.

Overriding the default behavior with an entrypoint

An easy way to override a CMD or an ENTRYPOINT command defined in the Dockerfile of an image is defining an entrypoint setting in the service definition in the docker compose file.

Note: Setting entrypoint both overrides any default entrypoint set on the service’s image with the ENTRYPOINT Dockerfile instruction, and clears out any default command on the image – meaning that if there’s a CMD instruction in the Dockerfile, it is ignored.

From ENTRYPOINT section on Docker compose file reference documentation.

I used the Boot.ps1 script as starting point for my custom entrypoint script and I extended it adding my custom code.

The custom entrypoint

The additional custom code executes the following steps:

1. If no database data files (.mdf) are found in the data volume, after seeding clean Sitecore databases, it checks for the existence of database backups files (.bak) in an additional backups volume.
   
2. If database backups files are found, for each found file the script:
   • identifies the type of database to restore (the implemented version supports only Sitecore Core, Master or Web databases, but it is easy to extend it to support more databases),
   • gets the logical names of the database to restore,
   • restores the database with replace and recovery options and creates the database files in the data volume (same location where other existing database files are stored),
   • and finally detaches the restored database (since the default Boot script takes care of attaching all databases in a later step).

The script contains 4 additional input parameters to specify the path of the volume where to search for backup database files (BackupsPath) and the filters used to detect the database type in a database backup filename (CoreDbNameFilter, MasterDbNameFilter and WebDbNameFilter).

The code

This is the complete entrypoint script, where the highlighted lines represent the additional custom code added to the default Boot.ps1 script:

[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)]
    [ValidateScript( { Test-Path $_ -PathType 'Container' })]
    [string]$InstallPath,
    [Parameter(Mandatory = $true)]
    [ValidateScript( { Test-Path $_ -PathType 'Container' })]
    [string]$DataPath,
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string]$SqlHostname,
    [Parameter(Mandatory = $true)]
    [ValidateScript( { Test-Path $_ -PathType 'Container' })]
    [string]$BackupsPath,
    [Parameter(Mandatory = $false)]
    [string]$CoreDbNameFilter = "_Core",
    [Parameter(Mandatory = $false)]
    [string]$MasterDbNameFilter = "_Master",
    [Parameter(Mandatory = $false)]
    [string]$WebDbNameFilter = "_Web"
)

$noDatabases = $null -eq (Get-ChildItem -Path $DataPath -Filter "*.mdf")

# Enable Contained Database Authentication
$SqlCommand = "sp_configure 'contained database authentication', 1; RECONFIGURE;"
Write-Host "### Enabling contained database authentication..."
Invoke-Sqlcmd -Query $SqlCommand

if ($noDatabases) {
    
    Write-Host "### Sitecore databases not found in '$DataPath', seeding clean databases..."

    Get-ChildItem -Path $InstallPath | ForEach-Object {
        Copy-Item -Path $_.FullName -Destination $DataPath
    }

    # Check existence of database backups
    $noBackups = $null -eq (Get-ChildItem -Path $BackupsPath -Filter "*.bak")

    if (-NOT $noBackups) {
        Write-Host "### Sitecore database backups found in '$BackupsPath', restoring databases..."

        Get-ChildItem -Path $BackupsPath -Filter "*.bak" | ForEach-Object {
            $databaseFileName = ""
            if ($_.BaseName.Contains($MasterDbNameFilter)) {
                $databaseFileName = "Sitecore.Master_Primary"
            }
            elseif ($_.BaseName.Contains($WebDbNameFilter)) {
                $databaseFileName = "Sitecore.Web_Primary"
            }
            elseif ($_.BaseName.Contains($CoreDbNameFilter)) {
                $databaseFileName = "Sitecore.Core_Primary"
            }
            
            if ($databaseFileName -ne '') {
                $databaseName = $databaseFileName.Replace("_Primary", "")
                # Get database logical names
                $logicalNames = Invoke-Sqlcmd -Query "RESTORE FILELISTONLY FROM DISK = N'$($_.FullName)' WITH NOUNLOAD" | Select-Object -ExpandProperty LogicalName

                # Restore database
                $SqlCommand = "RESTORE DATABASE [$($databaseName)] FROM DISK = N'$($_.FullName)' WITH REPLACE, RECOVERY, MOVE N'$($logicalNames[0])' TO '$($DataPath)$($databaseFileName).mdf', MOVE N'$($logicalNames[1])' TO '$($DataPath)$($databaseFileName).ldf';"
                Write-Host "### Restoring $databaseName database from backup..."
                Invoke-Sqlcmd -Query $SqlCommand

                # Detach database
                Invoke-Sqlcmd -Query "EXEC MASTER.dbo.sp_detach_db @dbname = N'$databaseName', @keepfulltextindexfile = N'false'"
            }
        }
    }
}
else {
    Write-Host "### Existing Sitecore databases found in '$DataPath'..."
}

Get-ChildItem -Path $DataPath -Filter "*.mdf" | ForEach-Object {
    $databaseName = $_.BaseName.Replace("_Primary", "")
    $mdfPath = $_.FullName
    $ldfPath = $mdfPath.Replace(".mdf", ".ldf")
    $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '$databaseName') BEGIN EXEC sp_detach_db [$databaseName] END;CREATE DATABASE [$databaseName] ON (FILENAME = N'$mdfPath'), (FILENAME = N'$ldfPath') FOR ATTACH;"

    Write-Host "### Attaching '$databaseName'..."

    Invoke-Sqlcmd -Query $sqlcmd
}

Write-Host "### Preparing Sitecore databases..."

# See http://jonnekats.nl/2017/sql-connection-issue-xconnect/ for details...
Invoke-Sqlcmd -Query ("EXEC sp_MSforeachdb 'IF charindex(''Sitecore'', ''?'' ) = 1 BEGIN EXEC [?]..sp_changedbowner ''sa'' END'")
Invoke-Sqlcmd -Query ("UPDATE [Sitecore.Xdb.Collection.ShardMapManager].[__ShardManagement].[ShardsGlobal] SET ServerName = '{0}'" -f $SqlHostname)
Invoke-Sqlcmd -Query ("UPDATE [Sitecore.Xdb.Collection.Shard0].[__ShardManagement].[ShardsLocal] SET ServerName = '{0}'" -f $env:DB_PREFIX, $SqlHostname)
Invoke-Sqlcmd -Query ("UPDATE [Sitecore.Xdb.Collection.Shard1].[__ShardManagement].[ShardsLocal] SET ServerName = '{0}'" -f $env:DB_PREFIX, $SqlHostname)

Write-Host "### Sitecore databases ready!"

& C:\Start.ps1 -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

The docker-compose sql service definition

The entrypoint script can be added in the sql service definition in the docker-compose file, using the entrypoint setting, as shown below:

sql:
    image: ${REGISTRY}sitecore-xp-sqldev:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    entrypoint: powershell.exe -NoLogo -NoProfile -Command "& C:\\tools\\entrypoints\\sql\\Boot.ps1 -InstallPath 'C:\\install\\' -DataPath 'C:\\data\\' -SqlHostname 'sql' -BackupsPath 'C:\\Backups'"
    volumes:
      - ${PROJECT_DEPLOY_BASE_PATH}\data\sql:C:\Data
      - ${PROJECT_DEPLOY_BASE_PATH}\backups:C:\Backups
      - ${PROJECT_SOURCE_FOLDER_PATH}\tools\docker\entrypoints\sql:C:\tools\entrypoints\sql
    mem_limit: 2GB
    ports:
      - "44010:1433"

Two additional volumes are also defined for the folder containing the database backups files and the folder containing the custom entrypoint script.

When the Docker sql service runs, if its data volume doesn’t contain any database and any database backups files are found in the backup volume, then new Sitecore databases are seeded and then restored using the backups.

Conclusions

In this blog post I described how to override the default command executed in a Docker container running a sitecore-<topology>-sql image to restore Sitecore databases using SQL database backups. If you have any questions, or want to share your different approach to this solution, please don’t hesitate to comment on this post.

Thank you for reading!