Move your database from SQL Compact to SQL Server

[Lansweeper]

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.

Old SQL Server Express versions are limited to 4 GB of data like SQL Compact. If you've reached Compact's database size limit, move to a recent SQL Express version (2014, 2016, 2017) limited to 10 GB. You can, of course, move to a non-Express (non-freeware) edition of SQL Server, which is not limited in size.

1. Download and run 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.
   
   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 create later.
3. Stop the Lansweeper Server service in Windows Services.
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).
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 can be found at Program Files (x86)\Lansweeper\SQLData\lansweeperdb.sdf.
6. If you added any documents, images, widgets, or other files to Lansweeper, back these up. 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 Encryption.txt as well. The file can be found at Program Files (x86)\Lansweeper\Key\Encryption.txt.
8. Run the latest Lansweeper installer.
9. 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.
   
   You must reinstall all Lansweeper components: database, service, and web console. You don't have to install them on the same machine, but you must set up a new SQL Server database and connect a new service and web console. 
   Don't try to create the database manually. Let the installer do this.
10. Configure your custom database password to replace the random password generated by the Lansweeper installer by following the instructions in this knowledge base article.
11. Stop the Lansweeper and web server services in Windows Services.
12. Run the CompactToServer executable found at Program Files (x86)\Lansweeper\Tools\CompactToServer.exe on the machine hosting your Lansweeper service.
13. Select the upper Browse... button, select the lansweeperdb.sdf file you created a backup of earlier and click Open.
14. If the correct instance is not selected, select 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 connect.
15. The schema versions of both databases should match, indicating that they're both on the same Lansweeper version. Select Migrate Database to start the migration process. Note that this may take a while to complete.
16. Restore the Website subfolders and Encryption.txt file you created a backup of earlier.
17. Restart the Lansweeper and web server services in Windows Services.

---

Was this post helpful? Select Yes or No below!
Did you have a similar issue and a different solution? Or did you not find the information you needed? Create a post in our Community Forum for your fellow IT Heroes!
More questions? Browse our Quick Tech Solutions.

---