SqlCeCmd tutorial part one – Managing database files

This tutorial will show various samples of using SqlCeCmd, which is available at http://sqlcecmd.codeplex.com – sqlcecmd is the “equivalent” of sqlcmd for the full SQL Server product.

The series will be in three parts, the first one demonstrates how to manage entire database files, the second how to create database objects and insert/update data, the third relating to querying data.

To get information about usage

Simply type “sqlcecmd” at the command prompt:

Notice that the –d parameter is always required. It is a SQL Compact connection string as documented here: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

To create a new database file with default settings

sqlcecmd –d “Data Source=C:\test.sdf” –e create

To create a new database file with password and case sensitivity

sqlcecmd
-d "Data Source=C:\test.sdf;Case Sensitive=True;Password=p"
-e create

To upgrade a version 3.0/3.1 database file to 3.5 format

sqlcecmd -d "Data Source=C:\Northwind.sdf" -e upgrade

To compact a database file

sqlcecmd -d "Data Source=C:\Northwind.sdf" -e compact
Similarly you can shrink or repair a database file, with the shrink, repairdelete and repairrecover options.

To change database options

You can change the following four database options on an  existing database: Change password, Change encryption mode, Change locale id, Change case sensitivity

Possible values for encryption mode are:
engine default or platform default or  ppc2003 compatibility

sqlcecmd
-d "Data Source=C:\test.sdf;Case Sensitive=True;Password=p"
-z "Data Source=;Encryption Mode=ppc2003 compatibility;Case Sensitive=false"

To drop a database

del c:\test.sdf

:-)

Stay tune for the next part, creating and modifying database objects.