Dump and restore

An ASCII-based database dump is a safe scheme to transport a database to another platform or to migrate to an (incompatible) new version of MonetDB. This feature is standard available in mclient.

Linux platforms

Consider you have already installed the SQL tutorial database voc and wish to transport it to another machine. Then the following client command line option generates the dump file.

     shell> mclient -lsql --database=voc --dump >/tmp/voc.sql

You can inspect the file /tmp/voc.sql to confirm that indeed a readable database dump is available. Move this file over to the new machine. The  monetdb tool can be used to create the database. Once done, it suffices to feed the dump file to mclient to populate the database.

     shell> mclient -lsql --database=voc /tmp/voc.sql

Migration of a database from other system follows the same route, but be aware that SQL dialects often differ. A manual or scripted patch of a foreign SQL dump is often needed.

Fast backups on Linux

Aside from the ASCII dumps, one can also take the database server out of production using commands

shell> monetdb stop demo
shell> monetdb lock demo

After the database has been securely stopped, we can create a copy of the database directory in the dbfarm and put it aside in a safe place. Alternatively, incremental file-system dumps can be used to reduce the time and storage space for a recovery point. Finally, the database is released for production again

shell> monetdb release demo

Windows platforms

It is recommended to always dump the old database into ASCII before installing a new MonetDB release. Subsequently remove the dbfarm. After installation, the dump can be restored.

To dump the SQL database, start the MonetDB SQL Client program and type the command

\>...\databasedump.sql
\D
\>

The path after \>should be an absolute path name (i.e. start with a drive letter) and be in a save location. By default the database is located in  %APPDATA%\MonetDB5. After having made a database dump it can be removed. This folder is located inside the dbfarm\demofolder.

Restoring the SQL database can be done using the MonetDB SQL Client program with the following command

     \<...\databasedump.sql