Database Upgrade Tool (SQL Server Compact Edition)

  • Article

The Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) Database Upgrade tool is a command prompt utility that upgrades a database created with Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) version 2.0 or Microsoft SQL Server 2005 Mobile Edition , to the latest version of a SQL Server Compact Edition database.

Note

The file format for SQL Server Compact Edition is different from earlier versions of SQL Server CE. Therefore, databases created with earlier versions must be upgraded to the new file format to be used with SQL Server Compact Edition.

How the Database Upgrade Tool Works

When the SQL Server Compact Edition Database Upgrade tool (upgrade.exe) is run on a smart device, a new SQL Server Compact Edition database is created. The new SQL Server Compact Edition database, with the file name extension .sdf, contains all the data that was present in the source database.

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

The SQL Server Compact Edition Database Upgrade tool does not upgrade tracking or subscription information. Only data in the source database is upgraded to a SQL Server Compact Edition database.

Important

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

Location of the Database Upgrade Tool

The database upgrade tool is installed with Microsoft Visual Studio 2005 as part of the SqlMobile30DevTools[lang].msi file. The default location of the file is *<drive>:\*Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\[platform]\[processor]\upgrade.exe.

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 1.0, SQL Server CE 1.1, or SQL Server CE 2.0
  • SQL Server Compact Edition and the SQL Server Compact Edition OLE DB Provider (this is installed by default with the installation of SQL Server Compact Edition)

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 tries to use the SQL Server CE 1.0 and SQL Server CE 1.1 OLE DB Provider (ssce10.dll).

Note

If neither of the OLE DB providers from earlier versions of SQL Server Compact Edition is found, the Database Upgrade tool returns the message "SQL Server CE 1.1 or higher must already be installed."

  • Uses the SQL Server Compact Edition storage engine to create a new SQL Server Compact Edition database.
  • Reads the schema of the source database using the OLE DB provider of the earlier version of SQL Server Compact Edition.
  • Writes the schema to the SQL Server Compact Edition destination database using the SQL Server Compact Edition OLE DB provider.
  • Reads the data in the source database using the OLE DB provider for the earlier version of SQL Server Compact Edition.
  • Writes the data to the SQL Server Compact Edition destination database using the SQL Server Compact Edition OLE DB Provider.

Note

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.

Note

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

For more information about backward compatibility and upgrading to SQL Server Compact Edition, see Upgrading SQL Server Mobile and SQL Server CE.

To run the Database Upgrade tool, see How to: Use the Database Upgrade Tool (SQL Server Compact Edition). 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)