Are you talking about keeping records in sync or the database design? To update records in a development or testing environment, I usually just restore a backup of the live database over the dev or test database. This is done rarely, maybe once a month, maybe once a year.
I rarely do development tasks that need the most up to date data. as long as there is an acceptable sampling, I'm set. All that said; I use ApexSQL Diff: http://www.apexsql.com/sql_tools_diff.asp It works for both the structure and data. I have not tested in 2005 yet. At 11:18 PM 4/7/2006, you wrote: >I'm still wrestling with this problem of how to keep my databases in synch >between the dev versions, the backup version and the production versions in >my shared hosting environment. It's proving a VERY time consuming (and >utterly tedious) process to delete all the records in a table, then use SSIS >one table at a time to copy all the records from the other database. I >have about 40 databases with a total of 350 tables, and while I don't have >to keep all of them in synch it's taking up the equivalent of a whole day >each week just copying records. > >Previously, on SQLServer2000 it was done automatically every night without >my having to do anything manually. > >I thought the "Transfer database" prebuilt task would be the go- then I >found out that in a shared hosting environment, I don't have permission to >create databases - only the control panel does, so it can keep track of >quotas and disk space etc. > >I thought I could use SSIS and have it loop through the tables, copying one >after the other, but apparently that wont work because you can't loop >through table names, only column names (unless I am misunderstanding the >docs) and besides, you have to delete all the records from the receiving >table before you can upload the records from the sending table, or all the >linked tables will have the wrong indexes. > >Then I thought perhaps replication might be the way to go. Apparently it's >designed for just this task. But there's no way to set it up on the tools >Microsoft gave me. Does anyone know how I can set up replication with >these tools? There's no tab, menu, right-click option or anything I can >find that mentions "replication" that I've found. > >Here are the tools I have available - perhaps someone knows them better than >I do and can offer a suggestion as to where to look: > > >EMS SQLManager for 2005 Lite - no mention on any menu of replication or SSIS >Microsoft Visual Studio 2005 - no mention of replication on any menu >Microsoft SQL Server Management Studio 9.00.1399.00 no replication mention >anywhere Microsoft Analysis Services Client Tools 2005.090.1399.00 Microsoft >Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) >Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 > >Microsoft Visual Studio 2005 >Version 8.0.50727.42 (RTM.050727-4200) >Microsoft .NET Framework >Version 2.0.50727 > >Installed Edition: IDE Standard > >SQL Server Analysis Services >Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00 > >SQL Server Integration Services >Microsoft SQL Server Integration Services Designer Version 9.00.1399.00 > >SQL Server Reporting Services >Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00 > > >Does anyone have any ideas how I can proceed on this? I'm not going to be >able to keep up this amount of time spent on simple routine database >maintenance for much longer. I refuse to believe I'm the only one in the >world that needs to do this kind of thing. > > >Cheers >Mike Kear >Windsor, NSW, Australia >Macromedia Certified Advanced ColdFusion Developer >AFP Webworks Pty Ltd >http://afpwebworks.com >Full Scale ColdFusion hosting from A$15/month > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2463 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
