Working with SQL Server to Manage Your Windows XP Embedded Database

Working with SQL Server to Manage Your Windows XP Embedded Database


Microsoft Corporation

January 2009

This article contains information on common database management tasks using the SQL Server database engine that hosts an embedded database.

Although Windows XP Embedded does not require the developer to be an expert in Microsoft SQL Server functionality, there are several common tasks that require a user to interact with the embedded database by using management tools in SQL Server, or SQL Express.

This article contains specific information on these procedures:

  • How to detach and reattach your embedded database in another location
  • How to back up and restore your embedded database
  • How to create SQL Server roles, user logins, user groups, and manipulating permissions for these groups

Moving your database to a different drive that has more space, or to a completely new development workstation, has the following requirements:

  • You must be an administrator on the development workstation where the database resides.
  • You must be an administrator of the database.
    By default, the user who installs the embedded database should be an administrator on the database.
  • You must have the SQL Server Management tools installed.
    These are included as part of SQL Server, but must be separately downloaded if you use SQL Express to host your database.

The following example uses the MantisSQLDB database to show how to move your database to another location.

  1. Start SQL Server Management Studio in SQL Server 2005 and 2008, or Enterprise Manager in SQL Server 2003.

  2. Connect to the server or development workstation that hosts your embedded database.

  3. Expand the Database node, right-click the MantisSQLDB database, click Tasks and then Detach.


    If other users are attached to the database, you see a notification about active connections. If no other users are connected you see a Ready status. If other users are connected you can click the connection message, select each active connection, right-click and select Kill Process to close the active connections and then click OK. Browse to [System Drive]\Windows Embedded Data. Copy the MantisSQF_Data.mdf and MantisSQF_Log.ldf files to the new location.

    If you do not require a transaction history, you will not have to have the .ldf files; just ignore any reminders. The database will work correctly, but there will be no transaction log.
  4. In SQL Server Management Studio, right-click the Databases node, and then click Attach.


  5. In the new window, click Add and browse to the .mdf file in its new location, select it, and then click OK. You will see the database details listed in the lower pane. Click OK to accept and close this window.

  6. Now you see MantisSQLDB in the list of databases. Start the Windows XP Embedded tools to make sure that the database is successfully attached in its new location.

In this example, you did not move the repositories. They still are located in the original location under [System Drive]\Windows Embedded Data\Repositories on the same development workstation and the database still points to that location.

However, what if you want to move your database to a new development workstation? In this instance, we recommend that you move the repository folders together with your database.

  1. Detach the database, following the steps in the previous procedure.

  2. Copy both the database files and the repository folder to a location on the new development workstation. This can be [System Drive]\Windows Embedded Data, or another location.

  3. If you are sharing the repositories for multiple users, make sure that you set up the appropriate permissions on your development workstation.

  4. On the new development workstation, attach the database pointing to the .mdf file on the new development workstation.

  5. Rebase your repository folders to associate the database and the new location of the repository folder.

    Before rebasing, the database will contain information on where the repositories were located on the old development workstation.

    To rebase your repository folders:

    1. Close all open Windows XP Embedded tools.
    2. Open the Component Database Manager.
      You should see Exclusive Mode in the title bar. This indicates there are no other active connections to the database.
    3. Click the Repositories tab.
    4. Double-click each listed repository and enter the location on your new development workstation, or click Change Location.
      You can clear the Move repository file to new location check box, because you have already moved your repositories to this new location.
    5. To make sure that any new repository folders that are created for new custom components are also imported to this new location, add this new location using Repository Roots. You may want to delete the old repository root location if it is listed.

You can also have the database reside on one development workstation and the repositories on another development workstation.

Management of this arrangement could become cumbersome, because users would require access permissions to both locations.

  1. Relocate the repository folder and its files to another development workstation available to all the users of the tools.

  2. Use the Database Manager Repository tab to locate the \\[Local]\Share for each of the repositories and change this field, to \\[Remote]\Share.

    You must have administrator rights to make this change.

  3. If you want all future repositories to be redirected to this location, make sure that you update the Repository Roots field in the Database Manager Repository tab.



We recommend that you save a copy of your database before you apply either a new service pack or any other major update to your database, so that you can easily roll back your database to the earlier version.

This can easily be done by using the same steps for detaching and reattaching databases as listed earlier in this article. You can detach your existing database, make a copy of the .mdf file in a backup location, and then reattach the database and apply the update.

