You've learned how to take a snapshot of your data. Snapshots can be used effectively for different purposes in development, testing, and production. You can use historical data to summarize and analyze data from specific time frames. You've also seen how to audit changes to your data and how to restore individual records using audited data. By saving historical data and auditing changes to your database, you can ensure the integrity of your data by being able to back out individual changes without having to restore the entire database.
To | Do this |
|---|
Create a database snapshot | Use the CREATE DATABASE statement with the AS SNAPSHOT OF option. |
Revert to a database snapshot | Use the RESTORE DATABASE statement with the FROM SNAPSHOT option. |
Delete a database snapshot | Use the DROP DATABASE statement or delete the database snapshot from SQL Server Management Studio. |
Schedule loading a summarized history table | Create a new Job in SQL Server Agent and set the schedule to meet your needs. |
Summarize data for a specific time frame | Create an indexed view for the time frame or time frames you wish to summarize. |
Audit changes to your data | Add Audit columns to the table, in which to track who made the change and when it was made. |
Fully track data changes and be able to recover from data entry mistakes | Create and load an audit table either with triggers or by using the OUTPUT clause. |