Let’s Work Together

Partner with you to deliver responsive and cost-effective IT & Support solutions
Partner with you
Attitude to Serve
These are the reasons why AionSolution is your vendor when you are seeking someone to support your IT in your office.
+852 2636 6177

Adding per user backup and restore

Since I got a rousing response of 1 inquiry to my post about adding per-user backup to this script, I thought I’d just go ahead and post it.

Here are the changes. In the main backup script, add the lines with the + in them (leaving out the +, of course).

--- OLD/zmbac.0.8.sh.orig       2010-02-14 20:19:22.000000000 -0700
+++ zmbac.sh    2010-05-16 14:43:40.000000000 -0600
@@ -61,25 +61,26 @@
 #--- Directories ---#
 # Please add the trailing "/" to directories!
 ZM_HOME=/opt/zimbra/   # where zimbra lives
 SYNC_DIR=/backup/sync/ # intermediate dir for hot/cold syncs. must have at least as much free space as ZM_HOME consumes
 ARCHIVEDIR=/backup/current/    # where to store final backups
+ SCRIPT_DIR=/usr/local/sbin/  # where zmbac.sh and zmDBbac.sh reside

@@ -129,6 +130,9 @@
 #Hack to start Stats, even run zmlogprocess if needed
 STATHACK="yes"                 # valid answers are "yes" or "no"

+#--- Dump Databases? ---#
+# Adjust zmDBbac.sh script to backup up per-user or raw dbs, or both.
+DUMP_DBS="yes"                 # valid answers are "yes" or "no"

 ## ~~~~~!!!! SCRIPT RUNTIME !!!!!~~~~~ ##
 # Best you don't change anything from here on, 

@@ -695,6 +764,28 @@
                exit 1
+       # Dump DB's, if enabled
+       if [ $DUMP_DBS = "yes" ] ; then 
+           echo
+           # Create db_dumps directory
+           if [ ! -d $SYNC_DIR"db_dumps" ] ; then
+              echo "Creating db_dumps directory..."
+              chmod 755 $SYNC_DIR
+              mkdir $SYNC_DIR"db_dumps"
+              chown $ZM_USER $SYNC_DIR"db_dumps"
+           fi
+           # Run script to dump dbs
+           echo "Backing up Zimbra DBs..."
+           su - $ZM_USER -c $SCRIPT_DIR"zmDBbac.sh $SYNC_DIR"
+           if [ "$?" -ne "0" ] ; then
+             echo "There was an error running DB backup script! Aborting DB backup. Continuing main backup."
+           fi
+        # Check that /opt/zimbra/db_dumps exists, or create empty /opt/zimbra/db_dumps directory tree if necessary
+           if [ ! -d $ZM_HOME"db_dumps" ] && [ -d $SYNC_DIR"db_dumps" ] ; then
+             $RSYNC_BIN -a -f"+ */" -f"- *" $SYNC_DIR"db_dumps" $ZM_HOME
+           fi
+       fi
+       echo
     # Starting the Zimbra server again
    # Reinstate zimbra user's crontab
        echo "Reinstating Zimbra's crontab..."

These changes set the script directory, enable DB backups, then call the DB backup script.

You can download the db backup and restore scripts at: zmDBbac.zip


1. Put the zmDBbac.sh and restore_user.sh scripts in the same directory as zmbac.sh
2. Make them executable:

chmod 755 zmDBbac.sh
chmod 755 restore_user.sh

3. Make any changes to settings in the zmDBbac.sh script. These are

  • Create processed DB dump, with added SQL commands to ease restore. (default)
  • Create RAW db dump, with no processing. (optional. Since you can convert a processed db dump back to raw using the ‘makeraw’ option in this script, raw dumps should not be needed. Raw db’s are handy for individual folder restore.)
  • Specify a domain DN to back up. (optional)
  • Specify a COS to back up (optional)

4. The zmDBbac.sh script will create several folders. It creates a db_dumps directory in your SYNC_DIR directory, with ldap, raw, and mailboxes subdirectories. It creates these same folders in /opt/zimbra. The directories in /opt/zimbra remain empty always, and are merely used by rsync to empty the directories in SYNC_DIR/db_dumps every night.

In your nightly email from the script, it will show the db dump results:

Wed May 5 03:00:02 MDT 2010
Performing DIFF backup
Doing a fast cold sync...

Backing up Zimbra DBs...
Making sure all Zimbra services are stopped
Starting just LDAP and MySQL for db dump...
Starting LDAP...
Started slapd: pid 27636
Starting MySQL...
Starting mysqld...done.
Trying to connect to MySQL...

