Upgrade EPiServer database manually 5 => 6

When using the upgrade tool in the EPiServer deployment center, you might get some problems with the database connections. You will find multiple blog posts on the issue around the net and I have written about it earlier here. This post will show you the steps necessary to manually upgrade the database using the SQL scripts.

1. Finding the current database version

First we need to know the current database version, so we can locate the correct script to start with. This can be done by opening you database and execute the “sp_DatabaseVersion” stored procedure.

A CMS5 R2 SP2 base will typically return “5205”

2. Finding and executing the SQL scripts

I’ll be using SQL Server in this example, but you will find similar scripts for oracle.

On a machine with CMS6 installed, you will find the scripts at the following location on a default install (for 64 bit systems add (x86) to program files):

“C:\Program Files\EPiServer\CMS\6.0.530.0\Upgrade\Database\sql”

My install contains the following files

if someone knows the logic of these numbers, please leave a comment

I haven’t found a way to figure out which script to start with from the names of the files, but if you open them, they all contain a settings section at the top of the file with some useful information. We will use an CMS5 R2 SP2 base as an example, if you remember they have a version of “5205”. Open the text files and look in the settings part until you find  a file with the following entry

This will be your starting file. Execute it in management studio or your preferred tool. Then just proceed to execute the rest of the script files in order. Unless you have sorted your files differently they will be in the correct order in the file manager. (Sort ascending on names to be sure).

Simple right ? Well, there always have to be a catch.

3. The catch

Although it looks like this catalog contains all the upgrade scripts from 5 to 6, it is actually one missing script file located elsewhere. This is the script that creates all the new tables and objects needed by CMS6. If you execute the scripts in order, you will get errors when trying to execute the script taking you from 5 to 6.

Stop before executing this script. Instead, you want to execute the following script first

This script will create all the necessary new objects for CMS 6. You can now continue with the script above and execute all the rest one by one.

4. Data conversion

The last step we need to perform, is to run an additional tool from the Deployment Center. It is called “Migrate data for site with SQL Server database”.

This tool does not suffer from the same problems as the upgrade tool does, and only uses the connection you have specified in the connection strings. Amongst other things, this tool upgrades all your XForm data to the new CMS6 data store.

¨