Shell script to recover corrupted Confluence h2 database

confluence-h2-restore.sh

#!/bin/bash
# This script has to be run as your confluence user or root
# Create a backup of, and attempt to restore a confluence h2 internal database

# Go home.
cd ~

# Stop confluence
/opt/atlassian/confluence/bin/stop-confluence.sh
# Really though...
pgrep java | xargs kill

#create a good backup of your database files
mkdir -p ~/confluence-db-restore/originals
cp /var/atlassian/application-data/confluence/database/*.db ~/confluence-db-restore/originals

# create another good backup of your database files
tar -cvzf ~/confluence-db-restore/"$(date '+%Y-%m-%d')_h2db_backup.tar.gz" ~/confluence-db-restore/originals

# create a copy of your db to work from
cp ~/confluence-db-restore/originals/*.db ~/confluence-db-restore

# Create recovery SQL script
/opt/atlassian/confluence/jre/bin/java -cp \
/opt/atlassian/confluence/confluence/WEB-INF/lib/h2-*.jar \
org.h2.tools.Recover

# move working files and restore database
mkdir -p ~/confluence-db-restore/recovery
mv *.sql ~/confluence-db-restore/recovery
cd ~/confluence-db-restore/recovery
/opt/atlassian/confluence/jre/bin/java -cp \
/opt/atlassian/confluence/confluence/WEB-INF/lib/h2-*.jar \
org.h2.tools.RunScript -url jdbc:h2:./h2db -user sa -script *.sql


################################################################################
# IMPORTANT THAT YOU MOVE THE RECOVERED DATABASE IF THE RESTORE WAS SUCCESSFUL #
# Uncomment this line to copy the restored database over your existing         #
################################################################################

# cp -f ~/confluence-db-restore/recovery/*.db /var/atlassian/application-data/confluence/database/
# chown -R confluence:confluence /var/atlassian/application-data/confluence

systemctl restart confluence

There are only a handful of commands executing in this gist. I'd recommend stepping through each of them manually and then verify that the restore command on line 32 functions. If that works you should have a .sql file. This script is years old and may need updating. We've since migrated to the SaaS platform so I don't have anywhere to test and validate this script anymore. Best of luck recovering your data! Thanks, - Chris
…
On Tue, Oct 12, 2021 at 10:57 AM KO @.> wrote: @.* commented on this gist. ------------------------------ Hey guys, What's sql file? It tells me 'sql file not found' Thanks. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://gist.github.com/6220fbf8adb7f46044e4578f67f3a28e#gistcomment-3924555, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAOKLVBE7GS7RGBEYGPD75DUGREEDANCNFSM4I3TNZFQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

Thank you! I will try again.

hi Team, java -cp /lib/h2-*.jar org.h2.tools.Recover step is creaing a .sql file with table names O_Number instead of actual table names.

INSERT INTO O_87 VALUES(10606544, TIMESTAMP '2022-11-28 13:44:14.143', 1, 'comments ', NULL, 635, 188, NULL);

please let me know how to fix this