community
cancel
Showing results for 
Search instead for 
Did you mean: 

alteryx connect Knowledge Base

Definitive answers from Connect experts.

Back Up and Restore Connect Database via Command Line

Moderator
Moderator
Created on
PRODUCT: Alteryx Connect 
VERSION: 18.3 
LAST UPDATE: 2018-09-28 

ATTACHMENT: None


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: 

"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 <password>

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

BACKUP to '<path to backup>';

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

 

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.196.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>';"
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>" -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 in C:\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.

Comments
Quasar
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?

Moderator
Moderator

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