Backing Up Data Warehouse Data
An online transaction processing (OLTP) system captures incoming data and updates a database. To ensure against loss of data, the system logs transactions as they are performed, and administrators develop backup strategies that include periodic full and incremental backups of database. These strategies are designed to prevent loss of data, to minimize interference with operational transaction processing, and to provide for rapid recovery from system malfunctions.
In contrast, a data warehouse stores massive amounts of stable historical data that is updated on a managed periodic schedule. For data warehouses, you should design backup strategies to minimize full backups and to use incremental backups for data updates.
Recovery time constraints are often more flexible and less restrictive for data warehouse malfunctions than for OLTP malfunctions. More permissive recovery time constraints usually permit full data warehouse backups to be made much less frequently than are required for OLTP systems. For example, a table of sales facts may contain hundreds of millions of rows that reflect sales for ten years of history. It is quite unlikely that changes will be made to sales data after the business has performed a year-end closing process.
Repeated backups of data that has not changed are unnecessary, and backup strategies should take this into account. Depending on recovery time constraints and data volume, a strategy may be created that backs up data added during data warehouse updates using incremental backups, and then creates a backup of only the data added during the current year after year-end closing. To recover from a complete failure of the data warehouse database would require loading multiple backups, one for each year prior to the current year, then incremental backups for the current year updates.
Microsoft® SQL Server™ 2000 Analysis Services maintains OLAP data in special-purpose Analysis server databases, which can be archived and restored separately from data warehouse database backups.