8.1 The need for a database backup

You have learned how to create database schemas, tables, view them. Also fill these tables with data and change them. Now you need to learn the most important thing - to make backups.

The database is very easy to break. The fact is that the database is usually in a state of constant change: something is constantly saved and added to it.

Imagine that you have a tab open in your browser and you do not want the information in it to be lost. Agree that if you just decide never to close it, then this will not be the best option. After all, the browser can freeze, it can be closed by accident, you can lose the light or burn your computer. Windows can install another update, but you never know what.

So, your database is the same tab. And it's only a matter of time when something goes wrong during the next write to the disk. Therefore, you definitely need to learn how to make backups.

Making backups is like brushing your teeth, normal companies make a backup of the contents of each database every night. And since hard drives can also fail and there are fires in data centers, backups are usually stored in 2-3 copies in data centers in different parts of the world.

There are several approaches to creating a backup.

Backup files. Since all database data is stored on disk as a set of files , the easiest way is to simply copy these files somewhere. Or first archive it into one archive, and then copy it somewhere.

Java university

This is the fastest way to make a backup, but it requires that the files do not change while they are being copied/archived. The database must be frozen while the copy is being created, otherwise the copy will be crooked. After all, there may be a situation that some of the new data is recorded in the database, and some is not yet.

Backup in the form of SQL queries . As you already know, adding and changing data in the SQL server database is always represented as SQL queries. So you can ask the SQL server to save the contents of a particular table or tables to a file as SQL queries.

This method is slower than the previous one, but it can be used to transfer data between SQL servers from different manufacturers.

Also, not all data always needs to be saved. Often you have a bunch of service or obsolete information in your tables and you can tell the SQL server not to save it.

Also, for tables that store some events with reference to time, you can simply select such events for the last day and store only them.

Various utilities . Companies are willing to pay a lot of money to get a guarantee that their data will remain intact and safe. Therefore, various IT companies began to offer solutions for all occasions. For example, there are programs that can make backup databases in the form of files and at the same time do not require that these files do not change.

For example, you can install special drivers in the operating system and keep track of when, what and where has changed.

8.2 Backing up the database

Workbench is excellent at exporting and importing data from the databases it works with. To do this, you just need to press the menu item: Server-> Data Export. And you will see something like this:

Workbench Buckup Data Export

Now let's add some explanation:

Workbench Buckup Data Export 1

The order is this:

  1. First, select the schema, or schemas, that will be exported.
  2. Then on the right we specify the tables for backup.
  3. Next, we must choose what exactly we will export:
    • data only;
    • structure only (tables will be, but empty);
    • data and structure.
  4. Choose how to save the data:
    • one file for each table;
    • one file for all tables.
  5. You can also add the schema creation code to the top of the file. This is useful when transferring data between different DBMS.

8.3 Backing up the table schema

Let's back up the schemas only, without the actual data.

I selected Dump Structure Only and clicked Start Export.

Workbench Buckup Data Export 3

And this is what I found in the file that Workbench made.

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client 	= @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  `created_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

That's right, this is the schema of our tables, if I may say so about a single table.

8.4 Backing up data

Now let's backup only the data, but without the schema structure.

Let's select Dump Data Only and see what they give us:

Workbench Buckup Data Export Dump Data Only

And this is what I found in the file that Workbench made.

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES
 	(1,'Ivanov Ivan',40,'2022-05-11'),
 	(2,'Petrov Nikola',1,'2021-05-01'),
 	(3,'Sidroov Vitaly',8,'2022-05-12');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Yes, very much like the truth. We also see a special code here that locks the user table. This is done so that you can simply execute this SQL script: it contains all the instructions that will allow any SQL server to correctly restore the data.

8.5 Deploying a backup

And finally, the last thing is restoring the database from a backup. This Action is very simple but useful.

Let's say I want all my students to have a specific database so that you can learn how to write queries on it. Then I'll just give you a link to the file that you deploy locally and that's it.

And even if you accidentally delete some data, tables, or even the entire schema, it can always be restored from a backup.

In order to import data, you need to click on the item in the menu Server-> Data Import. Then you will see this picture:

Workbench Buckup import data

As in the case of export, you are first prompted to select a data source: a group of files or all data in one file.

Then you need to select the scheme into which the specified data will be uploaded. You can select an existing scheme or create a new one.

After importing, you need to click the update button 🔄 to see the imported table/schema.

Important! If your backup contains not only data, but also a description of the table structure, then the tables will be recreated (if there are already any in the target schema).