Database failures when creating a site in Visual Studio

I’ve seen a few cases where Visual Studio is not able to create the database successfully when using the template to create a new EPiServer site. The MDF file is in the correct location, but either there are errors in the Visual Studio console window saying that the database could not be connected to, or when the site runs you get an error about missing schema or ‘Could not find stored procedure 'sp_DatabaseVersion'’. There are a number of reasons why this could happen – funny Local DB configurations, odd permissions issues, SQL Express version issues, but the one thing I’ve found is that troubleshooting can be long and tedious. For this reason, I’ve found that often the easiest thing to do is to set up the database myself following a few simple steps.

Firstly, run the aspnet_regsql wizard in your .Net 4 framework folder .Connect to your Local DB instance and choose your EPiServer database (it will be named the same as the MDF file). If you are unsure of the instance name, grab it from the connection strings file in your project. I have found that sometimes the Local DB isn’t started properly… if this is the case then you can start an instance using these instructions, and try again. Follow the wizard through and it will create all your ASP.NET support in the database (needed for user management, among other things).

Next, open SQL Server Management Studio (download the latest version for free if you need to – it supports all versions of Local DB and you only need to download the management studio package, not the database engine itself or any of the advanced services). You should see your database listed. Open and run the following queries against your database, in this order:

• %systemroot%\Microsoft.NET\Framework64\v4.0.30319\SQL\en\SqlPersistenceService_Schema.sql
• %systemroot%\Microsoft.NET\Framework64\v4.0.30319\SQL\en\SqlPersistenceService_Logic.sql
• [your site installation folder]\packages\EPiServer.CMS.Core.7.7.1\tools\EPiServer.Cms.Core.sql

The first two add .Net 3.5 Workflow support to the site – needed because the workflow subsystem in EPiServer currently runs the older workflow engine using the backwards-compatibility in .Net 4. The third script is the EPiServer schema.

Once done, try and spin your site up again and you should be good to go!