Making a Full Database Backup
You should regularly backup your database. Data loss can happen for a variety of reasons – hardware malfunctions, viruses, power outages, power surges, human error etc. One of the most time-consuming parts of operating a database is entering information. If your database gets corrupted, manually restoring it can be a lot of work. However, if you have a backup, you can often automatically restore in a short amount of time.
When you are making a database backup (sometimes also referred to as a data dump), there are various components you need to be aware of: The actual data itself, associated transaction and log files and externally stored data. The actual data is self explanatory. Associated transaction and log files keep track of all the operations that happen to your database including reads, writes and deletes. Logs can also include the user account that made the change, and the associated timestamp. Externally stored data can include images and documents that are too large to store directly in the database (instead a pointer to these files is maintained in the database).
Not all of these components will apply to all database systems. For example, larger database systems like SQL, MySQL, MongoDB, MariaDB, and PostgreSQL will have a concept of transactions. But smaller desktop systems may not keep track of individual transactions, or actions that alter the data.
There are several ways you can create a backup – manually copy data and files yourself, use free open-source tools, use tools integrated into your database system, or purchase commercial backup software.
Three Types of Backups
There are 3 types of backup approaches that can be used to make a copy of your database. The selection of an approach may be dictated by your database system:
Full backup – A full database backup makes a complete copy of your data. This type of backup is the easiest to create, and making a full recovery of your data is relatively easy. However, a full recovery model is also the most time consuming and takes the most space.
Incremental Backup – An incremental database backup just saves changes since the last full backup. This approach works well if your database system automatically supports incremental backups. Creating manual incremental backups is very complicated and not recommended.
Differential Backup – Very similar to an incremental database Backup expect that a new copy is made of all previous backup files. Again, this approach should only be used if your database system natively supports is.
Backup Space Constraints
Database backups can take a lot of space. If you don’t have much storage space available, there are some strategies you can use to manage your storage space. First, if you are saving to a hard drive, flash drive or cloud service, instead of saving every backup permanently, you can choose to keep only the last few backups. Second you can save your backups to physical media like blue ray discs, and just purchase new discs as needed. Last, if your database system supports them, you can look at incremental or differential backups, which only save changes, not the entire database.
Backup Storage Media Longevity
Backup media is not all created equal. Here is list of commonly used backup media, along with expected lifespans:
CD and DVD – optical media with shelf life of up to 10 years.
Blue-Ray – advertised as lasting longer than CDs and DVDs but are still optical media, and should not be relied on for more then 10 years.
M-Disc – also optical media but designed to be a long-term storage solution (some manufacturers claim that an M-Disc will last up to 1000 years).
USB Flash Storage – these are drives that plug into your USB ports. These devices degrade the more you use them. If you just store a single backup on a flash drive and don’t touch it again, it could last indefinitely. However, if you repeatably use the same flash drive for your backups, you can expect the media to last a maximum of 10 years.
Magnetic hard drive – this is the type of hardware that is found in most computers and external hard drives. Hard drive longevity is very difficult to predict. It could be anywhere from 2 years to 10+ years.
SSD Hard Drive – these drives use solid state technology instead of magnetic. They are more expensive than magnetic hard drives, and can be expected to last 10 years.
Another option for backup storage is to use a cloud-based service. If you use a reputable cloud provider (like Microsoft Azure, Dropbox or Amazon Web Services), they will automatically move your backups to newer media as older media starts to degrade. However, you will have to pay monthly or yearly for these types of services.
No matter what media you decide to use, you should make sure that is external to your computer, and if possible, you should also make sure your media is stored external to your site. Otherwise, if you have a problem with your computer or office, you may lose your backup as well.
Database Backup Common Mistakes
Even if you are making regular backups, you may still run into problems. Here are some practices to avoid when you make your backups:
Saving the backup to the same computer as the database. If you have a hardware failure, your backup could be lost as well. It’s always a good idea to save your backup on media that is external to your system.
Not making frequent backups. In a heavily used system, your database may grow significantly everyday. If you are only making occasional backups, you could lose a lot information.
Not testing your backup. When you start making backups, you should always test a restore, preferably on a completely different computer. This way you will know for sure that you have everything configured properly, ensuring backup integrity.
Not properly identifying backup files. Making regular backups is great. But if you can’t find your backup file when you need it, it won’t do you any good. Therefore, you should always give your backup files clear names that indicate the date and time the backup was made.
Not including everything. Depending on how your backup is configured, it may not include all of your data. For example, a default WordPress database backup (performed through phpMyAdmin) does not include images and theme files. It only includes pages, comments and posts. Even if you are using a prebuilt backup function, you should make sure the backup contains all the data you need to restore your system. You may need to look at custom backup configurations before all of your data is properly selected. The procedure for a custom backup will differ depending on your database system and you may need to use tools like MySQL Workbench and SQL Server Management Studio (SSMS).
Database Backup Policies
Your database backup policy outlines your backup procedures, schedules and content. Having a defined policy ensures that you have a comprehensive backup plan that will support your disaster recovery plan. Your policy should include the following:
A list of items you want backed up (i.e., transaction logs, data, videos, documents, images etc.)
The schedule you will make your backups (hourly, daily, weekly, monthly etc.)
The time(s) of day you will make your backups.
The tools you will use to make your backups and perform database system restores.
Who is responsible for your backups (yourself, IT support staff etc.)
The type of backups (manual or automatic).
Location of backups (onsite, offsite
Validation procedures to make sure that your database backups are complete.
Documentation for restoring data if needed.
Having the above items worked out advance will ensure that your backups are available when you need them, and can be used to fully restore your database system in case disaster strikes and your data becomes corrupted.
Tracker Ten Backups
Making backups in our own Tracker Ten system is quite easy. You just need to make a copy of your database file, along with the program data directory found in “My Documents”.
