Skip to main content
Code42 Support

Back up Code42 server database dumps to another location

Available in:

StandardPremiumEnterprise
Small Business
Applies to:

Overview

Code42 servers running the Code42 environment perform daily dumps, or backups, of their internal database at least once per day and store these database backups on the Code42 server file system. Each individual store point has copies of the database dumps, but we recommend backing up the database dumps to a separate location as part of a disaster recovery plan.

It is possible to retrieve the database dumps manually, but it is much more useful to configure an automated process to retrieve the database dumps and store them in a secure location.

Code42 Managed Private Cloud
Managed Private Cloud customers must work with our Customer Champions for Code42 for Enterprise support to set up this service for their managed appliances.

Considerations

  • You are responsible for adapting the steps in this article to your environment, network configuration, and operating systems.
  • This procedure requires you to have an available server on which to store the database dumps. You are responsible for setting up and maintaining the server that stores the backups of the database dumps.
  • If your Code42 environment uses automatic secure keystore, you must have a copy of your password file in order to use the backed-up database dumps.

Definitions

database dump

A copy of the Code42 environment's internal database. The standard installation of a Code42 server "dumps" a copy of its internal database to the file system on that Code42 server as part of scheduled daily services. (A Code42 server using a PostgreSQL database does not perform a database dump.) These dumps contain detailed information about your Code42 environment, and can be used to recover your Code42 server. You can sanitize your database dumps to remove sensitive information before sending them to Customer Champions as part of a troubleshooting request.

internal database

The database used to index the archives that store data. The internal database is essential for performing any actions on the archives, including restoring data. It also stores user account information.

source server

For the purposes of this article, the source server is the server that currently holds the internal database that you wish to back up.

Linux

This section describes one method to retrieve the database dumps on Linux, including these high-level tasks:

  1. Setting up an SSH File Transfer Protocol (SFTP) server on the source Code42 server
  2. Using an SFTP client on the destination server to retrieve the database dumps
  3. Automating the retrieval of the database dumps
  4. (Optional) Configuring SFTP to use key-based authentication for increased security

Before you begin

  • This tutorial uses Linux as the example operating system, but the general principles apply to other server operating systems.
  • You should be familiar with the Linux or Unix command line and the SSH File Transfer Protocol (SFTP).
  • You should be familiar with the cron job scheduler used by Linux and Unix, and know how to add and modify crontab files.
  • Your system must have OpenSSH installed.

Step 1: Set up an SFTP server on the source enterprise server

  1. Create a directory database dumps by entering the following command in your terminal window as root:
    mkdir -p /sftpuser/dumps
  2. Create an sftp user group with this command:
    groupadd sftpusers
  3. Create the user with the command:
    useradd -G sftpusers -d /sftpuser sftpuser
  4. Create a password for the user:
    passwd sftpuser
    If your source server does not have SFTP installed, install it before continuing.
  5. Using a text editor such as vim, modify /etc/ssh/sshd_config:
    • Comment out the following line so that it looks like this:
      #Subsystem sftp /usr/libexec/openssh/sftp-server
    • Add the following lines to the end of /etc/ssh/sshd_config
      Subsystem sftp internal-sftp
      Match Group sftpusers
      ChrootDirectory /sftpuser
      ForceCommand internal-sftp
  6. Restart sshd with the following command:
    /etc/init.d/ssh restart
    The server responds: [ ok ] Restarting OpenBSD Secure Shell server: sshd.
  7. Create a bind mount to the database dump directory on your source server.
    A bind mount is necessary as symbolic links do not work with sftp.
    • Using a text editor such as vim, add the following line to /etc/fstab:
      /var/opt/proserver/dumps /sftpuser/dumps none bind
      Replace /var/opt/proserver/dumps with the actual path to your database dumps directory on your source server if it uses a non-default path.
    • To put the changes to /etc/fstab into effect now, enter the following command:
      mount -a
  8. Enter the command:
    ls /sftpuser/dumps
    You should see a listing of the database dumps:
    proserver-db_1375333203614_2014-01-04_033207.sql.gz
    proserver-db_1375333203614_2014-01-05_025342.sql.gz
    proserver-db_1375333203614_2014-01-06_021834.sql.gz
    proserver-db_1375333203614_2014-01-07_024713.sql.gz
    proserver-db_1375333203614_2014-01-08_024248.sql.gz
    proserver-db_1375333203614_2014-01-15_022254.sql.gz
    proserver-db_1375333203614_2014-01-16_020101.sql.gz
    proserver-db_1375333203614_2014-01-17_020101.sql.gz
    proserver-db_1375333203614_2014-01-30_022520.sql.gz
  9. As root, change permissions for the dumps directory to allow remote user access with the following command:
    chown -R root:sftpusers /sftpuser/dumps

Step 2: Retrieve the database dumps on the destination server

Test the ability to use your SFTP client on the destination server to retrieve the database dumps from the source server. Here is an example test from the destination server.

  1. Run the following command replacing the IP address or hostname with the IP address or hostname of your source server:
    myserver:~ user$ sftp sftpuser@192.0.2.10
    You will be prompted for the sftpuser's password.
  2. List the directory contents of the dumps folder with the following command:
    ls dumps
    You should see a listing of the database drops.
    sftp> ls dumps
    dumps/proserver-db_1375333203614_2014-01-04_033207.sql.gz
    dumps/proserver-db_1375333203614_2014-01-05_025342.sql.gz
    dumps/proserver-db_1375333203614_2014-01-06_021834.sql.gz
    dumps/proserver-db_1375333203614_2014-01-07_024713.sql.gz
    dumps/proserver-db_1375333203614_2014-01-08_024248.sql.gz
    dumps/proserver-db_1375333203614_2014-01-15_022254.sql.gz
    dumps/proserver-db_1375333203614_2014-01-16_020101.sql.gz
    dumps/proserver-db_1375333203614_2014-01-17_020101.sql.gz
    dumps/proserver-db_1375333203614_2014-01-30_022520.sql.gz
    This example shows the "sftpuser" listing the contents of the dumps directory. The contents of the dumps directory are now available for downloading to your destination (remote) server.

