Restoring and Upgrading a Sitecore Database with Docker

Upgrading a database from SQL Azure PaaS and integrating it into your local Docker-based Sitecore development environment

February 2, 2024

By Dan Cruickshank

What’s Needed To Begin The Upgrade

I wrote an article on Logging Into MS SQL Server When Using Docker & Sitecore which is a prerequisite to beginning the update process. Ensure you can login to your local SQL container instance, of course.

To follow along strictly, the second prerequiste is to have a BACPAC from a SQL database back-up. If you have a database from a different source, that could be used as well. But this article I’m using a database back-up from Azure SQL.

Steps To Import, Update & Replace The Sitecore Database

I had exported a Sitecore database from a previous version of Sitecore from Azure. Follow along for the steps to import it, upgrade it and replace the existing database.

Importing A BACPAC Database

Steps To Import, Update & Replace The Sitecore Databse

Select Import Data-tier Application.

Steps To Import, Update & Replace The Sitecore Databse

Select browse and select the local bacpac file.

Steps To Import, Update & Replace The Sitecore Databse

Importantly, you want to make sure this new database is added to mounted as a volume on docker. To do this, press browser and select c:\data. The c:\data folder is inside the container and mapped to the external path of c:\code\project\docker\data\sql.

Update the database name if you need to in order to avoid conflicts.

Steps To Import, Update & Replace The Sitecore Databse

As an aside, you can see this mapping in the docker compose file. Or if you’ve installed the Docker extension for Visual Studio code, you can find the container, hover it with your mouse and will show the volumes that exist.

This lets you understand how the container interacts with your local file system.

Now lets get back to the import.

Steps To Import, Update & Replace The Sitecore Databse

This is what the final screen looks like when it’s successful.

Steps To Import, Update & Replace The Sitecore Databse

The BACPAC database we imported into our MS SQL container is now available on our local filesystem through the mounted volume.

Options To Upgrade The Database

Part of an upgrade is upgrading the databases. Sitecore provides a couple methods to do this on the Sitecore 10.3.1 downloads page. If you can find others version on downloads page.

  • Sitecore UpdateApp Tool - This did not work for me. I needed to update the connection strings file in the project to point to my docker MS SQL server but didn’t really want to. I couldn’t find any documentation on this tool and I knew the other option will work. Hopefully you’ll have more luck.
  • SQL Upgrade Scripts - Sitecore provides these for download under “Configuration files for upgrade” heading. I know these scripts work, so I’ll this is what I’ll use.

Upgrading The Database

Unzip the downloaded file and then unzip and navigate to the Database Upgrade Script folder. In this upgrade, we’re only looking at moving the master database to preserve the content so we will focus on the CMS_master.sql file.

Upgrading The Database

You can see the breadth of files below to potentially upgrade. They don’t offer XP vs XM flavors so just use what you need.

Upgrading The Database

Now right-click on the database (in our case SiteCore_Master) and select New query.

Then open CMS_master.sql in a text editor, copy and paste its contents into the blank new query window. Press the Execute button and you should see a successful update in the messages window.

Swapping The Upgraded Database

Now I want to take my upgraded database and swap it in so it is my active Sitecore master database.

  • First we take SiteCore_Master offline and detach it.
  • Then we run a small script to point the database to the .mdf and .ldf files we just created.

Swapping The Upgraded Database

For steps we right-click on SiteCore_Master , then select Tasks > Take Offline . On the next screen click to drop all active connections and click OK.

Swapping The Upgraded Database

Detach the this database. Click OK.

Swapping The Upgraded Database

To do the swap, we’ll want to check the logical names of database files inside Sitecore.Master.

Steps To Import, Update & Replace The Sitecore Databse

Right-click on the Sitecore.Master database, click properties. Select Files and see the details under the Logical Name heading in the image above. We have Sitecore.Master and Sitecore.Master_log.

Now lets write the script to do the swap.

ALTER DATABASE "Sitecore.Master"   
    MODIFY FILE ( NAME = "Sitecore.Master",   
                  FILENAME = 'C:\data\SiteCore_Master.mdf');  
GO

ALTER DATABASE "Sitecore.Master"
MODIFY FILE ( NAME = "Sitecore.Master_log",
FILENAME = 'C:\data\SiteCore_Master.ldf');
GO

Update the values appropriately to reflect the database name, logical name and file location.

Note in this case, quotes were needed because of the dot in the database name. And the file path needs to reference the location inside of the container which is c:\data

Swapping The Upgraded Database

You can see it has run successfully. A restart of the SQL Server server is required to pick-up the changes.

To see these changes take affect I will run docker-compose down or the shorthand .\down.ps1 and bring it back up to restart Sitecore.

All done.

In Closing

The steps are a little different, but overall its not too that different. BACPAC files need to be imported a specific way. Run the SQL scripts from Sitecore required for your upgrade. Then lastly swap those files into the Sitecore.Master database that your Sitecore instance is already using.

If you ever find yourself working on XM Cloud or an upgrade leveraging Docker, I hope you found this to be some help.

Thanks for reading.


Related Links

Dan Headshot

Dan Cruickshank

President | Sitecore MVP x 11

Dan is the founder of Fishtank. He's a multi-time Sitecore MVP and Coveo MVP award winner. Outside of technology, he is widely considered to be a top 3 father (routinely receiving "Father of the Year" accolades from his family) and past his prime on the basketball court.