Had to migrate a site from a remote server onto a Windows virtual server (Virtuozzo) running Plesk. The site had a database running on SQL Server 2005, but, by default Windows Plesk virtual servers only run MSDE. The database also had stored procedures, which, MSDE doesn’t support. So, needed to install SQL Express 2005.

I was provided with a .bak file of the SQL 2005 database, so, I installed a temporary SQL 2005 server and imported/restored the database. For some reason I was not able to restore the database directly to a SQL Express 2005 server, hence, installing the full version as a go between.

On the Virtuozzo hardware node I installed a SQL Express 2005 application template (saved the .efd file to Vz\Templates\__cache and then ran vzpkgdeploy -i <pkgname>) supplied by SwSoft and then installed it into the VE via VZMC.

The new install of SQL Express didn’t:

• Have sa access, although, was in authentication mixed mode (required by plesk). I had to enable the sa user through management studio and change password. Then, could add database server to plesk.
• Support remote logins (over TCP) was note enabled. Had to open SQL Server Configuration Manager, drop down SQL Server 2005 Network Configuration and select Protocols for SQLEXPRESS. Enabled TCP/IP temporarily.

In Plesk I added the new database server… to have two MSSQL database servers I also needed to upgrade Plesk from 7.5.6 to some version of 8 (went to 8.2). The Plesk 8.2 application template didn’t install properly on the virtual server and need SwSoft technical support to fix it.

[ Days later ] … Now Plesk was fixed, database server was added, I could create the database under the clients site in Plesk.

On my temporary SQL 2005 server I ran SQL Server Management Studio and connected to both the full SQL server and the SQL Express server on the virtual server. The full version of SQL 2005 still had the database I previously restored onto it, so, I was able to click on the new SQL Express database and then select Import.

I used the data source “SQL Native Client” and specified the server name of the full SQL server and selected the database. Next, I was able to select the destination details which of course was SQL Express 2005 running on the virtual server.

Then selected “Copy data from one or more tables or views”. Selected all tables and then executed. Management studio told me that “The execution was successful”.

Edited the web applications Web.config to point the local database. I previewed the site with the Plesk site preview option: http://192.168.0.1/$sitepreview/site.co.uk/app/

Didn’t work as I hadn’t copied over the stored procedures and the site returned: Could not find stored procedure ‘dbo.usp_tblSetting_SelectAll’.

Going back to the Management Studio I selected the database on the full SQL server and selected Generate Scripts. Pretty much left defaults and then selected ‘Stored Procedures’ and selected ‘Script to new Query Window’. I selected the database on the SQL Express server and then copied the query window into a new query window (no doubt an easier/quicker way to do this) and executed it. The site started working.