KB363: How to migrate Moxie's database from SQLite to SQL Server

Note: Before beginning this process, ensure there is no pre-existing Moxie_Database on your SQL Server.

  1. From the computer on which your Moxie Data Server is installed, stop all services starting with "Omnivex".
  2. Launch the Moxie Data Server Connection (MDSC) wizard by running MDSC.exe, located in the Tools folder of the Moxie installation directory (default location C:\Program Files\Omnivex\Moxie).
  3. In the MDSC:
     
    1. From Data Source, select SQL Server.
    2. In Server name enter the SQL Server name.
    3. Configure the Database administration account.
    4. Click Next. The Moxie_Database is created on your SQL Server.
    5. Do NOT close or complete the MDSC at this point - leave it open on the current page.

  4. Download the ZIP file here and extract it to a folder on the Data Server computer. For the purposes of this article, this folder is referred to as the "Migration Folder".
  5. Using Windows Explorer, navigate to C:\ProgramData\Omnivex\Moxie\Drive C\Program Files\Omnivex\Moxie\Data Server\DataServer\ and copy the Backbone.db file. 
  6. Paste the copied database file into the Migration Folder
  7. Run Command Prompt as an administrator and change the directory to the Migration Folder
  8. Type sqlite3 backbone.db <script.sql and press ENTER. This generates 4 SQL Server files in the Migration Folder.
    Note: This process may take several minutes to complete.
  9. Next, you need to open and make changes to each of the four files that were created in the Migration Folder. To do this, for each file:
     
    1. Use a text editor, such as Notepad, to open the file.
    2. At the top of the file, on its own line, type the text for the corresponding file, as indicated in the table below.
    3. Save the changed file.
    4. Copy the entire contents of the file to your System clipboard (with the file open in Notepad, click CTRL+A then CTRL+C).
    5. Open the Moxie database in SQL Server Management Studio.
    6. Right-click Moxie_Database and click New Query.The Query editor opens.
    7. Paste the content from the clipboard into the Query editor.
    8. Click Execute.
    9. Make sure there are no errors in the query results.
    10. Close the Query editor

    For:
     
    File name
    Type
    tblDataItem SET IDENTITY_INSERT tblDataItem ON
    tblDataItemValue SET IDENTITY_INSERT tblDataItemValue ON
    tblGroups SET IDENTITY_INSERT tblGroups ON
    tblPermissions SET IDENTITY_INSERT tblPermissions ON

  10. Return to the MDSC and click Previous.
  11. Click Create or Update database.
  12. Complete the MSDC, configuring everything as necessary for your system.

After completing the MDSC, it may take several minutes for the changes to take effect. Once this time has elapsed, launch the Moxie Studio and confirm that the changes have taken place.