Moving your database from SQL Compact to SQL Server

This article explains how to migrate a Lansweeper database hosted in the Microsoft SQL Compact database server to Microsoft SQL Server. 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 Server
Old SQL Server Express versions (2000, 2005, 2008) are, like SQL Compact, limited to 4GB of data. If you've reached Compact's database size limit, make sure to move to a recent SQL Express version (2008 R2, 2012, 2014, 2016, 2017) that is limited to 10GB. You can of course move to a non-Express (non-freeware) edition of SQL Server as well, which is not limited in size.

To migrate a SQL Compact database to SQL Server, do the following:

  1. Download the latest Lansweeper installer.
  2. If your current SQL Compact installation is not on the latest Lansweeper version, use the installer you just downloaded to update the installation. Update instructions can be found in this knowledge base article.
    updating Lansweeper to migrate SQL Compact database to SQL Server
    Don't skip this step! The migration won't work if your SQL Compact database is on a different Lansweeper version than the SQL Server database you'll be creating later on.
  3. Stop the Lansweeper Server service in Windows Services
    stopping the Lansweeper Server service
  4. 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 web server service
  5. Create a copy of your SQL Compact database, which stores all of your data, reports and settings, 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
  6. 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.
    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.
  7. If you are using Lansweeper 6.0 or a more recent Lansweeper release, back up the following file as well:
    Program Files (x86)\Lansweeper\Key\Encryption.txt
  8. Replace your existing SQL Compact installation with a SQL Server one by performing a new, Advanced Install with the latest Lansweeper installer. Make sure you select SQL Server as your database server. Detailed instructions for performing an Advanced Install can be found in this knowledge base article.
    performing a new Lansweeper installation to migrate SQL Compact database to SQL Server
    performing an Advanced Lansweeper installation to migrate SQL Compact database to SQL Server
    You must reinstall all Lansweeper components: database, service and web console. You don't have to install them all on the same machine, but it is absolutely critical that you set up a new SQL Server database and connect a new service and web console to it. Don't try to manually create the database either. Let the installer do this.
  9. Configure your own custom database password to replace the random password generated by the Lansweeper installer, by following the instructions in this knowledge base article.
  10. Once again stop the Lansweeper and web server services in Windows Services.
  11. Run the following executable, found on the machine hosting your Lansweeper service:
    Program Files (x86)\Lansweeper\Tools\CompactToServer.exe
    migrate SQL Compact database to SQL Server using CompactToServer.exe
  12. Hit the upper Browse... button, select the lansweeperdb.sdf file you created a backup of earlier and hit Open.
  13. If the correct instance is not already selected, hit the lower Browse... button to submit the name of the SQL Server instance you want to migrate to. You can use the built-in lansweeperuser SQL user and the database password you configured earlier to make the connection.
    CompactToServer SQL Server instance details
  14. 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.
    CompactToServer database schema version
  15. Restore the Website subfolders and Encryption.txt file you created a backup of earlier.
  16. Restart the Lansweeper and web server services in Windows Services.
    restarting the web server service

Related Articles