Although there are few database administrators (DBAs) who do not believe in performing regular database backups, there are many opinions on how best to do so. Whichever approach you espouse, there are many good reasons to keep a copy of the database schema. In the event of data loss, you can restore the database structure from the schema, and then populate it with the latest data backup.
Some database vendors, such as MySQL, offer free utilities (i.e. mysqldump) for backing up the database structure on its own, while others require a specific administration tool to do so. If you're a Navicat user, there's no need for external tools. While data backups may be performed using the Backup Wizard, the schema can be copied using the Data Transfer Tool. In this blog, we'll learn how!
About the Data Transfer Tool
The Navicat Data Transfer Tool is a wizard-driven process that helps you to transfer tables, collections or other objects from one database/schema to another, or to a SQL/script file via a series of screens. The target database/schema can reside on the same server or on a remote server. In Navicat Premium, you can also transfer objects across server types, e.g. from MySQL to SQL Server. Only MongoDB does not support transferring to other server types, due to it being a NoSQL document database, as opposed to a traditional relational database.
You'll find the command to launch the Data Transfer Tool under Tools > Data Transfer in the Main Menu:
Source and Target Screen
The first screen is where you provide the Source Connection and Database/Schema and Target. The Target may be another connection or an SQL File that you can execute to rebuild the database schema later.
We'll specify the File option, and choose a location and name for the SQL/script file:
Options Screen
At the bottom of the Source and Target Screen, you'll see a button for choosing various options, including Table, Record, and Other options.
To backup the database structure only, we simply need to uncheck the Create records option as shown in the image below:
Database Objects Screen
On the Database Objects Screen, we can choose which tables, views, procedures/functions, and events to backup. If we do not select anything here, an empty database will be backed up, without any objects.
Summary Screen
The last screen in the process provides a summary of your choices along the way, so that you can verify them before clicking the Start button. Should you change your mind about anything, you can click the Back button to return to the relevant screen.
You'll also find a couple of common options there for quick selection:
Progress Screen
The Progress Screen displays every step of the backup along with a summary of transferred objects, errors, and the elapsed time:
Conclusion
Keeping a copy of the database schema is always a good idea so that, you can restore the database structure from the schema, and then populate it with the latest data backup, in the event of data loss. Although some database vendors, such as MySQL, offer free utilities (i.e. mysqldump) for backing up the database structure on its own, an even easier option is to use Navicat 's Data Transfer Tool. It can transfer tables, collections or other objects from one database/schema to another, or to a SQL/script file via a series of screens!
