How to Restore ConnectWise Automate Database

Overview

It is essential for your business to have backups of your IT environment. Lost data can cause a noteworthy emergency, lost income and extremely unhappy customers.

Info! This document gives well-ordered instructions to restore your Automate system from backup. We recommend you do not skip any step. If you are feeling uncomfortable performing the restore, do not hesitate to Contact us.

How ConnectWise Automate does backup of database?

You must be interested to know how ConnectWise Automate does the backup of database.

1. ConnectWise Automate create dump of each table and restore into %ProgramFiles%\ LabTech\Backup\TableBase folder.
2. And then packs all dump files into a zip file called LabTech.1.zip and places it in %ProgramFiles\LabTech\Backup.

Info:
ConnectWise Automate perform backup operation during the 3AM maintenance.

Steps to Restore ConnectWise Automate from Backup

Prerequisite

1. Make sure you logged in as Administrator to Connectwise Automate (formerly LabTech) server.
2. Download:
   All Batch and Powershell Scripts that are used in this article.
   
3. Make sure you have backup file that you want to use for restoring the ConnectWise Automate.
4. Make sure you know rootPassword of MySQL.
   
   • Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Automate\Setup
     registry key and copy the password that given in “ RootPassword ” value.

Note:
You may know the “RootPassword” by using batch script called ”
KnowYourRootPassword.bat“. And, edit the following files and replace the word ”
ProvideYourPasswordHereWithoutQuotes” with your root password.

• Drop and Create ConnectWise Automate Database.bat
• Import Data to Database.bat
• Import Structure to Database.bat

See screenshot.

• Know MySQLEXE file path. There are few steps you need to follow, in order to know MYSQL setup folder.

Note :

• Know to location of my.ini file. Open the properties of LabMySQL service and look at the path of
  Path to executable .
• Open the my.ini file in notepad and find “
  basedir ” variable.
• Open explorer and navigate to
  value of basedir variable and then open
  bin folder.
• There, you can see “mysql.exe” file. Hold
  shift key and
  right click on “mysql.exe” file and then click on “Copy as path”.

Note:
You just copied mysql.exe file’s full path. Open the following script files in notepad and provide path to “MySQLexe” variable (
without Quotes ).

• Drop and Create ConnectWise Automate Database.bat
• Import Data to Database.bat
• Import Structure to Database.bat

See screenshot.

1. Lastly, you need to know where your database is stored.
   • Again, Open
     my.ini file in notepad and find “
     datadir ” variable.
   • Value of
     datadir variable is your
     ConnectWise Automate database folder path.

Important: :

• Drop and Create ConnectWise Automate Database.bat
• Import Data to Database.bat
• Import Structure to Database.bat

Stop ConnectWise Automate Critical services

1. Stop all the following services.
   • LabMySQL (LabMySQL),
   • ConnectWise Automate Database Agent (LTAgent),
   • ConnectWise Automate Monitoring service (LTService) ,
   • ConnectWise Automate Monitoring Service Checkup Util (LTSvcMon),
   • ConnectWise Automate Redirector Service (LTRedirSvc).

Use the following Powershell command to stop ConnectWise Automate critical services

• Open Windows Powershell and run the following command.

Get-service LabMySQL,LTAgent,LTService,LTSvcMon,LTRedirSvc | ?{$_.status -eq 'running'}|stop-service


Info!
Please verify that all above services’ status manually by opening services.msc

Unzip ConnectWise Automate databse “Structure” and “Data” to “Labtech.1” folder on your desktop!

• Create a New folder on your desktop and rename it LabTech.1
• Navigate to the C:\Program Files\LabTech\Backup folder
• And right-click on the most recent backup file, Labtech.1
• And select ExtractAll zip file’s content in recently created folder Desktop\LabTech.1\

• Now, navigate to the C:\Program Files\LabTech\Setup directory
• Copy the following files and paste them in C:\Users\Administrator\Desktop\LabTech.1\ folder

