March 20, 2009 at 7:10 pm
I just need to do data refresh..just data for the tables...i tried all possible ways never works properly...here is wat i did.
i) disable foreign keys EXEC sp_MSForEachTable 'ALTER TABLE ? NoCHECK CONSTRAINT ALL'
GO
This command works for some tables and sometimes doesnt..
ii)then delete the data and copy each table..works for few tables..doesnt works for others..i do not want to drop the foreirn keys and create that...if anyone has much better and feasible way please let me know..thanks..
March 21, 2009 at 2:58 am
Hello,
We use RedGate’s Data Compare to refresh data in tables e.g. from UAT to Dev.
If you don’t have Data Compare or the budget to buy it, then there other similar tools available for free.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 21, 2009 at 8:38 am
i just downloaded data compare..may i will try..just curious to know?..does it disable the fks..i mean did u have any pbms with thr fks while using data compare?..thanks..
March 21, 2009 at 9:26 am
I"ve never had a problem with any Red Gate product and FKs. If you script out it's code first, you can see that it uses what it needs to handle the data moves, including transactions. Come to think of it, I did run into a problem once with Link Servers, but that's been awhile...they might have fixed that stuff.
March 21, 2009 at 10:46 am
iqtedar (3/20/2009)
I just need to do data refresh..just data for the tables...i tried all possible ways never works properly...here is wat i did.i) disable foreign keys EXEC sp_MSForEachTable 'ALTER TABLE ? NoCHECK CONSTRAINT ALL'
GO
This command works for some tables and sometimes doesnt..
ii)then delete the data and copy each table..works for few tables..doesnt works for others..i do not want to drop the foreirn keys and create that...if anyone has much better and feasible way please let me know..thanks..
Here is how is supposed to be done.
1 Count active FK constraints
2 Issue an alter table nocheck for each constraint
3 Count active FK constraints, should be zero
4 Trucate tables -do not delete, please!
5 Populate tables
6 Issue an alter table check for each constraint
7 Count active FK constraints
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 21, 2009 at 2:46 pm
will it give any problem if the data is already there while using REd Gate.thanks everyone
March 21, 2009 at 3:53 pm
RedGate tools are very smart; if you are just doing compare and update it will only fix data that needs to be changed.
I been using RedGate tools for about year .. so far I have never ran into issue with them 🙂 RedGate and Apex tools = life savers.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply