Moving your database from SQL Compact to SQL LocalDB

This article explains how to migrate a Lansweeper database hosted in the Microsoft SQL Compact database server to Microsoft SQL LocalDB. Existing data, reports and settings remain intact. From March 2020 onward, SQL Compact is no longer a supported database server for hosting Lansweeper. We therefore recommend migrating SQL Compact databases to SQL LocalDB or SQL Server. This knowledge base article explains the differences between the database server options.

migrate SQL Compact database to SQL LocalDB

Automated migration from SQL Compact to SQL LocalDB

The easiest and quickest way to migrate a SQL Compact database to SQL LocalDB is to update your Lansweeper installation to the latest version. If your Lansweeper installation is hosted in the SQL Compact database server and you are updating to the latest Lansweeper version, your database is converted to LocalDB. This process is automatic and requires no user input, though the installer will indicate that this conversion is about to happen.

automated migration from SQL Compact to SQL LocalDB

Manual migration from SQL Compact to SQL LocalDB

As stated previously in this article, the easiest and quickest way to migrate a SQL Compact database to SQL LocalDB is to update your Lansweeper installation to the latest version. Manually migrating your SQL Compact database to SQL LocalDB is ordinarily not necessary. If you do require a manual migration for whatever reason, you can follow the migration steps below.

Note that the manual migration process can only work if your SQL Compact database is already on Lansweeper version 7.2 or higher. If your SQL Compact database is on a lower Lansweeper version, follow the automated migration process instead. The automated migration path is the preferred path anyway.

Step 1: stop the services

Stop the Lansweeper Server service in Windows Services. Stop your web server service in Windows Services. Keep in mind that this will log everyone out of the console. Your web server service is either IIS Express or World Wide Web Publishing Service (IIS).

stopping the Lansweeper Server service
stopping the web server service

Step 2: back up your SQL Compact database and other Lansweeper files

Your SQL Compact database stores all of your data, reports and settings. Create a copy of your SQL Compact database file and move it to a safe location outside of the Program Files folder. Your SQL Compact database file is: Program Files (x86)\Lansweeper\SQLData\lansweeperdb.sdf

creating a backup of a SQL Compact database

If you added any documents, images, widgets or other files to Lansweeper, back these up as well. Information on which folders store which files can be found in this knowledge base article. If you are using Lansweeper 6.0 or a more recent Lansweeper release, make sure to back up the following file: Program Files (x86)\Lansweeper\Key\Encryption.txt

Do not back up the entire Website folder. Only back up the specific subfolders you need. Backing up and restoring the entire Website folder can lead to issues.

Step 3: perform an Advanced Install under SQL LocalDB

Replace your existing SQL Compact installation with a SQL LocalDB one by performing a new, Advanced Install with the same Lansweeper installer that you used to perform your SQL Compact installation. Make sure you select SQL LocalDB as your database server.

Advanced Install under SQL LocalDB

Reinstall all Lansweeper components: database, service and web console.

Step 4: migrate the SQL Compact data to LocalDB

Once again, stop the Lansweeper and web server services in Windows Services. Afterward, run the following executable, found on the machine hosting your Lansweeper service: Program Files (x86)\Lansweeper\Tools\CompactToServer.exe

Hit the upper Browse... button, select the lansweeperdb.sdf file you created a backup of earlier and hit Open. The schema versions of both databases should match, indicating that they're both on the same Lansweeper version. Hit Migrate Database to start the migration process. Note that this may take a while to complete.
migrate SQL Compact database to SQL LocalDB using CompactToServer.exe
CompactToServer.exe database schema versions

Step 5: restore Lansweeper files and restart services

Restore the Website subfolders and Encryption.txt file you created a backup of earlier. Afterward, restart the Lansweeper and web server services in Windows Services.

starting the web server service

Related Articles