Q: Would you suggest recovery strategies in data warehousing while loading high volumes of data?

Chuck Kelley's Answer:

One of the recovery strategies that I have successfully used in the past is to have the extract, transform and load (ETL) process bulk load files to the database. Then I would keep that data for some amount of time and use those for my recovery. For example, I might do a full backup on the first of the month. Then keep the load files for the balance of the month. Then if I need to recover, just recover the last full backup and bulk load all of the files. However, this depends on doing no "updates" to the data warehouse. Another option might be done for recovery is to keep a separate database and use log shipping to update that database. Then it is always ready. You will need to balance cost versus recovery time.

Regardless of what you choose, please test it. There are too many places that have great recovery strategies but have never tested them. Of course, they will fail.

Anne Marie Smith's Answer:

I would always have a recovery strategy for any loading of data to the data warehouse (DW), regardless of the amount of data. It is essential to have a good backup and recovery plan in the project and to use the plan when necessary. Exactly what you back up and recover and when is dependent upon the project and the tools and other issues, but the plan/strategy should be developed, implemented and refined during the project development phase.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access