Step 3: Automate the retrieval of database dumps

Use the Linux cron daemon to automatically retrieve database dumps from the source server.

Examples
The entries below are for example purposes only. Please note that this script may lack features that you would want for your environment, such as versioning, time-stamping, etc.
  1. Create a shell script named "sftpBackup".
  2. Enter the following commands, replacing the variables below for your environment:
    • Directory (/home/proserver/DB_Backups) with your database backup directory.
    • SSHPASS (examplePassword) with your password for the user "sftpuser".
    • SSHD host location (192.0.2.10) with the IP or FQDN of your source server.
      #!/bin/sh
      export SSHPASS=examplePassword
      sshpass -e sftp -oBatchMode=no -b - sftpuser@192.0.2.10 << !
      lcd /home/proserver/DB_backups
      get -r dumps
      bye
      !
  3. Save your shell script.
  4. Schedule your shell script by adding the entry below to crontab:
    • The path to the script sftpBackup should be replaced with the path to your working script.
    • This entry is configured to run the script every day at 8:00 p.m. US Central Time. The values for the dates and times in the crontab entry should be modified so that it is suitable for your environment.
      00 20 * * * /usr/local/bin/sftpBackup

Step 4: Public/private key access (optional)

Public/private key access is more secure than password authentication, so you can optionally configure SFTP to use key-based authentication.

  1. Enter the following commands as root on the source server (source of the database dump files and has the user "sftpuser"):
    mkdir /sftpuser/.ssh
    cd /sftpuser/.ssh
    ssh-keygen -t rsa -f /sftpuser/.ssh/id_rsa
    cat /sftpuser/.ssh/id_rsa.pub >> /sftpuser/.ssh/authorized_keys
    chmod 700 /sftpuser/.ssh/
    chown -R sftpuser:sftpuser /sftpuser/.ssh
  2. Copy id_rsa (the private key file) to the server that will serve as the destination for the database dumps, and delete it from the source server. After copying the private key file to the destination server, enter the following command:
    rm /sftpuser/.ssh/id_rsa
  3. From the destination server, you can now log in securely with the following command on the source server:
    sftp -i ~/.ssh/id_rsa sftpuser@exampleServer
  4. Change your shell scripts and/or crontab files to reflect that fact that you are now using an identity file.

Technical note

You may choose to use rsync, rather than sftp, to transfer the database dumps. In this case, rsync could be used to mirror the database dump directory from one server to another. For example, you could mirror the database dumps from an active authority server to a standby authority server. You may wish to contact PRO Services for help with this configuration.

Windows

This section describes a method of retrieving database dumps on Windows, which includes these high-level tasks:

  1. Creating a PowerShell script to copy the database dumps
  2. Using Task Scheduler to automate the script
  3. Testing the configuration

Before you begin

  • This tutorial uses Windows 7 as the example operating system, but the general principles apply to other server operating systems.
  • You should be familiar with the Windows PowerShell command line and Windows networking.
  • You should be familiar with Task Scheduler used by Windows, and know how to add and modify scheduled tasks.
  • Your system must have Windows PowerShell installed.
  • This tutorial assumes that you have already configured a separate Windows server with a network share to receive the database dumps.

Step 1: Create a copy-item script in PowerShell

Windows PowerShell script files are plain-text files with the extension .ps1. The script below uses the Copy-Item cmdlet to copy your database dumps to the network share you created.

  1. Open a text editor.
  2. Enter the following text and replace the variables below:
    The database dumps location varies in different versions of Windows.
    Copy-Item -path <path_to_the_database_dump_folder>* -destination <path_to_the_secondary location> -force
    
  3. Save the file with the .ps1 extension.

Example a

This example uses an existing network share on a second server, called exampleserver:

Copy-Item -path C:\ProgramData\PROServer\dumps\* -destination \\exampleserver\network\share\ -force

Example b

This example uses a mapped network drive instead of a UNC file path:

New-PSDrive –Name “K” –PSProvider FileSystem –Root “\\exampleserver\network\share\” –Persist
Copy-Item -path C:\ProgramData\PROServer\dumps\* -destination K: -force
Do not use UNC file paths
The Code42 app does not support backups of UNC file paths (such as //SERVERNAME/Users/). If you include UNC paths in the backup file selection, the files appear to back up properly, but those files cannot be restored.

File selections must use an absolute path with a drive letter (such as C:/Users/) to make sure the files are backed up and restorable.

 

Step 2: Automate the script with task scheduler

Task Scheduler runs scheduled scripts or launches programs at specified times or intervals.

  1. Open Task Scheduler.
  2. Click Create Basic Task and enter the following parameters:
    • Create A Basic Task: Name for the task.
      (Optional) Enter a description of the task.
    • Trigger: Daily.
    • Daily: Select a time.
    • Action: Start a program.
    • Program/script: PowerShell.exe
    • Add arguments: Enter the following:
      -ExecutionPolicy ByPass -file C:\Location\of\your\powershell\script.ps1
  3. Click Finish to save the task.
Task notifications
Task Scheduler can also be used to automatically create email notifications you so that you know your database dump is being correctly copied to the new location. Creating these notifications is outside of the scope of the Code42 Customer Champions.

Step 3: Test the configuration

  1. Click Task Scheduler Library.
  2. Select the script you created.
  3. Click Run under Selected Item.
    Your script copies the file to the location you specified.
  4. Verify that the database dump has been copied to the new location.

External resources