Alteryx Connect

Definitive answers from Connect experts.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Back Up and Restore Connect Database via Command Line

Alteryx
Alteryx
Created
PRODUCT:Alteryx Connect
VERSION: 18.3+
LAST UPDATE:2020-03-18

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 

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



BACKUP to '\.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

 

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  -sql "BACKUP TO '\.zip';"
net start alteryxconnect


 

Part 2:Restoringa 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 "\.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 selectAdministration
  3. From the Admin Menu, clickReindex
  4. ClickFull 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.

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

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.