At any time that you might want to use the backup version of the database, you can detach the updated one, copy the old .mdf back to the usual location under [System Drive]\Windows Embedded Data, and reattach the .mdf file.

If you do not have the SQL Express Management Tools installed, and are not using SQL Server, you can use the DBRestore command prompt utility that was included originally in the Windows XP Embedded Feature Pack 2007 ValueAdd folder on the CD. DBRestore can back up local and remote databases, as long as you have administrative credentials on the development workstation.

To use DBRestore, open a Command Prompt window and browse to the ValueAdd folder on your Windows XP Embedded CD. Type the following command to run DBRestore.vbs:

DBRESTORE <ComputerName> <BackupPath> [/CB|/CREATEBACKUP]

These are the DBRestore command parameters and values:

  • <Computer Name> (required)
    • Can be "local" (no quotation marks) or the name of a computer (local or remote).
    • The named computer must be running an embedded database, retail or evaluation copy.
  • <Backup Path> (required)
    • Can be a local or UNC path.
    • Can be the path of a folder, or the path of a specific file.
    • If a file name is specified, it must be an .mdf file (case is not important).
    • If a folder is specified, a file must exist in the folder matching the existing database file, unless you are creating a backup.
  • /CB or /CREATEBACKUP (optional)
    • Specifies that you want to create a backup of the existing database.
    • In this case, <Backup Path> must point to a valid location to create the backup file.
    • If you do not specify a file name that has .mdf extension, DBRestore will determine the file name by querying the database directly and will create a backup file that has the same name as the existing database file.

For example, to create a backup of your existing SP2 database type the following at a command prompt:

dbrestore MyServer c:\MySP2Backup.mdf /cb

To restore this database, you would execute the same line without the /cb switch.

DBRestore only backs up your database .mdf file. It does not back up your repositories. However, it does manage your repositories by automatically checking to see which repository folders correspond to the repository definitions in your database.

DBRestore will remove any repositories that do not belong after the backup database is restored. For example, if you restore your SP2 database after having installed Feature Pack 2007, the Feature Pack 2007 repositories will be removed. However, if your SP2 database includes some custom components and repositories, those will be preserved during the restore.

If you have ever had problems with trying to assign different permissions for your embedded database to different sets of users in your organization, then there are several server roles that will help you manage user permissions.

Open SQL Server Management Studio or Enterprise Manager and browse to the MantisSQLDB database> Security> Roles>Database Roles. There are three predefined roles: WES Admin, WES_Importer and WES_Reader.


These roles each have different permissions:

  • WES_Admin
    This role has permissions to do almost everything to the database. A member of this group can import objects into and delete objects from the database. They can update any group or object within the database, and also run all functions on the database. Users should be assigned to this role only when they must make major changes to the database, such as deleting things and changing repository roots.
  • Wes_Importer
    Members in this role can import objects into the database and make limited updates and execute certain functions, such as create a new repository object when you import a new component, but cannot delete anything from the database, such as packages or components. They cannot make major modifications to any objects and groups in the database, such as changing the repository roots. When a user in this role starts Component Database Manager they will see the warning "You do not have permission to delete or change repository settings…". This role is most useful for the average user who must import their custom components into the database and build configurations and runtimes.
  • WES_Reader
    This role is the most restrictive of the three. It provides members read-only permissions on the database so that they can view all the existing components and other objects in the database but cannot import anything into the database or make any changes. When a user in this role starts Component Database Manager they see the warning "You do not have permission to modify the component database. Import, delete and repository settings are disabled…"
    This role can be assigned to users who may have to build runtimes, but who use the existing components in the database.

In order to view the full range of permissions for each of these roles, right-click the role, select Properties, and then Permissions.

To use these roles, first add the user account to the Login node of the server and set the default database to be MantisSQLDB.


Add the user to the Users node of the MantisSQLDB (usually supplying both domain and user name), and then click to select the Database Role check box for the role that you want them to assign them.


Depending on the role that you assign to each user, they will see a message that outlines their permissions when they start the Windows XP Embedded tools. For example, they may see a message that informs them that they cannot import objects into the database, or that they cannot open the database in Exclusive Mode if they are not an administrator.

What you have learned

By using the information on common database management tasks in this article you can use the SQL Server to more easily manage your embedded database.

© 2015 Microsoft