@jochem, the reason you have to do the copy is because without system administrator privileges, you cant use any of the features built in to SQLServer - you can use backup/restore, you cant use the copy database wizard, you cant use the synchronise functionality. In a shared hosting environment, you cant have every user with system administrator access. The most they get is DBO (database owner).
Hosting companies (including mine) take daily backups, but I'm not certain it's a brilliant idea to rely 100% on that - Murphy's law says that if anything goes pear shaped and i need to go back to backup, that'll be the day that something went wrong with the backup process and there's no backup available. (I'm a belt and braces man - i like to have a backup plan to my backup plan) And also, I develop locally, and like to have my development environment operating on an identical database to my production one. So i take a regular copy of the production databases to the dev environment. Then as i develop things, frequently there are database changes, so I change the dev version, build my code and test it, then copy the database to the production. So if you havent got database server administrator privileges, the only way to do all that is to copy databases, both data and schema between one server and the other. @Richard, thanks - i have experimented with SSIS too, and never manged to be able to construct a solution that i could save in a way that it's reusable. The only way i ever got it to do anything for me is to build the solution then run debug. There was no way i ever got it to compile into a job i could run. At one point i got excited because i did manage to get it to compile. I ran that compiled job, it seemed like it was doing something but not a single byte of data was transferred after an hour of ticking over So I still have to use DTS, go though every table one by one, and set the specs of that, then run the DTS package, and next time i need it, i have to do the whole thing all over again because there is no way i've discovered to recall a previously run DTS job and run it again. That is something i used to do all teh time ( like every night) in the old SQLServer2000 but doesnt seem to exist in SQLServer2005. @Matt: i think you might have a good point. Perhaps i should start migrating all my clients over to MySQL. But that's not going to happen overnight though. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jan 6, 2009 at 9:54 AM, Jochem van Dieten <[email protected]> wrote: > On Mon, Jan 5, 2009 at 1:30 AM, Mike Kear wrote: >> I'm heartily sick of the tedious way I have to spend half a day or >> more EACH WEEK uploading and downloading databases from my >> SQLServer2005 web sites. > > The thing I don't understand is why you would do all that uploading > and downloading in the first place. Shouldn't you have some > architecture where one of the two is leading and you just sync the > other one to it? Or is the data on production leading and do you have > weekly releases with schema changes that you push from development to > production? > > Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317436 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
