Export Custom Reports and Import to a new SQL Database

[Alig26]

Hello,

We had to start a fresh Lansweeper install in order to utilize the cloud features, since our old LS Classic DB was on Azure Managed Instance. We have several reports we need to migrate from the old SQL DB to the new SQL DB. I found this post about providing an export of all our existing custom reports. But I'm confused on how I can import this information into the new SQL DB?

Export custom reports (SQL Server only) - Lansweeper Community - 53130

Thanks,

[jduke_halls]

Before we start, stop the Lansweeper service and the IIS Express (or the Web Server if you went the complicated route) service on the new LanSweeper server.

If you used the query from Export custom reports (SQL Server only) - Lansweeper Community - 53130 using a tool like SQL Server Management Studio, copy the output of all the rows under "TsysReports" column to a new script and save that script as "LanSweeperTSysReports.sql". 

The "View" Column contains the actual report query. You can paste each of these lines into another query with the SQL keyword "GO" in between.  Example:

CREATE VIEW dbo.web50rep00e8000eb2e449d5a413f82b7e23cbea AS Select Distinct Top 1000000 tblAssets.AssetID, ...
GO

CREATE VIEW dbo.web50rep014a023df5314ada89f74154a38c754b AS Select Top 1000000 tblassets.AssetID, ...
GO

The reason for the "GO" keyword is that MSSQL can only create one view per statement. When you're done with all of those, save that file as "LanSweeperView.sql".

Copy the files to the new server, or connect to your new LanSweeper database server using SQL Server Management Studio (or other tool).

Right-click the new LanSweeper database and click "New Query".  This will select the database for when you've reopened the scripts.

File > Open and select the "LanSweeperView.sql" script. Press the "Execute" button. It should output "Commands completed successfully" in the output below the script.

File > Open and select the "LanSweeperTSysReports.sql" script you saved above. Press the "Execute" button.  It should output "Commands completed successfully" in the output below the script.

Restart the LanSweeper service and the IIS Express (or Web Server) service on the new server. Open up Lansweeper on the new server and check your reports. They should all be there.

[ErikT]

@Alig26 

The custom reports should be restored if you restore your Lansweeper installation from backup.

Restore your installation from a backup 

[Alig26]

Erik,

I can't restore from backup, our DB was on Azure SQL Managed Instance, and LS Cloud doesn't support it, so we rebuilt our LS environment from scratch because we can't do a backup/restore from Azure MI to SQL in a VM. So I found that forum post above about how to get a list of our custom reports from the old DB, but it doesn't explain how to import it into a new LS DB.

Thanks