Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Connect Knowledge Base

Definitive answers from Connect experts.

Back Up and Restore Connect Database via Command Line

SophiaF
Alteryx
Alteryx
Created

Backing up and Restoring Alteryx Connect Database via Command Line


Backing up and restoring Connect can easily be done from the Administrator Console within Connect. Please see the following Help article for details steps: Back Up and Restore Connect. However, for various reasons, it may be advantageous to do the backup/restore via the command line or a script. This article walks you through how to do this.

 

Part 1: Creating a backup of Connect's H2 database via the command line:

 

1. Stop AlteryxConnect service

2.In a command prompt, change directories to the appropriate folder:

cd "C:\Program Files\AlteryxConnect\webapps\ROOT\WEB-INF\lib\"

 

3. Run the following command to enter the H2 shell:

For version 2020.1
 

"C:\Program Files\AlteryxConnect\jre\bin\java.exe" -cp h2-1.4.200.jar org.h2.tools.Shell -url jdbc:h2:"C:\Program Files\AlteryxConnect\ac_work\ac_data" -user sa -password 

 

For earlier versions
 

"C:\Program Files\AlteryxConnect\jre\bin\java.exe" -cp h2-1.4.196.jar org.h2.tools.Shell -url jdbc:h2:"C:\Program Files\AlteryxConnect\ac_work\ac_data" -user sa -password 

 

4. You should now be in the H2 shell, where you will enter the following SQL command:

BACKUP to '<path to backup>\<filename>.zip';

Please note that this backup file must be a .zip file.

 

If successful, you will see something like:

fxvqZw

 

5. Exit the shell:
 

exit

 

6. Restart the AlteryxConnect service

You should now have the .zip folder in the directory that you specifed.

 

To script this backup:

 

You can use the following command to execute the SQL query directly from a script:

net stop alteryxconnect

"C:\Program Files\AlteryxConnect\jre\bin\java.exe" -cp "C:\Program Files\AlteryxConnect\webapps\ROOT\WEB-INF\lib\h2-1.4.200.jar" org.h2.tools.Shell -url jdbc:h2:"C:\Program Files\AlteryxConnect\ac_work\ac_data" -user sa -password <password> -sql "BACKUP TO '<path to backup location>\<filename>.zip';"

net start alteryxconnect

 

Part 2:Restoring a backup of Connect's H2 database via the command line:

 

1. Stop AlteryxConnect service

2. Open a command prompt as an Administrator

3. Run the following command:
 

"C:\Program Files\AlteryxConnect\jre\bin\java.exe" -cp "C:\Program Files\AlteryxConnect\webapps\ROOT\WEB-INF\lib\h2-1.4.196.jar" org.h2.tools.Restore -db ac_data -file "<path to database backup>\<filename>.zip" -dir "C:\program files\alteryxconnect\ac_work"

 

4. Start the AlteryxConnect service

5. Perform a full reindex of the database to complete the restoration:

  1. Log in to Alteryx Connect
  2. Click your icon in the top-right of the screen and select Administration
  3. From the Admin Menu, click Reindex
  4. Click Full Reindex

 

Common Errors

 

'java' is not recognized as an internal or external command,operable program or batch file.

 

As a note, Java must be available to call directly (meaning it must be added to the environmental variable PATH) or you will need to list the full path to the java executable found in C:\Program Files\AlteryxConnect\jre\bin. The above commands already reference the full path, so this will not occur if you run the commands as written above.

 

Error: Could not find or load main class org.h2.tools.Shell

 

This means that the .jar file is incorrect. Please ensure that the .jar file listed in the command matches the h2*.jar file located inC:\Program Files\AlteryxConnect\webapps\ROOT\WEB-INF\lib

 

TQNtv1

 

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Database may be already in use: null.

 

The AlteryxConnect service is still running - please stop the service and try again.

No ratings
Comments
Treyson
12 - Quasar
12 - Quasar

Hello @SophiaF!

 

If we were to migrate our connect instance off of an existing machine and onto a new machine, would this method be the suggested route? I could just put the backup in a shared location and then reference it after installing connect on the new machine?

SophiaF
Alteryx
Alteryx

@Treyson - yes I would recommend following this. So long as you are not leaving the database on a shared location (this to me sounds like a network drive?) but are moving it to a drive on the new Connect server, that would work.

LisaL
Alteryx
Alteryx

Notes to self:
single quotes and semicolon are important when doing the

BACKUP to '<location.zip>';

step.

LisaL
Alteryx
Alteryx

An alternative that sometimes works is to use a script rather than the shell:

That takes the form of 

"<installDirectory>\jre\bin\java" -cp "<installDirectory>\webapps\ROOT\WEB-INF\lib\h2-1.4.200.jar" org.h2.tools.Script -url "jdbc:h2:<installDirectory>\ac_work/ac_data" -user sa -password Conn3ct!! -script "C:\temp\backup.zip" -options compression zip

then after removing ac_data.mv.db and ac_data.trace.db, one can restore using a script command of the form

"<installDirectory>\jre\bin\java"  -cp "<installDirectory>\webapps\ROOT\WEB-INF\lib\h2-1.4.200.jar" org.h2.tools.RunScript -url "jdbc:h2:<installDirectory>\ac_work/ac_data" -user sa -password Conn3ct!! -script "C:\temp\backup.zip" -options compression zip

 

Both of these backup and restore sequences are sometimes proposed to address corrupted chunks in the database.