Database Backup Strategy

Why and when should I backup my database? 

Backup and recovery are one of the most important aspects of a business information system.  Hardware and software can always be replaced, but your data may be irreplaceable. 

There are many reasons why a backup could be needed:

1. Recover from accidental file deletions. The most common need for backups is to recover from user errors and accidents.

2. Recover from Application Errors. Bad data or processing faults may make it necessary to restore application data to a known good state.

3. Restore a Prior State of the System. Changes to the application environment such as applying patches or version upgrades may have a bad impact on a production system. Undoing such a change may be very tricky if it can be done at all. Performing a full backup before the change is good insurance that upgrade faults can be recovered.

4. Recover from Hardware Failure. Hardware faults can cause data errors or more likely crash the system. It is not unusual for interrupting processing to leave an application in an indeterminate state. Many commercial database management systems include recovery mechanisms to clean up the mess.

5. Recover from Loss of System or Site. Commercial applications and operating environments may be recoverable from vendor media but application data and customisation recovery requires a sound backup. Media should be stored off-site. If the building is destroyed it is not very helpful if all the backups were sitting in a rack next to the computer.

How often should you back up? 

The frequency of backups should be based on the rate or frequency of changes to database data such as insertions, updates, and deletions of rows in existing tables, and the addition of new tables. If a database's data is changed at a high rate, the database backup frequency should be proportionally high. 

Normally one would schedule a hierarchy of daily, weekly and monthly backups 

How many backups should I keep? 

Most people make a backup and then just replace it every time. It saves space and is less to worry about. But what if that backup file is corrupted or lost? Then what? The general rule of thumb is to keep at least three backups and keep them in three different places or forms, like thumb drives, CDs, different hard drives, cloud storage, etc. 

How can I back up the database in HRPro?

Go to System Manager ⇒ Maintenance ⇒ Database Backup

The are two tabs on this screen, Application Database and System Database. Application Data is storing all the business information while the System Database stores the system-related information including the security settings. 

Backup Procedure: 

Optionally input the Backup Name

Optionally enter the path of the backup location (Please note that the backup location is related to the server disk hierarchy). Otherwise backup will back up to the default location, usually, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL 

Click the Start Button to start backup 

After the backup is done. A new row will be created as a new backup set from the Last 10 Backup Sets Table. 

You may also consider backup the HRPro Web Sites that are usually stored in C:\Inetpub\wwwroot. 

Can backups be automated? 

After you decide what types of backups you require and how frequently you have to perform each type, we recommend that you schedule regular backups as part of a database maintenance plan for the database. For information about maintenance plans and how to create them for database backups and log backups, see Maintaining Databases (Database Engine)🡕 and Maintenance Plan Wizard🡕. 

To create a maintenance plan

• How to: Create a Maintenance Plan🡕

To create and schedule jobs

• How to: Create a Job (SQL Server Management Studio)🡕

• How to: Schedule a Job (SQL Server Management Studio)🡕

You also set up the scheduled job using Scheduled Backup in HRPro.

Testing Your Backups

You do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. 

We recommend that you maintain an operations manual for each database. This operations manual should document the location of the backups, backup device names (if any), and the amount of time that is required to restore the test backups.