Database Backup and Restore

You have a SQL server with a number of SQL databases running on it. It’s not a large server, but it provides hosting for your CRM, ERP, accounting, and time clock systems which employ a number of databases.

Being accessed by both customers and staff, It is of course critical to production, so you have correctly have it ranked at the top of your restore priorities in your disaster recovery plan with nightly image backups running to both local and offsite backup storage so the server quickly can be brought in its entirety.

As well as the image backup is great for restoring the entire server, it’s useless for selective restore. To restore one or more databases for an application you can’t use the image restore as it at the same time will remove/revert all other changes made to the system.

Just as a file-level backup on its own does not make a complete disaster recovery plan, neither does a block-level backup. A well-thought out disaster recovery plan needs to be able to account for disasters that are common on database servers and that means being able to restore individual components of a database server is an important capability.

One common scenario would be a healthy server that is fulfilling its production duties and is only missing or has corrupted a small amount of data. Taking the host machine down for an image restore would be undesirable. as fully functioning systems would lose good data needlessly.

Any production database server will always need a reliable and verifiably consistent backup of the individual databases themselves, as well as a fast and foolproof means to restore the databases flexibly, selectively, and without taking anything else on the host machine offline. There will always be situations that call for that capability.

In the case of Exchange databases, for example, companies that use Exchange generally have made it the hub of all communications and scheduling for the entire organization, and have many years invested in it. This makes Exchange a critical service.

Exchange will always be at the top of any disaster recovery plan in any disaster scenario and will have the least tolerance for any data loss or downtime. Mailbox and calendar data are particularly precious to executives.

It is common for many organizations to foolishly run Exchange on a server that is also performing duties unrelated to Exchange, or to host Exchange with a third party and hope that removes Exchange from their data protection obligations.

Because of this it is more worthwhile to backup the Exchange content at an even more granular level so that a person’s individual contacts, mail and calendar items can be restored quickly and without interrupting mail service for other users or interrupting other services running on the host.

Having Exchange data backed up at the “mail-level” allows for very convenient and precise restore, as well as very long retention times and it is especially valuable in cases where the Exchange database content needs to be recovered to a new Exchange server in a different domain.

The idea with backup of specialized applications is to carefully select the granularity at which you do the backup so that “the grain” most reflects the real world recovery situations you will face.
At a minimum, a good database backup will give you at least all of the following capability:

• Create a safe, verified backup of each database in a verifiable backup file format
• Backup the database while it is running
• Provide a fast means of restoring backup data to new or existing databases
• Restore data without interrupting the host
• Retain a history of backup data for however long you want
• Compress the backup data to conserve backup storage
• Send you an email report of backup progress/status

While a great database backup will give you even more capability and flexibility:

• Encrypt your backup data for certainty of privacy in the event that you use public or third party storage
• Provide a means of doing an incremental backup on large databases
• Provide a means of backing up the database quickly, multiple times per day for busy systems
• Provide a means of executing custom programs, commands, or scripts before and after the backup
• Provide a means of scheduling or automating restore procedures to new hardware or secondary site
• Provide a centralized management where you can monitor and administer all of your backups in one place