changing of db

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

changing of db

Post by janrev15 » 2016-10-12 21:10

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,

User avatar
jimimaseye
Moderator
Moderator
Posts: 8070
Joined: 2011-09-08 17:48

Re: changing of db

Post by jimimaseye » 2016-10-12 22:50

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
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-13 12:27

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?

User avatar
jimimaseye
Moderator
Moderator
Posts: 8070
Joined: 2011-09-08 17:48

Re: changing of db

Post by jimimaseye » 2016-10-13 12:45

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.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-13 13:10

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 ?

User avatar
jimimaseye
Moderator
Moderator
Posts: 8070
Joined: 2011-09-08 17:48

Re: changing of db

Post by jimimaseye » 2016-10-13 14:44

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?
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-13 18:40

hi,

some are using pop and some using imap.

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-13 18:47

if i can get the size reduced to 16gb, i can just do a normal backup of domain / settings and email, am right?

User avatar
jimimaseye
Moderator
Moderator
Posts: 8070
Joined: 2011-09-08 17:48

Re: changing of db

Post by jimimaseye » 2016-10-13 20:54

In theory, yes. (You will need the recent version of HMS).
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
mattg
Moderator
Moderator
Posts: 19994
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: changing of db

Post by mattg » 2016-10-14 00:13

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
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-14 06:00

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.

User avatar
mattg
Moderator
Moderator
Posts: 19994
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: changing of db

Post by mattg » 2016-10-14 08:00

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
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-14 09:27

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?

User avatar
mattg
Moderator
Moderator
Posts: 19994
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: changing of db

Post by mattg » 2016-10-14 09:32

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
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

MarHMS
Normal user
Normal user
Posts: 103
Joined: 2015-12-11 17:10

Re: changing of db

Post by MarHMS » 2016-10-14 18:01

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?

janrev15
New user
New user
Posts: 16
Joined: 2015-03-04 03:22

Re: changing of db

Post by janrev15 » 2016-10-14 18:53

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..

User avatar
mattg
Moderator
Moderator
Posts: 19994
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: changing of db

Post by mattg » 2016-10-15 01:08

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
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

MarHMS
Normal user
Normal user
Posts: 103
Joined: 2015-12-11 17:10

Re: changing of db

Post by MarHMS » 2016-10-15 03:57

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

MarHMS
Normal user
Normal user
Posts: 103
Joined: 2015-12-11 17:10

Re: changing of db

Post by MarHMS » 2016-10-22 22:43

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?

User avatar
mattg
Moderator
Moderator
Posts: 19994
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: changing of db

Post by mattg » 2016-10-23 02:11

Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

MarHMS
Normal user
Normal user
Posts: 103
Joined: 2015-12-11 17:10

Re: changing of db

Post by MarHMS » 2016-10-23 02:21

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!

User avatar
jimimaseye
Moderator
Moderator
Posts: 8070
Joined: 2011-09-08 17:48

Re: changing of db

Post by jimimaseye » 2016-10-23 08:30

HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

MarHMS
Normal user
Normal user
Posts: 103
Joined: 2015-12-11 17:10

Re: changing of db

Post by MarHMS » 2016-10-23 16:45

Wow, seems like the perfect script.

User avatar
Dravion
Senior user
Senior user
Posts: 1411
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: changing of db

Post by Dravion » 2016-10-24 00:53

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.

Post Reply