1.70.2 Database Dumps

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.

Unix

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 compact database dump is available. Move this file over to the new machine. The tool monetdb 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.

Windows

It is recommended to dump the old database and remove it before installing the new version. After installation, the dump can be restored.

In order to dump the SQL database, start the MonetDB SQL Client program and type the commands

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

In order to dump the XQuery database, start the server and then connect to the administrative console http://127.0.0.1:50001/admin and use that to dump the database.

By default the database is located in %APPDATA%\MonetDB4 for the MonetDB4/XQuery server and %APPDATA%\MonetDB5 for the MonetDB5/SQL server. After having made a database dump, the latter folder can be removed, but the database dump for MonetDB4/XQuery is made inside the database folder, so remove anything except the folder backup and its contents. This folder is located inside the dbfarm\demo folder.

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

     \<...\databasedump.sql

Restoring the XQuery database can be done from the administrative console.