Moving from SQL local DB to SQL server when you don't have a backup of your Lansweeper database

[ErikT]

When for any reason, the SQL local DB fails to start, is inaccessible, or is entirely non-functional, your Lansweeper installation is down. In this case, we advise moving to SQL Server Express, the free SQL server offered by Microsoft. 

However, moving to a new SQL server requires a database backup. Unfortunately, things broke unexpectedly, and as things go, this backup is not available.

Now what?

Don't panic! You can still move to SQL Server and rescue that valuable Lansweeper data and your beloved Lansweeper installation. If the database .mdf file is still safe and sound, that is.

How?

This is quite a lengthy procedure so be prepared to spend some time on it.  

The main steps are:

• Install and configure a SQL Server instance if you don't have one.
• Attach your existing Lansweeper database to this instance and gets your Lansweeper installation running on SQL Server instead.
• Clean up SQL LocalDb

Install and configure a SQL Server instance

• If you don't have an SQL Server instance, SQL Server 2014 or newer, download and install SQL Server 2019 Express. This is a free version of the Microsoft SQL server. 
• Run through the SQL Server 2019 installation.
• Download and install SQL Server Management Studio if you don't have it on your Lansweeper server.
• Use SQL Server Management Studio to configure your SQL Server instance according to our recommendations. 
  • If you've just installed SQL Server and installed a default instance, the instance name will simply be your computer name or localhost.
  • If you installed a named instance, the instance name would be computername\instancename or localhost\instancename

Attach your existing Lansweeper database

Get some coffee. This is the lengthy part.  

• Make sure all Lansweeper services are stopped under services.msc.
  • The Lansweeper Server services
  • The Lansweeper LocalDb Service (likely this is already stopped but check just to be sure)
  • The web server service. Likely this is IIS Express but could be IIS also.
    
    
• Copy the database files from lansweeperdb.mdf and lansweeperdb_log.ldf from Program Files (x86)\Lansweeper\SQL Data to the folder below.
  • Note that if you're using an existing SQL Server instance, this folder path may be different. It is SQL Server version-specific.

Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA

• Open SQL Server Management Studio and connect to your SQL Server instance.
• Right-click Databases and click Attach...
  
• Click Add... and select lansweeperdb.mdf
  
• Click Ok
• The lansweeperdb should now be listed among your databases.
• Right-click lansweeperdb and click New Query
  
• Perform step 6 from this article in this New Query window. Make sure to replace the password in the script with the password you would like to use.
  

  Note:

  Note that the script will likely throw an error that lansweeperuser does not exist. This is normal.

• Go to Security\Users under lansweeperdb and verify that lansweeperuser is present
  
• Open ConfigEditor.exe under Program Files (x86)\Lansweeper\Tools

Note:

You'll get an error when you open the tool. This is normal. It's still trying to connect to LocalDb at this stage

• In both the Service and Website tabs, specify the following:
  • Data source: your new SQL Server instance
    • The Data Source should match what you're connecting to in SQL Server Management Studio. It will likely not match up precisely with the example screenshot below.
    • Password: the password you just specified for lansweeperuser in the script you've just run.
• Click Save configs and restart the service.
  
  
• Start the IIS Express service manually.
• Browse to the web console. It should be functional and use your existing database at this stage.

Cleaning up LocalDb

Once you've confirmed that your Lansweeper installation is operational, all that remains is to clean up SQL LocalDb.

• Open a CMD as Administator.
• Run the command below.

sc delete lansweeperlocaldbservice​

• Go to Add/Remove Program and uninstall SQL LocalDb.