ServiceNow MySQL Database Restore
1. Infrastructure
GitHub repo: hmcts/servicenow-infrastructure
Pipeline: hmcts.servicenow-infrastructure
2. Azure Resources
- Resource Group:
rg-servicenow-prod - Storage Account:
servicenowprodukssa - Private Endpoint VNet:
ss-prod-vnet - Private Endpoint Subnet:
private-endpoints
3. Storage Account Setup and Database Download
3.1 SFTP User Creation
First, create a new SFTP user on the Storage Account servicenowprodukssa as this is not done through automation.
3.2 SSH Key Generation and Configuration
To mount using SFTP authentication on the bastion-prod VM, generate an SSH key pair on the bastion VM itself:
ssh-keygen -t rsa -b 4096 -f ~/.ssh/azsftp_id_rsa
This stores the private key at the specified path. Associate the public key with the SFTP user created on the storage account.
3.3 Mounting the Storage Account
After logging into the bastion-prod VM as root user:
- Create a new directory:
mkdir -p ~/azsftp2
- Mount the Storage Account using the private key:
sshfs \
"servicenowprodukssa.mysql-backup.mysql@servicenowprodukssa.blob.core.windows.net:/" \
/root/azsftp2 \
-o port=22 \
-o IdentityFile=~/.ssh/azsftp_id_rsa \
-o reconnect \
-o ServerAliveInterval=15 \
-o ServerAliveCountMax=5 \
-o TCPKeepAlive=yes
- Verify if the SFTP mount is active:
mount | grep sshfs
- Test the mount by creating directories and files:
cd ~/azsftp2
mkdir test
cd test
vi test.txt
Verify that these files appear in the Storage Account container.
3.4 Downloading the MySQL Database
3.4.1 Connect to ServiceNow SFTP
Receive the private key from the ServiceNow team via email and decrypt it using the password they provided.
Connect to ServiceNow SFTP:
sftp -i private_key mojcppprod_sftpmig@sftp-lhr.service-now.com
- Enter the password when prompted (same password used to decrypt the private key).
3.4.2 Download Database Files
- After connecting to ServiceNow SFTP, verify the uploaded files:
sftp> ls -l
-rw-r-xr-- 1 0 0 527356255387 Sep 9 15:02 CHG66945130_mojcppprod_1.sql.gz
-rw-r-xr-- 1 0 0 520794 Sep 9 14:41 CHG66945130_mojcppprod_1_schema.sql.gz
- Set the mounted location as the download destination:
sftp> lcd /root/azsftp2
- Download the database file:
sftp> reget CHG66945130_mojcppprod_1.sql.gz
Note: The download process took several hours as the file is approximately 492 GB in size.