Migrating the Database Backend

Migrating to a different database backend

How to move an existing installation from one of the four supported databases to another - in practice, off SQL Server Compact, which the installer still chooses by default and which Microsoft stopped maintaining a long time ago.

There is no migration tool, and that is not an omission. Backup and restore are already backend-agnostic, so the migration is three operations you already have: back up, repoint, restore. What was missing was this document.

Read Upgrading Guide instead if you are moving to a newer release of the server. That is a different operation and it keeps your backend.

The short version

1. Stop the service.
2. Take a backup with domains, settings and messages selected.
3. Create an empty database on the new backend and point hMailServer.ini at it.
4. Start the service and restore the backup.

Your mail files never move. Nothing is exported to an intermediate format you have to trust.

flowchart TD
    A["Hold inbound mail, or accept<br/>that anything arriving during the<br/>window is lost to the new database"] --> B["Stop the service"]
    B --> C["Start it again and take the backup:<br/>domains + settings + messages"]
    C --> D{"Is the mail store large?"}
    D -- yes --> E["Set BackupMessagesDBOnly=1<br/>BEFORE the backup, and leave it set<br/>for the restore. The .eml files stay<br/>where they are and are never touched"]
    D -- no --> F
    E --> F["Stop the service"]
    F --> G["Create an EMPTY database<br/>on the new backend"]
    G --> H["Edit the Database section<br/>of hMailServer.ini"]
    H --> I["Run DBSetupQuick.exe:<br/>it creates the schema at<br/>the current version"]
    I --> J["Start the service"]
    J --> K["LoadBackup, tick all three<br/>restore options, StartRestore"]
    K --> L["The server reinitializes itself"]
    L --> M["Verify: counts, IMAP folders,<br/>one whole message body,<br/>settings, send and receive,<br/>an empty ERROR log"]
    M -- "something is wrong" --> N["Put the old Database section back<br/>and restart. The old database was<br/>only ever READ"]
    M -- "all good" --> O["Keep the old database until<br/>you are certain, then retire it"]

Loading

Why this works

Worth understanding before you run it, because it tells you what can and cannot go wrong.

Nothing in the archive is tied to a database. Neither BackupExecuter nor BackupRestorer looks at the database type - there is no branch on it anywhere in either. The archive is XML plus, optionally, the message files.

The XML carries no identity values. Account::XMLStore writes Name, Password, MaxAccountSize and the rest, and no account id; XMLLoad sets none. On restore, SaveObject sees an object whose id is 0, inserts it, and lets the new database assign its own. Relationships are rebuilt through the object graph - accounts under their domain, folders under their account, messages under their folder - not by raw id. So the fact that MSSQL and PostgreSQL will hand out completely different numbers than SQL CE did is not something the restore has to cope with. It never sees the old ones.

  In the OLD database              In the archive                In the NEW database
  ------------------               --------------                -------------------
  hm_domains                       <Domain Name="example.com">   hm_domains
    domainid = 17          --->      no id at all         --->     domainid = 1
      |                                |                            |
  hm_accounts                        <Account Name="alice">      hm_accounts
    accountid = 412        --->        no id at all        --->     accountid = 1
    accountdomainid = 17               nesting IS the link         accountdomainid = 1
      |                                |                            |
  hm_imapfolders                     <Folder Name="INBOX">       hm_imapfolders
    folderid = 9001        --->        no id at all        --->     folderid = 1
      |                                |                            |
  hm_messages                        <Message                    hm_messages
    messageid = 88231                   Filename="{guid}.eml">     messageid = 1
    messagefilename ------------------  the SAME name ----------->  messagefilename
                                        |
                                        v
                            <DataFolder>\example.com\alice\AB\{AB34...}.eml
                              never moved, never renamed

The message store on disk is addressed by name, not by id. A message file lives at

  <DataFolder>\<domain>\<local part>\<xx>\{guid}.eml

where <xx> is the first two characters of the guid itself - the file name includes the braces, and the subfolder is taken from just after the opening one. A public-folder message lives at <DataFolder>\#Public\<xx>\{guid}.eml.

Those paths are built from the domain and account names and the file's own guid. Change every id in the database and every file is still exactly where the server will look for it.

That is the whole of it. The migration is possible because nothing identity-shaped crosses the archive boundary, which is also why it can cross a backend boundary.

