Move your database from SQL LocalDB to SQL Server

Lansweeper data, reports and settings are stored in a database, which is hosted in either Microsoft SQL Compact, Microsoft SQL LocalDB or Microsoft SQL Server. If your database is hosted in LocalDB, you can move it to SQL Server on the same or a different machine, if at some point required. If you are unsure which database server you are using, browse to the Configuration > Your Lansweeper Licensesection of the web console.

Prior to the migration, make sure to install SQL Server on the machine where the new database will be hosted. You should install SQL Server 2022 or higher, as Lansweeper's LocalDB database server is based on SQL Server 2022.

1. Stop the Lansweeper Server service in Windows Services.
   Next, 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).

2. Back up your LocalDB database. Log into SQL Server Management Studio. If SQL Server Management Studio isn't installed on your Lansweeper server, we recommend downloading it online. The instance name you need to submit in Management Studio to access your LocalDB database is (localdb)\.\LSInstance and you can access the instance with the Windows user that performed your Lansweeper installation. Create a backup of your LocalDB database by right-clicking the lansweeperdb database in SQL Server Management Studio and selecting Tasks > Back Up ... in the menu.

   • Database: lansweeperdb
   • Backup type: Full
   • Backup component: Database
   • Back up to: Disk, add a location where the backup file will be stored.
   • In the Options tab, you can tick Verify Backup When Finished to check the integrity of the backup file.

3. Download the latest Lansweeper installer and run it on your new database server.

4. Configure the wizard as follows. This installs a default Lansweeper database in the SQL Server instance you specify.

   • Select Advanced install.
   • Under DataBase Type, select SQL Server.
   • Select New Lansweeper Database.
   • Leave New Lansweeper Service and New Lansweeper Website cleared. You're only moving the database, not the other components.

   

   Don't try to manually create the database. Let the installer do this for you.

5. Restore your database backup on your new server by right-clicking the "lansweeperdb" database in SQL Server Management Studio and selecting Tasks > Restore > Database... in the menu.

6. Select Device under Source and browse to the backup file "lansweeperdb.bak" that you previously created. Make sure you've selected the correct destination "lansweeperdb" and source for the restore operation.

7. In the Files tab select Relocate all files to folder and check Overwrite the existing database in the Options tab. Select OK to perform the restore.

8. Execute the following script in SQL Server Management Studio to reset the lansweeperuser SQL login used by the Lansweeper service and web console to connect to the database. Replace 'lansweeperuserpassword' with the password you want to use for the lansweeperuser login, leaving the single quotes in the script.

   /* Makes sure there are no objects in the lansweeperuser schema, so the lansweeperuser SQL user can be reset */
   USE lansweeperdb
   GO
   IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'lansweeperuser')
   BEGIN
       DECLARE c_ALTSCHEMA CURSOR FOR
       SELECT 'ALTER SCHEMA dbo TRANSFER lansweeperuser.' + name + ';'
       FROM sys.objects
       WHERE SCHEMA_NAME(SCHEMA_ID) = 'lansweeperuser'
   
       DECLARE @SQLStmt NVARCHAR(200)
       OPEN c_ALTSCHEMA
       FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
       WHILE @@FETCH_STATUS = 0
       BEGIN
           EXEC(@SQLStmt)
           FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
       END
       CLOSE c_ALTSCHEMA
       DEALLOCATE c_ALTSCHEMA
   END
   GO
   
   /* Resets the lansweeperuser SQL user */
   USE lansweeperdb
   GO
   IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'lansweeperuser')
       DROP SCHEMA lansweeperuser
   GO
   IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'lansweeperuser')
       DROP USER lansweeperuser
   GO
   USE MASTER
   GO
   IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'lansweeperuser')
       DROP LOGIN lansweeperuser
   GO
   CREATE LOGIN lansweeperuser
       WITH PASSWORD = 'lansweeperuserpassword',
       CHECK_POLICY = OFF,
       DEFAULT_DATABASE = lansweeperdb,
       DEFAULT_LANGUAGE = [English]
   GO
   USE lansweeperdb
   GO
   CREATE USER lansweeperuser FOR LOGIN lansweeperuser
   GO
   EXEC sp_addrolemember [db_owner], 'lansweeperuser'
   GO
   
   /* Optional step to grant lansweeperuser additional permissions, which are required for syncing with Cloud */
   USE MASTER
   GO
   ALTER SERVER ROLE [dbcreator] ADD MEMBER [lansweeperuser]
   GO
   GRANT VIEW SERVER STATE TO lansweeperuser
   GO
   

   Don't skip this step. Restoring a database backup almost always corrupts the SQL user used by the Lansweeper service and web console to connect to the database. If you don't reset the user, the service and web console will be unable to connect to the database.

9. Submit the SQL instance name and database password in ConfigEditor.

   • Run the ConfigEditor tool, found at Program Files (x86)\Lansweeper\Tools\ConfigEditor.exe on the servers hosting your Lansweeper Server service and web console.
   • Click through any warnings the tool may be giving you about your password being incorrect.
   • Select the Data Source field, select Edit and submit the name of your new SQL Server instance.
   • Select the Password field, select Edit and submit the same password you previously used in the database script.
   • If the ConfigEditor tool has multiple tabs due to your server hosting multiple Lansweeper components, select the other tab, click through any warnings and repeat the password and instance changing process.
   • Select Save configs and restart service. You've now successfully migrated your SQL LocalDB database to SQL Server.