• DBaseTable.sql,
• DBaseViews.sql ,
• DBaseTrigger.sql,
• DBaseProc.sql.

Delete logs files and a folder

Open to C:\Program Files (x86)\LabTech\mysql\data folder and delete the following log files and a folder.

• • labtech (a folder).
  • ibdata1, ib_logfile0 and ib_logfile1 log files.

Note:
If you don’t find these files and given folder there then check “Program Files” directory.

Re-create ConnectWise Automate database

Before proceeding further, Please make sure LabMySQL is running. If not, open the command prompt and run the following command.

Download File: Start LabMySQL Service.bat
net start LabMySQL


Now, you need to drop “ConnectWise Automate” database and create it again.Use the following batch script to recreate MySQL database called “LabTech”. Replace ProvideYourPasswordHereWithoutQuotes with root password in the following script.

Download File: Drop and Create LabTech Database.bat

@echo off

SET rootuser=root
SET rootPassword=ProvideYourPasswordHereWithoutQuotes
SET MySQLexe=”c:\program files (x86)\labtech\mysql\bin\mysql.exe”

%MySQLexe% –user=%rootuser% –password=%rootPassword% –execute=”drop database labtech;”
Echo ……………….Dropped ConnectWise Automate Database………………
Echo.
pause > nul
Echo ……………….Creating ConnectWise Automate Database……………….
Echo.

%MySQLexe% –user=%rootuser% –password=%rootPassword% –execute=”create database ConnectWise Automate;”

Echo ConnectWise Automate Database has been created successfully……………….
echo.
pause > nul

Import ConnectWise Automate database structure

After re-creating database, you need to import MySQL “Tables”, “Views”, “Triggers” and “Procedures” into database. Use the following batch script.

Download File:
Import Structure to Database.bat

@echo off

SET rootuser=root
SET rootPassword=ProvideYourPasswordHereWithoutQuotes
SET MySQLexe=”c:\program files (x86)\labtech\mysql\bin\mysql.exe”
SET sourceFolder=%USERPROFILE%\Desktop\Labtech.1

%MySQLexe% –user=%rootuser% –password=%rootPassword% -b labtech < “%sourceFolder%\DBaseTable.sql”
echo.
Echo ……………….Imported LabTech Tables structure………………
Echo.

%MySQLexe% –user=%rootuser% –password=%rootPassword% -b labtech < “%sourceFolder%\DBaseViews.sql”
echo.
Echo ……………….Imported LabTech Views………………
Echo.

%MySQLexe% –user=%rootuser% –password=%rootPassword% -b labtech < “%sourceFolder%\DBaseTrigger.sql”
echo.
Echo ……………….Imported LabTech Triggers………………
Echo.

%MySQLexe% –user=%rootuser% –password=%rootPassword% -b labtech < “%sourceFolder%\DBaseProc.sql” echo. Echo ……………….Imported LabTech Procedures……………… Echo. Echo ——————— Press any key to Exit———————- Pause > nul

Import ConnectWise Automate Database data

Now, we need to import data in LabTech database. Use the following batch script for that.

Download File: Import Data to Database.bat


@echo off

SET rootuser=root
SET rootPassword=ProvideYourPasswordHereWithoutQuotes
SET MySQLexe=”c:\program files (x86)\labtech\mysql\bin\mysql.exe”
SET sourceFolder=%USERPROFILE%\Desktop\Labtech.1

FOR /f %%a IN (‘dir /b %sourceFolder%\*.sql’) DO “%MySQLexe% –user=%rootuser% –password=%rootPassword% -b labtech < “%sourceFolder%\%%a” Echo. Echo ……………….Imported LabTech data in database……………… Echo ——————— Press any key to Exit———————- pause > nul

Log in to the Control Center

Log in to the
Control Center
and verify that all agents are there, and then wait approximately 10 minutes for
the agents to begin to check in