Page 1 of 1

changing of db

Posted: 2016-10-12 21:10
by janrev15
hi,

i need to change my db type from compact to mysql (using dbsetup). however, i need to do a backup first of all my domain including their messages.

please let me know what external tool should i use to backup everything. the built-in backup asking to use some external tool if the size if more than 1gb.

thanks,

Re: changing of db

Posted: 2016-10-12 22:50
by jimimaseye
Upgrade hmailserver to the latest version.v The limit of 1gb on the internal backup has been raised to 16gb so you may then be able to do it.

Upgrade to latest
Internal Backup with ALL OPTIONS ticked
Db setup creating external db
Restore from backup

Re: changing of db

Posted: 2016-10-13 12:27
by janrev15
hi,
My email size is more than 16gb... is it possible to do a back-up of the settings, then manually copy the hmailserver/data then change dbsetup, restore the settings then paste the old data folder to the new data folder?

Re: changing of db

Posted: 2016-10-13 12:45
by jimimaseye
Im afraid you fell in to the trap of using internal database for non-permitted commercial use. (You shouldnt have been doing it as it's against the EULA, and the internal database has restrictions and one of them is the lack of 'administration' of it preventing you from doing administration and what you want to do - not good for a commercial environment).

The only thing you can do with that method is import the email messages with Data Directory Synchroniser (DDS) but then they will appear in the INBOX of each account. That means all email messages - sent, subfolders, the lot, will all be imported and appear in the INBOX. You will then have to drag out and re-drop the relevant emails back to the iriginal folders they came from (sent, sub folders etc).

So, if you wish to go with that and accept it, the process would be:

1, backup DOMAINS and SETTINGS with internal backup
2, dbsetup pointing to external DB (MySQL is my recommendation)
3, do restore from DOMAIN/SETTINGS backup
4, check/ensure data directory is pointing to the data directory which hgolds all the .EMLS
5, Run DDS (this will populate the inbox of each account with ALL emails belonging to that account).
6, review each account and drag and drop the relevant emails to their intended folders out of the Inbox.

Re: changing of db

Posted: 2016-10-13 13:10
by janrev15
if i do the DDS, it will re-populate all the user inboxes. this means, once the user open their email clients, all emails will be downloaded even those that has already been downloaded ?

Re: changing of db

Posted: 2016-10-13 14:44
by jimimaseye
Yes.

Are you saying that clients connect via POP3? And if so are they leaving emails on the server? or do they connect via IMAP?

Re: changing of db

Posted: 2016-10-13 18:40
by janrev15
hi,

some are using pop and some using imap.

Re: changing of db

Posted: 2016-10-13 18:47
by janrev15
if i can get the size reduced to 16gb, i can just do a normal backup of domain / settings and email, am right?

Re: changing of db

Posted: 2016-10-13 20:54
by jimimaseye
In theory, yes. (You will need the recent version of HMS).

Re: changing of db

Posted: 2016-10-14 00:13
by mattg
janrev15 wrote:hi,
My email size is more than 16gb... is it possible to do a back-up of the settings, then manually copy the hmailserver/data then change dbsetup, restore the settings then paste the old data folder to the new data folder?
Rather than change to MySQL, use the Microsoft SQL installer to upgrade to a better MS SQL database.

MS SQL Express is zero cost and you should be able to upgrade a MS SQL CE to a MS SQL Express fairly easily.
just stop hmailsevrer then run the MS SQL installer and select upgrade option, find the MSSQL CE instance and upgrade, then restart hMailsevrer when done

Make sure that you do some sort of backup first, and if possible trial upgrade on another machine. I think copying the entire hmailserver directory (including the database and data directory) to a new machine on same path, then installing hmailserver again over the top should make it functional on a new machine

Re: changing of db

Posted: 2016-10-14 06:00
by janrev15
yes, planning to install hmail into a spare pc, restore the settings then copy / paste the whole directory. hopefully it works.

the reason i want to mysql is because i want to have access on phpwebadmin.

Re: changing of db

Posted: 2016-10-14 08:00
by mattg
janrev15 wrote:the reason i want to mysql is because i want to have access on phpwebadmin.
That is available no matter the database.

The requirements for PHPWebAdmin is a webserver + PHP on the same machine as hMailserevr

Mind if you are using the MS SQL CE with 15 GB of mail, you should change that as the CE database is toy at best, and will let you down sooner rather than later

Re: changing of db

Posted: 2016-10-14 09:27
by janrev15
so even if i used the built in db, phpwebadmin should still work?

i was able to access phpwebadmin just now, but i am not able to login error : Cannot log in to the MySQL server.

can you point me to the correct guide on this?

Re: changing of db

Posted: 2016-10-14 09:32
by mattg
Are you sure it is the hmailserver PHPWebAdmin that you are trying to log into not some other tool like PHPMyAdmin which is a MySQL administration tool?

You copied the PHPWebAdmin folder from your hMailserver installation to your webserver

Re: changing of db

Posted: 2016-10-14 18:01
by MarHMS
mattg wrote:
janrev15 wrote:hi,
My email size is more than 16gb... is it possible to do a back-up of the settings, then manually copy the hmailserver/data then change dbsetup, restore the settings then paste the old data folder to the new data folder?
Rather than change to MySQL, use the Microsoft SQL installer to upgrade to a better MS SQL database.

MS SQL Express is zero cost and you should be able to upgrade a MS SQL CE to a MS SQL Express fairly easily.
just stop hmailsevrer then run the MS SQL installer and select upgrade option, find the MSSQL CE instance and upgrade, then restart hMailsevrer when done

Make sure that you do some sort of backup first, and if possible trial upgrade on another machine. I think copying the entire hmailserver directory (including the database and data directory) to a new machine on same path, then installing hmailserver again over the top should make it functional on a new machine
Mattg are you saying that I can upgrade the database that came with HMS to MS SQL Express?
Theoretically, what about happen to all the emails?

Re: changing of db

Posted: 2016-10-14 18:53
by janrev15
hi guys,

i was able to successfully configure my phpwebadmin.

temporarily, I will be keeping the current db setup. I will be asking all user to clear their old mails and once the total messages is less than 16gb, i'm going to upgrade the db.

thanks for all the help guys..

Re: changing of db

Posted: 2016-10-15 01:08
by mattg
MarHMS wrote:Mattg are you saying that I can upgrade the database that came with HMS to MS SQL Express?
Theoretically, what about happen to all the emails?
Exactly what I am saying

All mail and settings should not be affected

PLEASE TEST on a spare server if you can, and remember to BACKUP the entire hMailserver installation first

Re: changing of db

Posted: 2016-10-15 03:57
by MarHMS
mattg wrote:
MarHMS wrote:Mattg are you saying that I can upgrade the database that came with HMS to MS SQL Express?
Theoretically, what about happen to all the emails?
Exactly what I am saying

All mail and settings should not be affected

PLEASE TEST on a spare server if you can, and remember to BACKUP the entire hMailserver installation first
GREAT!

Our mail server is a VM, so I'll back-up that

Re: changing of db

Posted: 2016-10-22 22:43
by MarHMS
mattg wrote:
MarHMS wrote:Mattg are you saying that I can upgrade the database that came with HMS to MS SQL Express?
Theoretically, what about happen to all the emails?
Exactly what I am saying

All mail and settings should not be affected

PLEASE TEST on a spare server if you can, and remember to BACKUP the entire hMailserver installation first
Also, other than a back-up of the VM, or Windows Server Backup, is there another backup that you would suggest for the entire HMS installation?

Re: changing of db

Posted: 2016-10-23 02:11
by mattg

Re: changing of db

Posted: 2016-10-23 02:21
by MarHMS
mattg wrote:viewtopic.php?f=7&t=28025&p=174106&hili ... pt#p174124
viewtopic.php?f=9&t=30217#p188995


There are many ways, here is a couple of examples
Thanks a lot!

Re: changing of db

Posted: 2016-10-23 08:30
by jimimaseye

Re: changing of db

Posted: 2016-10-23 16:45
by MarHMS
Wow, seems like the perfect script.

Re: changing of db

Posted: 2016-10-24 00:53
by Dravion
There is also the possibilty to decrypt the mssql-ce HMS Setup generated password with your hMailServer Administrator Password and login via a free MS-SQL-CE Workbenchtool like CE-Query.
You need to execute the vbscript decryptpassword.vbs inside your hMailServer installation and type in your hMailServer Administrator password to get the password you need for CE-Query. Good luck.