How to backup databases
IONOS editorial team6 mins
The following situation may sound all too familiar for a lot of computer users: a valuable file has been accidentally deleted and there’s no backup copy. The file is now gone forever – how irritating. If a lot of data is concerned, it can be devastating, especially for businesses. Therefore it’s indispensable to back up business data as well as private data.
Why it’s important to back up your databases
As integral elements of any business software, database systems play a big role when it comes to web hosting. This is due to the functionality of the website (and the capability of the business) relying heavily on the stored data being available.
Database-accessing website projects use different scripting languages to gather all the necessary information in order to then correctly display the page. The IT infrastructure of a business usually draws its information from underlying databases. The data exchange also takes place in the reverse direction since users save data in the database. This means it’s not uncommon for these systems to contain personal and financial information or sensitive customer data. Therefore a database error, or even a loss of data, can have serious consequences. It could be that the website doesn’t display the content properly or is completely offline, apps don’t function anymore, and customer data is incomplete or, as is often the problem in the worst case scenario, non-existent. Not only does retrieving this data prove to be a significant hassle, it also agitates customers, which could lead to a loss of trust.
The cause for data loss is usually not an outsider attack, but rather a technical hardware failure or simply user error. Even the best security software won’t be of much help. Therefore a data backup is recommended so that data loss isn’t irreversible.
Options for backing up a database
In order to prevent data loss, you should create backup copies of the database on an external storage device. Having these copies allows you to recover the database beginning from the moment of the initial data backup. There are differences between online and offline backups: online backups are created without having to shut down the database. During the security process, the database records the undertaken changes in a separate area and adds them to the relevant file at the end of the process. If you shut it down during the backup, this is an offline backup. This data backup technique has the advantage of being relatively easy to perform, but it also means that the applications or websites won’t be available during the course of the backup process. If possible, it makes more sense to perform an offline backup during the night when there is less data traffic.
Regarding the breakdown of online and offline backups: they can be split into the following three types depending on the way the data is transferred:
- Full backup: Just as the name suggests, this type of data backup transfers all the stored data. This means that lots of room is required for frequent backups. In cases where one particular data set is lost and needs to be restored, only the corresponding full backup is needed.
- Differential backup: When it comes to a differential backup, a full backup first needs to be created. It then differs from a full backup in that only the files that have been changed or added since the last backup are saved. So unlike full backups, storage space is spared. With each differential backup modified and new files are copied until the next full backup. A successful full recovery of lost data therefore requires two things with this method: the last full backup, and the desired respective differential backup.
- Incremental backup: For an incremental backup, only the files that have been modified or added since the last backup are copied following a complete backup. Unlike the differential methods, an incremental backup refers to the previous backup (both full backup and incremental backup). Files are only saved once, which frees up some storage space. In order to recover the desired files, all the backups are needed; from most recent full backup to the desired state.
As you can see there are a few options for backing up database systems such as SQL databases or Microsoft Access. The most suitable backup method depends on the needs of the user or business. Skipping backups in order to save storage memory should never be done. External storage devices like hard drives should be kept in a separate area where they are protected from theft and fire damage. The saved data should additionally be encrypted so that if it’s stolen, it can’t be used by any third parties.
How to carry out a backup
You’ve decided on a backup solution and now the next step is to decide how to implement it. There are different possibilities and tools to back up databases like a SQL database. The following list explains some of the options:
- MySQLDump: If you have Shell access, you can work with MySQL’s integrated backup function and the ‘mysqldump’ command. Not all hosting providers allow access to this function that enables the fastest backup performance.
- phpMyAdmin: This administration platform for SQL databases offers users the possibility to easily export the desired database to their desired format e.g. SQL. It could, however, be that the PHP script gets terminated by the server if the database is too big. This kind of backup only works if the database is less than 2 MB.
- BigDump: The BigDump tool is the perfect addition to phpMyAdmin since it can import large backups. It doesn’t, however, offer its own backup function.
- HeidiSQL: The backup solution for Windows systems isn’t based on PHP and therefore doesn’t have a problem with large backups. The tool is very similar to phpMyAdmin, but doesn’t offer automated backups.
Database protection is always necessary
The saved files in databases often play a big part in the smooth execution of a business and the correct layout of websites. Web servers access the information in order to present the hosted website correctly. The functionality of the applications in the network is often directly linked to a database. Databases also create the storage location of sensitive data such as addresses, account numbers, and telephone numbers.
Since they play such an important role, database systems should definitely be protected by adequate security measures. Your data isn’t just threatened from outsider attacks, but also from internal problems such as hardware failure or user error. Regular backups prevent data loss and guarantee long-term data security.
