| Author |
Topic |
|
amitch0219
Starting Member
4 Posts |
 Posted - 2011-08-23 : 09:50:07
|
Hi Guys,Is there some kind of a script or SP where i can identify any changes made to all tables within a database on a give date ?my database crashed due to a data entry, we have around 400 tables, I was wondering if there is a way to write a SP to give me a list of changes that were made to any of those tables on a particular date.Thank You so much !  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-23 : 09:54:20
|
| is it sql 2008? have you enabled change data capture?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
amitch0219
Starting Member
4 Posts |
Posted - 2011-08-23 : 10:05:40
|
| Yes it is SQL 2008 R2.i am not sure if i have enabled change data capture. |
|
|
|
amitch0219
Starting Member
4 Posts |
Posted - 2011-08-23 : 10:07:41
|
| No, we do not have change data capture enabled. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-23 : 11:25:18
|
| then its difficult unless you've explicit audit/history tables tracking changes to tables------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-23 : 13:22:07
|
| Log reader perhaps?Both expensive and requiring considerable effort / skills - e.g. the use of a specialist company. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-23 : 14:14:23
|
| $1000 at least and requiring full recovery model and log backups...--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-23 : 16:51:22
|
Indeedie ...  ... but if full recovery model was in use then a tail log backup should be possible, and thus restore to point-in-time which should make it possible to compare the data to some earlier position - giving a reasonable idea of what-had-changed, across all tables, for ... errmmm ... free? !! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-23 : 18:00:40
|
| Providing a full backup from before the date they are interested in exists and there an unbroken set of log backups from that full backup up until the time of the crash. If that exists, then that can be restored as the 'database at the time of the crash' and something like SQLDataCompare used to compare the two.--Gail ShawSQL Server MVP |
|
|
|
gwilson67
Starting Member
42 Posts |
Posted - 2011-08-23 : 20:58:32
|
| I have a tsql script that provides that information down to the date and time. I've used it a few times. Let me know if you need a copy.Greghttp://www.freewebstore.org/tsqlcoderepositoryPowerful tool for SQL Server development |
|
|
|
|
SP for querying changes in database