ResolutionNote: Performing an in-place upgrade of MSDE is the recommended upgrade procedure, rather than the side-by-side upgrade described in this article.
To upgrade from MSDE to SQL Server 2005 Express where you cannot or do not want to perform an in-place upgrade:
1. Backup MSDE and Plesk databases;
1.1. Stop Plesk Management and MSSQLSERVER services;
1.2. Copy the following folders to another location:
1.3. Start Plesk Management and MSSQLSERVER services;
2. The following steps describe how to detach user databases (Note: The detachment of user databases can be taken by means of SQL Server Management Studio):
2.1. Open a command prompt and use osql to connect to the instance you wish to upgrade. To connect to the local, default instance of MSDE using Windows Authentication, use the following command:
To connect to a named instance, use the -S switch and specify the instance name as shown below to connect to the desired named instance.
osql -E -S servername\instancename
2.2. List all of the databases on the MSDE instance by using the following commands at the osql prompt:
1> SELECT name FROM master.dbo.sysdatabases WHERE DBID > 4
This lists all of the user databases on the MSDE instance.
2.3. Detach each of the user databases on the MSDE instance by entering the following command at the osql command prompt:
1> EXEC sp_detach_db [database_name]
This takes each of the user databases offline. Replace the value of database_name with the name of the databases that you want to move from MSDE to SQL Server 2005 Express. The databases will later be attached to the new SQL Server 2005 Express instance.
2.4 Exit the osql utility by entering the following command at the osql command prompt:
3. Download and install Microsoft SQL Server 2005 Express Edition as a new SQL instance and Microsoft SQL Server Management Studio Express;
4. Copy detached user databases from MSDE folder to the default SQL 2005 folder:
For example, from: %plesk_dir%\Databases\MSDE\MSSQL\Data
to: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
5. The following steps describe how to attach user databases (Note: The attachment of user databases can be taken by means of SQL Server Management Studio):
5.1. Start sqlcmd by opening a command prompt and typing the following command and then pressing ENTER:
This connects you to the local, default instance of SQL Server 2005 Express using Windows Authentication. If you wish to connect to a named instance, use the -S switch and specify the instance name as shown in the following.
sqlcmd -E -S servername\instancename
5.2. Attach each of the user databases that were detached from the MSDE instance by entering the following command at the sqlcmd command prompt:
1> EXEC sp_attach_db [database] , 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_data_filename.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_log_filename.LDF'
Replace the values of database_data_filename and database_log_filename with the names of the database files from the user database that was detached from your previous MSDE installation. This example shows the default installation path that is used by MSDE. If your installation used a custom path, then you can substitute the correct path value. Repeat for each detached user database.
5.3. Exit the sqlcmd utility by typing exit and pressing ENTER.
6. Then it’s necessary to replace MSDE by SQL Express in Plesk database.
6.1. First change folder to %plesk_bin% by using this command in cmd
6.2. Then query registered in Plesk database servers:
dbclient --direct-sql --sql="select * from databaseservers where type='mssql'"
You will see output similar to the picture below:
6.3. After that run the following command to replace corresponding record in Plesk database:
dbclient --direct-sql --sql="update databaseservers set host='.\sqlexpress',server_version='9.00.1399.06',admin_password='new_password' where id=2"
admin_password is a password of sa account specified on SQL Express installation;
id is id of MSDE instance in databaseservers table of Plesk database.
6.4. Login to Plesk, go to Server, Plesk Components Management and click Refresh button. SQL Server version should be changed from ‘8.00.2039’ to ‘9.00.1399.06’. Open list of registered database servers in Plesk by opening Server, Database servers, there should be registered ‘.\sqlexpress’ instance, the same as on the picture below:
7. The following steps describe how to create users in SQL Express for corresponding user's databases
7.1. The query below selects user names of msde databases, their passwords and database’s names which users belong to:
dbclient --direct-sql --sql="select db_users.login, db_users.passwd,data_bases.name from db_users,data_bases where db_users.db_id=data_bases.id and data_bases.db_server_id=2"
You should get output similar to result sown on the picture:
7.2. Based on this output it’s necessary to create a sql query which maps existing user to a login with the same name, or creates the SQL Server login with the pointed password if the login doesn't not exist.
Here is an example of the SQL query:
EXEC sp_change_users_login 'Auto_Fix', 'db_user1', NULL, '123qwe'
EXEC sp_change_users_login 'Auto_Fix', 'db_user2', NULL, 'qweasd'
EXEC sp_change_users_login 'Auto_Fix', 'db_user3', NULL, 'asdzxc'
7.3 Then connect to SQL Express instance with use of SQL Server Management Studio, create a query, copy-paste the query created on the previous step and execute it.
8. Finally it’s necessary to uninstall MSDE instance from the server. By default the MSDE component is hidden in Plesk entry of Add/Remove Programs list.
First you should detach MSDE from Plesk , by opening Add/Remove Programs, choose Plesk then click Change button, expand Database Management, click on Microsoft SQL Server Desktop Engine and choose “Entire Component will be unavailable”, click on Next.
After that open regedit tool on the server and change SystemComponent value from 1 to 0 in the following registry branch:
Note: Add/Remove Programs window has to be refreshed to display MSDE entry in Add/Remove Programs list.
Now the MSDE instance is detached from Plesk and can be uninstalled.