Backup and DR
Database Backup Best Practices for MSPs
If you’re an MSP, backing up databases is a critical part of virtually every data backup and recovery plan that you provide to clients. It can also be a challenging part because the approach required to back up databases differs in certain key respects from backing up other types of files.
You can’t simply copy a database and expect to be able to restore it later from the copy without also taking into account special needs related to permissions, database state, and more. Toward that end, keep reading for an overview of database backup and recovery best practices for MSPs. No matter which types of databases you are dealing with -- Microsoft SQL, MySQL, a NoSQL-type database, or something else -- these principles will help ensure that you are prepared to back up and recover your clients’ databases effectively.
Scheduling database backups
The first question you’ll have to answer when planning database backups as an MSP is when to schedule them. This is a complex topic because in most cases, you should perform three distinct types of backups, each on a different schedule:
- Full database backups: This type of backup creates copies of your entire database. It’s the most thorough type of database backup, but also the one that consumes the most time and system resources. Therefore, you typically perform full database backups the least often.
- Differential database backups: With this type of backup, you back up only what has changed within a given database since the last full backup. It’s much faster than a full backup (if you schedule regular full backup and your differential backups do not approach the size of the full backup), but carries more risks of something going wrong that leads to an inability to restore the database.
- Transaction log backup: For database types that support transaction logs (or T-logs), such as Microsoft SQL Server databases, you can back up these logs and use them to reconstruct a database. This is the fastest type of backup, but it is not quite as reliable as the other options.
The schedule you use for each type of database backup will depend on your clients’ RTO and RPO needs, as well as how thoroughly you can test backups to ensure that data can actually be recovered. If you have a minimal ability to test backups, you should stick primarily with full database backups, since they are most reliable. But if you can perform thorough backup tests on differential or T-log backups, you can schedule these types of backups more frequently, and perform full backups perhaps only once a week or once a month.
Separate system backup and operational database backup
Another key database backup best practice is to back up databases separately from system files. Although you may be tempted to back up your operating system’s file system and configuration data as part of the same routine as databases, this is a poor approach, because databases have fundamentally different backup requirements.
Generally speaking, you can use the image-level backup to back up operating system data. Image-level backup does not usually work well for databases, because most databases are not designed to be able to recover from a system image that was created while the database was still “live” and running.
In most cases, you also don’t need to create a backup of the operating system (which rarely changes after installation and configuration) as frequently as you do T-log or differential backup of an operational database (meaning one that is constantly being modified and requiring frequent backup to avoid the risk of losing working data).
That’s why you want to use a backup tool, like MSP360 Backup for Microsoft Exchange or SQL Server, that is designed specifically to back up databases safely and reliably. (MSP360 Backup can also perform a system image backup, or as we usually call it, image-based backup, but it uses a separate process to do so.)
Use all available database verification options during the backup process
Another reason why you should rely on backup tools designed with the unique needs of databases in mind is that these tools provide verification options that help protect against the risk of data loss or inconsistencies during the backup process. Be sure to enable and take advantage of these features. They might slightly increase the time required to complete a backup, but they are well worth the extra reliability.
Storing your database backups
This may seem obvious, but it’s an easy mistake to make: Don’t store database backup files in the same physical location as the production databases. MSPs sometimes make this mistake because databases tend to be hosted in environments with lots of storage space, making them a tempting location to store backup databases, too. But that creates an unacceptable risk of the backups being lost due to a failure that impacts the production infrastructure.
An easy solution is to store database backups in the cloud, where they are separate from production data and can be recovered from anywhere.
Include system databases in your backups
Access rights and database configurations need to be preserved to restore the databases later in a proper way. Be sure that you are backing up system databases to save this data. This is another reason why it’s important to use database-aware backup tools, rather than simply copying database files in a generic manner to create backups.
Ensure database recoverability
Another important aspect of database backup and recovery best practices is to ensure database recoverability beforehand. No MSP wants to discover during disaster recovery that a database backup can’t be used to restore data. To guard against this risk, it’s critical to have a plan in place for testing database backups periodically to ensure that they can be used successfully for data recovery.
The best way to do this is to keep a test server available (either in an on-premises data center or in the cloud -- whichever location is more convenient for accessing the backup data) and periodically perform a recovery routine from it with database files. This requires some extra effort, but here again, it’s well worth the assurance that it provides against data loss.
Further reading Disaster Recovery Testing for MSPs
Disaster recovery planning for databases
A final database backup best practice is to make sure that you include database recovery as a distinct part of your clients’ disaster recovery planning. Don’t simply assume that your generic recovery procedures will allow you to restore databases successfully; doing so will often fail because, again, you can’t simply load a copy of a database into an operating system and expect it to work. If you do, you will likely find that permission problems, missing configuration data, and other issues prevent proper access to the database.
Instead, you need recovery tools, like MSP360 Backup (or MSP360 Managed Backup designed specifically for MSPs), and processes that are prepared to address the unique needs of database disaster recovery. Operating system recovery should be treated as a separate process. Refer to our guide on Disaster Recovery Planning to learn more:
Conclusion
Backing up databases reliably is more complicated than it may first appear. But with the right tools and database backup best practices in place, you can provide effective database backup and recovery services to your clients.
- CENTRALIZED MANAGEMENT
- NO CONTRACTS
- UNDER YOUR BRAND
