How to synchronize SQL Server 2008 database with SQL Server 2005 database?

views:

answers:

How to synchronize SQL Server 2008 database with SQL Server 2005 database?

I am using VS 2008 Team Suite and SQL Server 2008 in my development environment. I am deploying to a shared-host website with shared-host SQL Server 2005.

I want to push changes from my development environment to my production host.

I tried using Data | Schema Compare... and it reports to me that it does not support SQL Server 2008.

What do people use for this (Besides Red-Gate tools - I use those at my day job, and they rock...this is a volunteer thing for my son's school)?

I am looking for something very inexpensive if not free.

+2 A:

Downgrade the development environment is my advice -- I use the management studio and script everything by hand directly, or often just generate the scripts and trim them by hand. Tools are dangerous.

Once you know T-SQL backwards and forwards (every little under-the-hood or non-obvious way of doing something) a tool is just a shackle.

Hassan Syed 2010-01-18 19:33:14

What about installing SQL 2005 and/or SQL Express 2005 - anyone know of any problems with having those environments coexist on one box? I support I could make a VM for each server type. Thanks for your great input - voting you up.

arrocharGeek 2010-01-18 19:35:33

thanks for the upvote, that is not a bad idea -- however with the complexity of the install suite I would think they can't co-exist. However you have nothing to loose the installer will complain by itself I would assume.

Hassan Syed 2010-01-18 19:39:17

If this a school I would assume the traffic is ultra low. You can just do a cowboy upgrade =D backup the DB onto a temporary machine, and have it serve as the target DB while you rebuild the main server. If you are using WINS names (in the db clients) to referece the db server you can just temporarily redirect it by using the hosts file in windows (at the clients) http://en.wikipedia.org/wiki/Hosts_file. Make sure you create more than one backup if you use this approach.

Hassan Syed 2010-01-18 19:43:56

+1 A:

If this is a one-time thing, why not just use a demo version of Red Gate's comparison suite?

Paul Sasik 2010-01-18 19:33:35

I guess that would work for now. Would like something longer term.

arrocharGeek 2010-01-18 19:36:44

I am going to go with this approach, although it feels a little cheap to use the trial this way (sorry redgate). I am going to use redgate to get the production server seeded with data, then make a VMware VM, install SQL 2005, push the data from production down to that dev db, and point my dev server there. Then I can use the deployment wizard from SQL 2005 -> SQL 2005 databases.I appreciate all the help from those who commmented and answered.

arrocharGeek 2010-01-27 17:37:25

+1 A:

If you have Visual Studio 2008 Team Suite or Database Edition, Microsoft has a patch for this, "Microsoft® Visual Studio Team System 2008 Database Edition GDR R2," which you can download from msdn. You'll have to close Visual Studio to do the install, but that's really about all the hassle that's involved.

Brief Description: "Microsoft® Visual Studio Team System 2008 Database Edition GDR R2 implements support for SQL Server 2008."

Paul Smith 2010-03-17 21:07:54

ansaurus

tags:

views:

answers:

How to synchronize SQL Server 2008 database with SQL Server 2005 database?

related questions