Choosing a target

	MS SQL Server	PostgreSQL	MySQL / MariaDB	SQL Server Compact
[Database] Type	MSSQL	PostgreSQL	MYSQL	MSSQLCE
[Database] Internal	0	0	0	1
Runs where	Its own server, or LocalDB	Its own server	Its own server	In-process, one file
DDL in a transaction	Mostly	Yes - the only one of the four	No, commits as it goes	No, commits as it goes
So a failed schema step leaves	Normally nothing applied	Nothing applied	A partly built database	A partly built database
Isolation the server uses	READ UNCOMMITTED	its default	its default	its default
Transactions used at all	yes	yes	only if every table reports InnoDB at connect time	yes
Encryption in transit	ConnectionStringOptions=Encrypt=yes;TrustServerCertificate=no, appended verbatim to the connection string	PostgreSQLSslMode=verify-full with PostgreSQLSslRootCert=<CA file>; without them libpq encrypts when offered and verifies nothing	Required by the bundled client by default; a server with none needs AllowUnencryptedConnection=1	n/a - no network
Choose it when	You already run SQL Server, or you want the strongest transaction support of the four	You want the best-behaved backend in this codebase	You already run it	Only if you are migrating to a test rig

SQL Server Compact is the default and it is the one real dependency liability in this tree; see the SQL Server Compact row in Roadmap.md for the three defects that are specific to it.

The schema is created for you either way - you do not need to run the SQL scripts by hand.

Before you start

Take a filesystem-level backup of the whole installation as well. Not because the restore is unreliable, but because it is the only thing that will get you back if you discover a problem after the old database has been retired. See Rolling back.

Check that your messages are all inside the data folder. Backup refuses to run if any message row points at a file outside DataFolder - it fails with "All messages are not located in the data folder". This only happens on installations where messages were imported with absolute paths. If it fires, fix those rows before going any further; the migration is not the place to discover it.

Do not restore an archive taken by a newer hMailServer. The restore refuses it, by design and with no override:

This backup was taken by hMailServer X, which is newer than the Y running here. Restoring it would silently discard anything the newer version stores and this one does not. Upgrade this server to at least version X and restore again. Nothing has been changed on this server.

Migrate on one version, then upgrade - not the other way round.

Note your current settings. Open hMailServer.ini (normally C:\Program Files\hMailServer\Bin\hMailServer.INI) and keep a copy. The [Database] section is the part you are about to change:

[Database]
Type=MSSQLCE
Server=
Database=hMailServer
Username=
Password=
Port=0
Internal=1

Key	Meaning
Type	MSSQL, MYSQL, PostgreSQL or MSSQLCE, matched case-insensitively
Server	Host name or instance. Empty for the internal engine
ServerFailoverPartner	MS SQL mirroring partner, when you have one
Database	Database name
Username / Password	The login. See the DPAPI note below
PasswordEncryption	Written by the server; says how Password is protected
Port	0 uses the backend's default
Internal	1 only for the embedded SQL CE engine
NumberOfConnections	Pool size; default 5
ConnectionAttempts / ConnectionAttemptsDelay	Retries at startup; defaults 6 and 5 seconds

The password in this file may be machine-bound. With [Settings] ProtectStoredSecretsWithDPAPI=1, which is the default, the server writes Password through machine-scoped DPAPI (CRYPTPROTECT_LOCAL_MACHINE) and records PasswordEncryption accordingly. It cannot be decrypted on another machine. If you are also moving hosts, set the new backend's password on the new machine - through DBSetup.exe, or by writing the plain value and letting the server re-protect it - rather than copying the encrypted one across.

The administrator password lives in this file too, not in the database, so it carries over untouched.

The procedure

1. Stop the service

Stop-Service hMailServer

Wait for it to actually stop before continuing. A backup taken while mail is being delivered is a backup of a moving target.

Restart it for the backup itself - the backup runs through the running server - but stop accepting new mail first if you can, either by stopping the SMTP TCP/IP port in the administration tool or by holding the traffic upstream. Anything that arrives after the backup and before the cutover is delivered into the old database and will not be in the new one.

2. Take the backup

In the Control Panel, under Maintenance → Backup & restore, tick all three of Back up domains, Back up messages and Back up settings, set a destination with room for it, and click Start backup now. Or from a script:

