How to get contact adress book from rouncube database

[Chris.G]

Hi Guys

I moved one domain from one plesk to another server and removed the domain from old account. Now client is complaining that plesk migration didnt migrated the signatures and contact address book of the email accounts. 

Any one got any idea how to get this fixed in future migration and how to fix the issue for the current client email accounts ?

[Techyllinux]

Hi

It seems the plesk backup / migration will not be doing those which are added in the rouncube. If it was a server wide migration, you can simply restore the database for roundcube.  If you are moving only one account, It is better to ask the clients to take proper export format of the address book so that they can restore those in the new server.

https://talk.plesk.com/threads/problem-n...be.303785/

================
Roundcube Support
In 11.5, we introduce a new webmail component – Roundcube, shipped with two plugins: Mark as spam and Password change. When you upgrade to Panel 11.5, Roundcube does not replace other webmail software which is already installed with your Panel. Note that Roundcube requires PHP 5.2 or later.
Important: Roundcube’s internal database is not included in Panel backups. This means that customers’ settings for Roundcube, such as calendars or contacts, are not backed up.
==================

[sumesh]

Hi Chris,

If you have domain in old server (not yet removed then you can use the below commands to retrieve the email accounts in the csv format. 

I assume you have 3 email accounts under your domain like 

email.address1@freedomain.com
email.address2@freedomain.com
email.address3@freedomain.com

First you need to create a csv file using the below command 

===
echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address1.contacts.csv

echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address2.contacts.csv

echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address3.contacts.csv
===

Assume the db name of the roundcube is roundcubemail (if you have removed the domain in old server, then better restore the database to a new name like rouundcube_backup and we can generate the csv files from that backup as well) 

Now we need the id of each email account , please use the below commands

mysql > use roundcubemail;

mysql > select user_id from users where username='email.address1@freedomain.com';
+---------+
| user_id |
+---------+
|     335 |
+---------+
1 row in set (0.00 sec)

mysql > select user_id from users where username='email.address2@freedomain.com';
+---------+
| user_id |
+---------+
|     483 |
+---------+
1 row in set (0.00 sec)

mysql > select user_id from users where username='email.address3@freedomain.com';
+---------+
| user_id |
+---------+
|     583 |
+---------+
1 row in set (0.00 sec)

Once the Id's are ready, you can simply generate the email accounts address book using the below commands

# mysql  -uadmin -p`cat /etc/psa/.psa.shadow` roundcubemail   -qbse "select name,email from contacts where user_id=335;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address1.contacts.csv

# mysql  -uadmin -p`cat /etc/psa/.psa.shadow` roundcubemail   -qbse "select name,email from contacts where user_id=483;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address2.contacts.csv

# mysql  -uadmin -p`cat /etc/psa/.psa.shadow` roundcubemail   -qbse "select name,email from contacts where user_id=583;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address3.contacts.csv

You have the required csv format backup and can use to import the address book in the respective email accounts via roundcube

Hope this helps

[Edward]

Good one Sumesh. I tried this and it is working !!