Moving your database from SQL LocalDB to SQL Server

This article explains how to migrate your Lansweeper SQL LocalDB database to a SQL Server instance. 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 following section of the web console: Configuration\Your Lansweeper License

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 2014 or higher, as Lansweeper's LocalDB database server is based on SQL Server 2014.

To migrate a Lansweeper database hosted in LocalDB to a SQL Server instance, do the following:

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).


Step 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 ...

  • 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.

Step 3: install a Lansweeper database under SQL Server

Run the Lansweeper installer on your new database server and configure it exactly as shown below. This will install a default Lansweeper database in the SQL Server instance specified by you.

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

Step 4: restore the database

Restore your database backup on your new server by right-clicking the lansweeperdb database in SQL Server Management Studio and selecting Tasks\Restore\Database.... 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. In the Files tab select Relocate all files to folder and check Overwrite the existing database  in the Options tab. Then hit OK to perform the restore.

Step 5: reset "lansweeperuser"

Execute the script below 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 what's marked in bold 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
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
GO

/* Resets the lansweeperuser SQL user */
USE lansweeperdb
GO
DROP SCHEMA lansweeperuser
GO
EXECUTE sp_droplogin lansweeperuser
GO

USE MASTER
GO
EXEC sp_addlogin 'lansweeperuser', 'lansweeperuserpassword', 'lansweeperdb', [English]
GO

USE lansweeperdb
GO
EXEC sp_grantdbaccess 'lansweeperuser', 'lansweeperuser'
GO
EXEC sp_addrolemember [db_owner], 'lansweeperuser'
GO

/* Optional step to grant lansweeperuser dbcreator rights, which are required for Lansweeper Enterprise */
USE MASTER
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [lansweeperuser]

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.

Step 6: submit SQL instance name and database password in ConfigEditor

  • Run the following tool, found on the servers hosting your Lansweeper Server service and web console:
    Program Files (x86)\Lansweeper\Tools\ConfigEditor.exe

  • Click through any warnings the tool may be giving you about your password being incorrect.
  • Select the Data Source field, hit the Edit button and submit the name of your new SQL Server instance.
  • Select the Password field, hit the Edit button 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.
  • Hit Save configs and restart service. You've now successfully migrated your SQL LocalDB database to SQL Server.

Related Articles