Back Up and Restore Connect Database via Command Line

[SophiaF]

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:

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

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.

[Treyson]

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]

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

[lepome]

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

BACKUP to '<location.zip>';

step.

[lepome]

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.