$app = New-Object -ComObject hMailServer.Application
$app.Authenticate("Administrator", "<password>")
$b = $app.Settings.Backup
$b.BackupDomains  = $true
$b.BackupSettings = $true
$b.BackupMessages = $true
$b.Destination    = "D:\hmail-migration"
$app.BackupManager.StartBackup()

The archive lands in the destination folder as HMBackup <local time>.7z. That one file is the whole backup - the hMailServerBackup.xml index you may see appear next to it during the run is written into the archive and then deleted. $app.Settings.Backup.LogFile names the backup log, and the run verifies the archive before it counts as a backup at all: an archive that fails verification is deleted, or left in place with a log line saying it does not count.

All three of domains, settings and messages are required. The restore refuses combinations that would delete something and put nothing back, and it says so before it changes anything:

If you ask for	And the archive has	The restore says
domains	no domains	"Restore refused: this backup does not contain any domains, so restoring domains from it would delete every domain, account and alias on this server and put nothing back."
settings	no settings	"...would delete the SSL certificates, TCP/IP ports, IP ranges, global rules, blocked attachments and anti-spam lists and put nothing back."
messages	no messages	"Restore refused: this backup does not contain any messages."
messages	without also asking for domains	"Restore refused: messages can only be restored together with the domains they belong to."

Every one of those ends "Nothing has been changed on this server", and that is literally true: the checks run before the first deletion.

If your mail store is large, use the database-only mode. Set

[Settings]
BackupMessagesDBOnly=1

in hMailServer.ini before both the backup and the restore. This stores and restores the message rows - which is what you are actually migrating - while leaving the .eml files alone on disk.

This is not only a speed optimisation, and on a backend migration it is close to mandatory. A normal restore empties the live data directory and copies the staged message store into it (BackupExecuter::RestoreDataDirectory_ deletes every file and directory under DataFolder, then copies). With BackupMessagesDBOnly=1 there is no staged message store, that step is skipped entirely, and your .eml files are never touched - which is exactly what you want when the files are not moving anywhere. On an installation with a few hundred gigabytes of mail this is the difference between minutes and most of a day, and between "the mail was never at risk" and "the mail existed only in a staging directory for an hour".

The setting has to be identical for the backup and the restore. Remove it afterwards so your ordinary scheduled backups go back to including the files.

3. Create the new database

Create an empty database on the target server, and a login with rights to create tables in it. Do not create any tables - the next step does that.

Then edit hMailServer.ini to point at it:

[Database]
Type=MSSQL
Server=sql01.example.com
Database=hMailServer
Username=hmailserver
Password=<password>
Port=0
Internal=0

Now create the schema by running the database setup tool from the installation's Bin folder:

& "C:\Program Files\hMailServer\Bin\DBSetupQuick.exe"
if ($LASTEXITCODE -ne 0) { throw "Database setup failed with exit code $LASTEXITCODE" }

It reads the connection details you just wrote, sees no database, and creates one at the current schema version. It returns a non-zero exit code if it fails - that is what the installer checks - so it is safe to run from a script.

DBSetup.exe, in the same folder, is the interactive wizard and does the same job with prompts. Use it if you would rather enter the connection details in a dialog than edit the ini file by hand; it writes the same [Database] section, and it is the right tool when the password has to be DPAPI-protected on this machine.

4. Restore

Start the service and restore the archive:

Start-Service hMailServer

$app = New-Object -ComObject hMailServer.Application
$app.Authenticate("Administrator", "<password>")
$backup = $app.BackupManager.LoadBackup("D:\hmail-migration\HMBackup 2026-08-13 220511.7z")

# Confirm the archive really holds all three before asking for them
'{0} {1} {2}' -f $backup.ContainsDomains, $backup.ContainsSettings, $backup.ContainsMessages

$backup.RestoreDomains  = $true
$backup.RestoreMessages = $true
$backup.RestoreSettings = $true
$backup.StartRestore()

What that does, in order, from BackupExecuter::StartRestore:

