How to upgrade from SQL Express to SQL Express Advanced

  • Article

Update -- Update -- Update (6/1/2006)

As of 6/1/2006 we have posted a corrected Express Advanced installer that does not require you to follow these steps. If you're unsure if you have the update, download a fresh copy from the SQL Express download page at https://msdn.microsoft.com/vstudio/express/sql/download.

Important Note: If you have already succesfully installed SQL Express Advanced, you do not need to reinstall it.

Original Post

Note: These steps are no longer necesary for Express Advanced downloaded after 6/1/2006.

In order to upgrade from SQL Server 2005 Express Edition (RTM or SP1) to SQL Server 2005 Express Edition with Advanced Services, you have to do the following steps first:

 

Delete the files from the Template Data directory prior to installing Express Advanced

 

You will be able to upgrade to Express Advanced if you remove the files from the Template Data directory prior to upgrading to Express Advanced:

 

1. Open Windows Explorer and navigate to C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\Template Data.

 

      MSSQL.# represents the Instance ID for SQL Express. If there are no other copies of SQL Server ( any Edition) on your computer, then this will be MSSQL.1, if there are multiple copies of SQL Server installed review the information below to determine which Instance ID you need to use.

 

2. Select all the files in the Template Data directory.

3. On the File menu, point at Send To and then click Compressed (zipped) folder. Move the Compressed folder to a safe location.

4. Once the Compressed folder has been created, delete the original files from the Template Data directory.

5. Install Express Advanced as you normally would install an upgrade.

 

Note: Once Express Advanced installation has completed successfully you can delete the compressed folder you created in step #3.

 

How to determine the Instance ID

 

1. Open SQL Server Configuration Manager. (Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools)

2. Select the SQL Server 2005 Services node.

3. Select the SQL Server instance you are interested in. (For a default installation this will read “SQL Server (SQLEXPRESS)”.

4. On the Action menu, click properties.

5. Switch to the Advanced tab and find the Instance ID property.

 

This will give you the Instance ID to use in step #1.

 

Alternatively you can remove the copy of Express Edition prior to installing Express Advanced.

 

Uninstall Express Edition (RTM or SP1) before Installing Express Advanced

 

1. Navigate to Add / Remove Programs.

2. Select Microsoft SQL Server 2005 from the and click Remove.

3. From the list of available instances, pick the SQL Express installation you want to remove. (Note: A default installation of SQL Express would be listed as SQLEXPRESS: Database Engine.)

4. Click Next and then Click Finish.

5. Once removal is complete, you can install Express Advanced to the same Instance Name.

 

Note: You will not lose your databases during removal of SQL Express, they will be left behind in the Data directory under the InstanceID for the Instance of SQL Express you just removed. (Don't delete your database!) You will need to Attach the databases to the new installation of SQL Express Advanced once it's up and running. You can do this using either SQLCmd or Management Studio Express.

 

If you tried to do the upgrade without doing this and got the error "Login failed for user 'sa'" then read this blog entrie to correct the problem.

 

Got questions? Post them in the SQL Express Forum on MSDN.

 

- Mike