Dumping and processing per-user dbs and exporting per-user LDAP entries...
E-Mail                            User ID       Database       LDAP        zimbraId
------                            -------       ---------      ------      --------
(zimbra)                          --            zimbra         --          60fa3489-9854-22d9-8f21-000a67a98ef2
Alise.Amadeo@somewhere.com        11            mboxgroup11    11.ldif     71c8a11d-02ec-c29c-9dd9-b49c80600ca5
Bernetta.Verdin@somewhere.com     2             mboxgroup2     2.ldif      2705a240-07fe-cccc-acfd-cc26c489c293
Caitlin.Kellman@somewhere.com     45            mboxgroup45    45.ldif     67a60dca-07ad-c66a-9d6f-9f7832d29440
Cassey.Antczak@somewhere.com      42            mboxgroup42    42.ldif     fc4fc12d-b16e-c91d-ae3d-b6b86a72826c
Cecile.Lorenz@somewhere.com       12            mboxgroup12    12.ldif     6d0ec99f-c70e-caa8-afaa-e48ca98cae59
Chau.Gable@somewhere.com          43            mboxgroup43    43.ldif     8dfe7a6f-0b9f-cbff-9b8c-e1f5b860c3b8
Dannie.Weatherman@somewhere.com   103           mboxgroup3     103.ldif    f6ddd11c-ce1a-c6b9-a3ad-164762d5c8d3
Total users = 88
Stopping LDAP and MySQL...
Stopping mysqld... done.
Killing slapd with pid 27636 done.

Reinstating Zimbra's crontab...
Starting Zimbra...
Host mail.somewhere.com
    Starting ldap...Done.
    Starting logger...Done.
    Starting mailbox...Done.
    Starting antispam...Done.
    Starting antivirus...Done.
    Starting snmp...Done.
    Starting spell...Done.
    Starting mta...Done.
    Starting stats...Done.
Service down time was - Hr:0 Min:7 Sec:39
diff Zimbra Backup ended at: 03:33
Backup took Hr:0 Min:33 Sec:43 to complete

When users are added, only their LDAP entry is created. Their database entry is not created until the account is accessed. There are two ways of dealing with this:

  • If AUTCREATE_DB is set to “yes” (default), the script starts the zmmailboxd service and just queries the user’s mailbox size. This prompts Zimbra to create the user’s database.
  • If AUTCREATE_DB is set to “no”, their entry in the nightly email will look like:
    Some.User@somewhere.com             --          No_DB_Exists!*   --            46fc9b59-74aa-4d31-bfc3-7f66145bf285

The user’s LDAP entry will also not get backed up until their database gets created.
A manual way of creating the user’s database entry is just to Edit their account from the Admin interface. You don’t have to make any changes, just open the Edit window.


You can find the user’s id from the nightly email, or in /mysyncdir/db_dumps/namelist.txt
Restoring user “12” would go like:

Autmatic method, using restore_user.sh script:
1. Be sure you have a good backup.
2. Delete the user, if necessary. You basically always want to delete the user first, if they still exist, for a full user restore. This is to avoid duplicate database entries when you restore their DB data.
3. Restore the user’s message store to /opt/zimbra/store/#/12, with “#” being the volume directory (initial volume default is “0”). There may be several volume directories, with a portion of the user’s store in each. You need to restore the user’s store directory to each volume directory.
4. Restore the user’s index to /opt/zimbra/index/#/12, with “#” being the volume directory, in the same manner as the message store.
5. Place the user’s .sql and .ldif backup files in the same directory. If the user’s database does not exist, also place skeleton.sql into the same directory. The skeleton file is used to create basic table structure before importing the user’s SQL data.
6. Run restore_user.sh script, as Zimbra user, giving it the location of the of the backup files and the user’s mailbox id, e.g.

/usr/local/sbin/restore_user.sh /tmp/restore 12

The script will ask you whether you want to create the database (if it doesn’t already exist), whether to change the email address or mail server host name, and whether to import SQL and LDAP data. It also prompts a Zimbra shutdown: importing data is most safely done when Zimbra services (except mysql and openldap) are not running.

Manual method:
You can manually import data instead of using the restore_user.sh script.
1. Be sure you have a good backup
2. Restore user’s store and index, per above.
3. Best practice is to shut down Zimbra, and start only its MySQL and LDAP services.
4. As Zimbra user, restore user’s db and zimbra db records:

mysql < /mysyncdir/db_dumps/mailboxes/12.sql

Note: the user’s database must already exist, or you’ll have to create it manually first, including all table structure. This can be done by adding SQL statements above and below the USE mboxgroup* line:

USE mboxgroup12
SOURCE /tmp/restore/skeleton.sql

Make the changes before importing the .sql file.
5. Restore the user’s LDAP entry, if necessary. As Zimbra user (changing paths and host as necessary):

source ~/bin/zmshutil ; zmsetvars
/opt/zimbra/openldap/bin/ldapadd -D "uid=zimbra,cn=admins,cn=zimbra" -f /mysyncdir/db_dumps/mailboxes/12.ldif -w "$zimbra_ldap_password" -x -H ldap://myzimbraserver.somewhere.com

Note: If you want to restore using a different email address, or to a differently-named mailserver, you will need to manually edit the ldif and sql files first.

As previously stated:

  • This script must do the db dumps cold, which adds a few, to several, minutes of downtime during backup.
  • This script also does the LDAP user backup cold. Cold LDAP backups add a minute or two to server downtime for small servers. For larger servers, the LDAP backup could be moved to the main backup script, and be done hot.
  • I have done several successful test restores to a secondary server, but have not thoroughly tested every aspect of it. It would be wise to try test restores on a non-production server.

If you only want to restore a single folder, you can use the DB backup like this:
Restore a single folder