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 the 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 backing up the HRPro and EPortal websites 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.

為什麼以及何時應該備份我的資料庫？

備份與復原是企業資訊系統中最重要的環節之一。硬體與軟體雖然可以更換，但您的資料可能是無法取代的。

備份可能需要的原因有很多：

• 從意外刪除檔案中恢復。最常見的備份需求是從使用者錯誤和意外中恢復。

• 從應用程式錯誤中恢復。錯誤的資料或處理故障可能需要將應用程式資料還原至已知的良好狀態。

• 還原系統的先前狀態。應用程式環境變更，例如套用補丁或版本升級，可能會對生產系統造成負面影響。撤銷這種變更可能極為困難，甚至根本無法做到。在變更前執行完整備份，是防範升級故障可被復原的最佳保險。

• 從硬體故障中恢復。硬體故障可能導致資料錯誤，或更常見的是使系統崩潰。處理中斷常常令應用程式進入不明狀態。許多商業資料庫管理系統設有復原機制以整理混亂。

• 從系統或站點損失中恢復。商業應用程式和作業環境或許能從供應商媒體復原，但應用資料和自訂化則必須倚靠良好的備份。媒體應存放於異地。若建築物被毀，儲存在電腦旁的備份將毫無用處。

多久應備份一次？

備份頻率應根據資料庫資料的變動速率或頻率來決定，例如現有表格中資料列的新增、更新、刪除，以及新表格的添加。若資料庫資料變動頻率高，備份的頻率也應相應增加。

一般會安排每日、每週和每月的分層備份。

應保留多少份備份？

大多數人只做一份備份，每次都覆蓋舊檔。這樣省空間也減少麻煩。但若備份檔損毀或遺失怎麼辦？一般建議至少保留三份備份，並儲存在三個不同的場所或載體，如隨身碟、光碟、硬碟或雲端儲存等。

如何在 HRPro 備份資料庫？

請到 系統管理員 ⇒ 維護 ⇒ 資料庫備份

這個畫面有兩個分頁，應用程式資料庫 和 系統資料庫。應用程式資料儲存所有業務資訊，而系統資料庫則儲存系統相關資訊，包括安全性設置。

備份步驟：

（選填）輸入備份名稱

（選填）輸入備份位置路徑（備份位置與伺服器磁碟架構相關）。否則會備份到預設位置，通常為 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

點擊「開始」按鈕以啟動備份

備份完成後，最近 10 筆備份記錄表會新增一列新備份集。

亦可以考慮備份通常儲存於 C:\Inetpub\wwwroot 的 HRPro 和 EPortal 網站。

備份可以自動化嗎？

決定好所需備份類型和頻率後，建議將定期備份安排納入資料庫維護計劃。關於維護計劃和如何建立資料庫及日誌備份維護計劃，可參閱：Maintaining Databases (Database Engine)🡕 and Maintenance Plan Wizard🡕. 

建立維護計劃請參閱：

• How to: Create a Maintenance Plan🡕

建立及安排工作請參閱：

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

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

亦可在 HRPro 設定定期備份。

測試備份

未經測試的備份，等同於沒有還原計劃。務必針對每個資料庫完整測試備份策略，將資料庫還原至測試系統。必須測試所有打算使用的備份類型的還原過程。

建議為每個資料庫維護一本操作手冊，記錄備份位置、備份設備名稱（如有）以及還原測試備份所需時間。