sequenceDiagram
    participant You
    participant R as BackupRestorer
    participant E as BackupExecuter
    participant DB as The NEW database
    participant FS as The data directory
    You->>R: StartRestore
    R->>R: Extract and read the index
    R->>R: Refuse an archive from a newer version
    R->>R: Refuse an option the archive cannot satisfy
    R->>FS: Stage the message store into a temp directory - skipped entirely under BackupMessagesDBOnly
    Note over R,FS: Everything above happens BEFORE anything is deleted
    R-->>E: Accepted
    E->>DB: Delete every domain
    E->>DB: Delete the public folders
    E->>FS: Empty the data directory and copy the staged store in - skipped under BackupMessagesDBOnly
    E->>DB: Load domains, accounts, folders and messages from the XML
    E->>DB: Load settings LAST, because they refer to the domains
    E->>E: Reinitialize the server

Loading

The server restarts itself at the end of a restore. That is expected.

If the copy in the middle fails, the staged directory is kept rather than cleaned up, and the failure message names it - at that moment it is the only copy of the messages that exists. Do not delete it until they are back.

Verifying it worked

Do all of these. The failure mode worth catching is a partial restore, which looks like a working server to anyone who only checks that it starts.

• Counts match. Domains, accounts, aliases and distribution lists, compared against what you wrote down before you started:

  foreach ($d in $app.Domains) {
     '{0,-30} {1,4} accounts {2,4} aliases {3,4} lists' -f `
        $d.Name, $d.Accounts.Count, $d.Aliases.Count, $d.DistributionLists.Count
  }

  example.com                      42 accounts    7 aliases    3 lists
  example.net                       6 accounts    0 aliases    0 lists
  

• Mail is visible. Log in over IMAP as a real account and check that the folder list and the message counts are what they were. This is the one that proves the message rows and the files found each other again.

• A message opens. Fetch a whole message body, not just a header - that proves the row's file path resolves on disk under the new ids.

• Settings survived. SSL certificates, TCP/IP ports, IP ranges, routes, global rules and the anti-spam lists. These are the part of the backup that is easiest not to notice is missing.

• Send and receive. One message in, one message out.

• The error log is empty. hmailserver_ERROR_<date>.log in the log folder. Anything in it that appeared during the restore is worth reading before you retire the old database.

Rolling back

Until you delete the old database, rollback is exact and takes a minute: stop the service, put the original [Database] section back in hMailServer.ini, start the service. The old database is untouched by everything above - the migration only ever read from it.

Keep it until you are satisfied, and remember that any mail that arrived after the cutover exists only in the new database. That is the one thing rolling back loses, and it is why the cutover is worth doing during a quiet window with inbound traffic held.

There is no downgrade path for the schema, but that does not apply here: you are not changing schema version, only which server holds it.

Known sharp edges

Any mail delivered between the backup and the cutover is lost to the new database. Nothing in the process merges the two. Hold inbound traffic, or accept the gap knowingly.

Without BackupMessagesDBOnly=1, the restore empties your data directory. It deletes every file and directory under DataFolder and copies the staged store in its place. On a backend migration - where the files are not moving anywhere - that is a large, slow, entirely unnecessary risk. Set the flag.

Ids change, and anything of yours that stored one will break. Nothing inside the server depends on id continuity, which is the entire basis of this document - but if you have external scripts, reporting or an integration that recorded hm_accounts.accountid or hm_domains.domainid, those values will not mean the same thing afterwards. Key on the address or the domain name instead.

The database password does not survive being copied to another machine when DPAPI protection is on, which it is by default. Set it on the machine that will use it.

The SQL log device's table is not part of the backup. If your logging device is SQL (Logging.Device = hLogDeviceSQL, the Control Panel's Logging page), historical log rows stay in the old database. Export them separately if you need them; they are ordinary rows in hm_log.

MySQL and SQL Server Compact commit DDL as it executes. If the schema creation in step 3 fails halfway on those two, the database is left partly built rather than rolled back. Drop it and start step 3 again rather than re-running the tool over the remains.

MySQL needs InnoDB throughout. The server only issues transactions if every table reports InnoDB at connect time, and silently does not if any table does not. Check after the schema is created, not after the restore.

What this does not cover

Moving the data folder to different storage, which is a separate operation and is not coupled to the backend at all - the path is [Directories] DataFolder, and the only requirement is that the service account can reach it.

Moving to a new machine, which is covered in Upgrading Guide under Special cases, and which is where the DPAPI note above matters most.