Geek Answers Handbook

How to take daily table shots

I am creating a sales database. One of the tables should be a hierarchy of sales representatives and their designated territories. Ohese reps and their territories change every day, and I need to monitor what this table looks like every day. I need to take pictures of the table daily.

I would like to know what I need to do or how I should store data in a table in order to know exactly what data in the table was at a particular point in time.

Is it possible?

Please keep in mind that the table will be no more than one megabyte or so.

+2
source share
2 answers

I suggest using Paul Nielsen AutoAudit :

AutoAudit is a Code-Gen SQL Server utility (2005, 2008) that creates audit triggers:

  • Created, Created, Modified, Modified and RowVersion (INT Incrementing) Columns in a Table
  • Insert an event registered in the audit table.
  • Updates old and new values ​​registered in the audit table.
  • Delete logs of all endpoints in the audit table.
  • view deleted row recovery
  • UDF to restore row history
  • Schema audit trigger to track schema changes
  • Re-code-gens generated triggers when the change table modifies the table

His original blog post: CodeGen to create fixed audit triggers

Before introducing into production, offer to restore a backup copy of your database during the development process and work on it.

+3
source

This is for MS SQL.

Seeing that the table is so small you make the best use of the Snapshot functionality provided by MS SQL.

Take a database snapshot:

CREATE DATABASE YourDB_Snapshot_DateStamp ON ( NAME = YourDB_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\YourDB_Snapshot_DateStamp.ss' ) AS SNAPSHOT OF YourDB; GO

See this page for reference: http://msdn.microsoft.com/en-us/library/ms175876.aspx

You can take as many pictures as you want. So my advice is to create a script or task that creates a daily snapshot and adds a date to the name of the snapshot. Thus, all your pictures will be visible on your server.

Important to note: snapshots are read-only.

0
source

More articles:


All Articles