| Author |
Topic |
|
sync00
Starting Member
24 Posts |
 Posted - 2011-01-03 : 11:30:59
|
| I'm looking for a tool that can take a snapshot of the database and then compare a changed version of the database to the snapshot and show every record that is new, deleted or changed.My searching shows that SQL Server has a snapshot feature. I am not referring to that. Although I don't know anything about SQL Server snapshots so I suppose it is possible the tool might use that feature. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-03 : 12:47:54
|
| Look for Red Gate Data Compare. If you have Visual Studio 2010, or 2008 Database Edition, it also has a data comparison feature. |
 |
|
|
sync00
Starting Member
24 Posts |
Posted - 2011-01-03 : 14:46:34
|
| Thanks. I'll check those out. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 04:16:41
|
Might depend a bit what you want the "comparison" for?Its easy enough to compare a table in Database-A with one in Database-B (which is, say, your "old" version). So if you want the data displayed to end users within your application, say, then building-your-own is relatively simple.But for a one-off exercise, say, then Red Gate Compare will do a fine job.So ... depends a bit on how you want to use the comparison  |
|
|
|
sync00
Starting Member
24 Posts |
Posted - 2011-01-04 : 11:01:42
|
| The purpose is to help me figure out how the database works. This is a point of sale system and the vendor does not provide documentation for the back end. I need to write reports and utilities for the back end and have to figure out how it works. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 11:20:40
|
| You can use "SQL Profiler" to spy on the system. That will show you all SQL statements sent to the server for execution. You can filter it - e.g. to just your connection - so you can see that if you do operation-X that causes SQL-Y (or SQL-A, SQL-B and SQL-C) to be sent to the server for execution.If you are able to change the database (licence might disallow it?) you could add a TRIGGER to some/all tables. That trigger could just save the "BEFORE" (or "AFTER") data into Audit Tables (in another database if you like). These would show how the data for a particular record had changed over time. I don't think this would be useful for what you describe, but given that you original mentioned using a database-snapshot comparison then maybe knowing each set of values that a record changed through, over time [including the actual time the change was made] that might help. |
|
|
|
sync00
Starting Member
24 Posts |
Posted - 2011-01-04 : 12:47:53
|
| The profiler sounds interesting. However I'm not proficient with SQL and would find it easier to use a data compare tool. |
|
|
|
sync00
Starting Member
24 Posts |
Posted - 2011-01-13 : 15:19:50
|
| I now have a data comparison tool. When I used one in the past it was simple to copy the database for comparison purposes because it just meant copying a file.Is there a simple way to copy a SQL Server database? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-13 : 17:27:26
|
| Which tool are you using? Red Gate doesn't need to copy the database to do the comparison.If you prefer to work on a copy the best way to do it is to take a full database backup and restore it on another server. Books Online has the syntax for the BACKUP command. |
|
|
|
sync00
Starting Member
24 Posts |
Posted - 2011-01-13 : 19:08:09
|
| That feature of Red Gate sounds very useful.I went with a product from DB Balance because it also supports Access databases.There is no second server to restore to but I can restore to a new db.Thanks. |
|
|
|
fenixtx119
Starting Member
2 Posts |
Posted - 2011-03-10 : 12:38:33
|
| unspammed |
|
|
|
|
Looking for a data compare tool