Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-07-19 : 11:38:43
|
| Catchy subject has nothing to do with this question.My testing DB seems to eventually get out of date compared to the production DB.Is there a SQL compare utility that'll find the differences and update my test DB (that works?). Or, am I better off zipping a backup of the production and restoring it locally.Sam |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-19 : 11:58:43
|
| we use sql compare and sql data compare from red-gate and these tools work pretty well. -ec |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-19 : 12:58:00
|
| I absolutely love SQL Compare. But I only use it on systems that are way out of date. Typically, all of the various environments gets updated with scripts from VSS that are eventually run in production. It's the best way to test production scripts out.I don't use SQL Data Compare though. I've used Vyas' code to do the data for lookup tables.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-19 : 13:15:29
|
| I'll try the demo.Thanks... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 13:22:06
|
| I tried SQL Data Compare from Red-Gate and decided it wasn't for me. Very pretty, easy to use, but I couldn't make it do things I wanted to - e.g. ignore my UPDATED column in the compare (it can do that) but DISPLAY it in the results (removing it from the compare killed it in the display too). However, the "Copy" tool, used on a row-by-row basis, was handy. Definitely worth a look (30 day trial I think)So I grabbed my trusty sledge hammer instead ...I started with some code from Viktor Gorodnichenko (dunno the URL, but I expect that Google will get you there - I store the URLs in the code I download these days, I didn't back then ...)This compares two databases and shows, table-by-table, rows in one but not the other, and rows in each with the same PK but different values in one, or more, columns.I wrote an ASP page that would display this output in highlighted form.I added parameters for "Tables LIKE these", "Tables in this list", "tables NOT like these" and "Tables NOT in this list".And I made it use the PKs from the first database (instead of them having to be in both, and the same)Then I wrote an SProc to do a compare on a few tables we have which are "trouble" - "hierarchy" tables, for instance, that have "sequencing" columns that change like the wind, or tables that need JOINed data to be useful in a standalone display of differences only. That uses the same higlighting display ASP page, but is more fussy (or should that be "realistic") about what it considers constitutes a difference.I'm now toying with writing an SProc per table that will do differences - that can JOIN whatever is appropriate to make the display make more sense, and could be capable of generating SQL to allow the differences to be squirted from A to B or vice versa. Such SProcs would be mechanically generated, and customised on an as-needed basis.Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-19 : 13:39:27
|
quote:
Originally posted by Kristen
I tried SQL Data Compare from Red-Gate and decided it wasn't for me. Very pretty, easy to use, but I couldn't make it do things I wanted to - e.g. ignore my UPDATED column in the compare (it can do that) but DISPLAY it in the results (removing it from the compare killed it in the display too). However, the "Copy" tool, used on a row-by-row basis, was handy. Definitely worth a look (30 day trial I think)So I grabbed my trusty sledge hammer instead ...I started with some code from Viktor Gorodnichenko (dunno the URL, but I expect that Google will get you there - I store the URLs in the code I download these days, I didn't back then ...)This compares two databases and shows, table-by-table, rows in one but not the other, and rows in each with the same PK but different values in one, or more, columns.I wrote an ASP page that would display this output in highlighted form.I added parameters for "Tables LIKE these", "Tables in this list", "tables NOT like these" and "Tables NOT in this list".And I made it use the PKs from the first database (instead of them having to be in both, and the same)Then I wrote an SProc to do a compare on a few tables we have which are "trouble" - "hierarchy" tables, for instance, that have "sequencing" columns that change like the wind, or tables that need JOINed data to be useful in a standalone display of differences only. That uses the same higlighting display ASP page, but is more fussy (or should that be "realistic") about what it considers constitutes a difference.I'm now toying with writing an SProc per table that will do differences - that can JOIN whatever is appropriate to make the display make more sense, and could be capable of generating SQL to allow the differences to be squirted from A to B or vice versa. Such SProcs would be mechanically generated, and customised on an as-needed basis.
Are you saying that you are going to make this publicly available? ;)-ec |
|
|
|
|
|
|
|
|
Been there. Done that.