EPiServer have been making some great improvements to the setup of new sites lately. There’s now no need to wrestle with the Deployment Center. Using the latest EPiServer CMS Visual Studio Extension creating a shiny new CMS 7.6.3 project is a simple matter of choosing between and MVC and Web Forms and pressing a button. This will create a preconfigured site in Visual Studio, with the only remaining tasks being to create a site in IIS and attach the supplied database.

Supplied database?

That’s right, within the App_Data folder is an .mdf file comprising the site’s database. Naturally it isn’t included as part of the solution:

The template installs (at the time of writing) the version 7.6.3 Nuget packages, but there aren’t any database changes between versions 7.5.394.2 and 7.6.3.0 that I’m aware of. Feel free to correct me if I’m wrong.

However there’s a catch. There isn’t always a catch, but sometimes it can certainly seem that way. In this case it’s that the supplied database was created in SQL Server 2012, and 2012 databases cannot be ported back to earlier versions. This was a problem for me as our infrastructure runs mostly on SQL Server 2008R2. I suspect this is the case for a lot of people.

This was frustrating as it was standing in the way of having a nice easy setup process for new projects. The only thing to do was to roll up my sleeves and figure out where that sample database came from. This involved some tedious digging through the Visual Studio Extension’s.vsix file (it’s just an archive) the details of which I’ll spare you in favour of the highlights

Hooray there’s a script

I was concerned when I started looking that I’d find the .mdf file plainly archived in the .vsix. That would have been a disappointing dead end. However there was no sign of it, so it was either being supplied by one of the included Nuget packages (which would have also been a dead end) or created from a SQL script. It was of course the latter, inside EPiServer.CMS.Core.7.6.3.nupkg, in the tools folder: EPiServer.Cms.Core.sql.

Hopefully, I thought, I would just be able to run this script on a new database and we’ll be off.

Boo there’s a catch

The catch this time is that the script doesn’t contain everything you need. The database it created resulted in the error Missing stored procedure “RetrieveNonblockingInstanceStateIds” when I tried to use it with the new project. This is related to Windows Workflow Foundation, which requires some objects creating in the database. As these aren’t part of EPiServer itself, they weren’t included in EPiServer.Cms.Core.sql. However fortunately they are available to us in %WINDIR%\Microsoft.NET\Framework\v4.0.30319\SQL\en. Simply the following two scripts (in order) against the EPiServer database:

• SqlPersistenceService_Schema.sql
• SqlPersistenceService_Logic.sql

So we’re done, right?

Yes, I do believe that’s all that’s required to get a fresh database created. Here’s a zip containing all three required scripts. Just remember to run SqlPersistenceService_Schema.sql before SqlPersistenceService_Logic.sql and all should be well.

If however all is not well, please let me know as (there’s that catch again!) it hasn’t been thoroughly tested yet, but it seems sound in principle.