Database Backup Strategy

Why and when should I backup my database?

Backup and recovery is 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 operating system 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 prior to 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 interrupted processing to leave an application in an indeterminate state. Many commercial database management systems include recovery mechanisms to cleanup 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 customization 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 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 floppies, CDs, different hard drives, web disk, etc.

How can I backup the database in ERPro?

Goto System Manager->Maintenance->Database Backup

The are two tabs in this screen, Application Database and System Database. Application Data is storing all the business information while the System Database will storing 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 backup 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 line will be created as a new backup set form the Last 10 Backup Sets Table.

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)

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.