Chapter 11. Keeping History Data

After completing this chapter, you will be able to:

• Create and use database snapshots

• Design effective history tables for summarizing historical data

• Create and use indexed views to summarize your historical data

• Build audit columns and tables to track changes in your data

• Use audit tables to recover lost data

The previous chapter discussed using transactions to ensure the integrity of your data. This chapter delves into ensuring data integrity by tracking changes and storing historical versions of the data that can be restored if needed without restoring the entire database.

Historical data can take many different forms, from auditing to data warehousing for analytics. This chapter concentrates on methods for auditing and archiving your data. One of the primary goals of this chapter is to show you ways that you can recover data and track changes. As you can see from the list of objectives above, there are a number of ways to meet this goal. However, Microsoft SQL Server 2005 has methods other than these approaches, which will be highlighted throughout the chapter as alternative methods. Look for Tips as you read to learn about these alternative methods.