The SQL Server Compact 3.5 Database Upgrade tool is a command prompt utility that upgrades SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE 2.0) databases.

The file format for SQL Server Compact 3.5 differs from earlier versions. Therefore, databases created by using earlier versions must be upgraded to the new file format. Opening a SQL Server CE database file by using SQL Server Compact 3.5 will fail and a System.Data.SqlServerCe.SqlCeException will be raised.

How the Database Upgrade Tool Works

When the SQL Server Compact Database Upgrade tool (Upgrade.exe) is run on a smart device, a new SQL Server Compact database is created. The new SQL Server Compact database, with the file name extension .sdf, contains all the data in the source database. To use Upgrade.exe, both the SQL Server Compact and SQL Server CE runtimes must be installed on the mobile device.

The source database from the earlier version of SQL Server Compact also remains on the device. Before running the Database Upgrade tool, you must make sure that the device has sufficient memory in both databases. Additionally, the Database Upgrade tool requires approximately 300 KB of memory on the device.

The Database Upgrade tool does not upgrade tracking or subscription information. Only data in the source database is upgraded.

If you are using replication or remote data access (RDA) for connectivity, you must synchronize the source SQL Server Compact database together with the SQL Server database first, before upgrading the database. Upgrading a connectivity database would only be used to preserve any local tables that might exist in the database. This is because, after the upgrade, you will have to re-subscribe or re-pull from the new database.

Location of the Database Upgrade Tool

The database upgrade tool is installed with Visual Studio 2008 as part of the SQL Server Compact 3.5 for Devices (SSCEDeviceRuntime-[lang].msi ) MSI file. The default location of the file is %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Devices\[platform]\[processor]\upgrade.exe.

The SQL Server Compact 3.5 SP2 for Devices MSI does not install with Visual Studio 2010. SQL Server Compact 3.5 SP2 for Devices is only available at the Microsoft Download Center.

Running the Database Upgrade Tool

To run the Database Upgrade tool, you must have the following software installed on the smart device:

  • SQL Server CE (v. 2.0) and SQL Server Compact 3.5/3.5 SP1/3.5 SP2

  • SQL Server Compact OLE DB provider (By default, this is installed when you install SQL Server Compact.)

When deployed on the smart device where the source database is located, the Database Upgrade tool performs the following tasks:

  • Opens the source database file (with the extension .sdf) that was specified at the command prompt.

  • Opens the specified database. If the SQL Server CE 2.0 OLE DB provider (ssce20.dll) is not found on the device, the Database Upgrade tool returns the message "SQL Server CE 1.1 or a later version must already be installed."

  • Uses the SQL Server Compact storage engine to create a new SQL Server Compact database.

  • Reads the schema of the source database using the OLE DB provider of the earlier version of SQL Server Compact.

  • Writes the schema to the SQL Server Compact destination database using the SQL Server Compact OLE DB provider.

  • Reads the data in the source database using the OLE DB provider for the earlier version of SQL Server Compact.

  • Writes the data to the SQL Server Compact destination database using the SQL Server Compact OLE DB provider.

    The Database Upgrade tool cannot distinguish between an index created by the user and an index created by adding a constraint such as a primary key. The tool will not upgrade indexes that share the same name as the constraint, as the tool assumes that these were created by the constraint itself.

Any errors are written to a log file (upgrade.log), which are saved to the same directory on the device where the Database Upgrade tool (Upgrade.exe) is located.

Upgrade.log is deleted and re-created before each upgrade.

For more information about backward compatibility and upgrading to SQL Server Compact, see Upgrading from Earlier Versions (SQL Server Compact).

To run the Database Upgrade tool, see How to: Upgrade SQL Server Compact 2.0 By Using the Database Upgrade Tool. The following table outlines the command-line parameters that are used with the Database Upgrade tool.

Parameter

Description

/s

The path of the source database file. (required)

/sp

The password for the source database file. (optional)

/d

The path of the destination database file. If you specify an existing file, the file will be overwritten. (required)

/dp

The password for the destination database file. (optional)

/e

If specified, the destination database will be encrypted. If you specify the /e parameter, you must also specify /sp and provide a password. (optional)

/q

Perform the upgrade silently without any UI. (optional)

/?

Displays this list of parameters. (optional)