Have Geek Brain

Transferring Objects and Data Between SQL 2005 Databases

I want to transfer objects (tables, stored procedures, data, etc.) between two servers (Dev box and Live box) and was wondering what is the best way to do this?

In SQL Server 2000, you can transfer all objects and data between databases. Now everything is "copy data" and "write request". Where does the second option go?

Both databases are SQL 2005 (SP2). Primary keys and relationships must be preserved as they are passed, as well as all views and other associated data with respect to ASP.NET authentication. The integration services are not configured on a real server, so this is not an option.

The only way I can think of is to generate scripts and then run them on a different server, but that takes longer than the old one (which is how I do it now).

0


a source to share


5 answers


Database Publishing Wizard



http://sqlhost.codeplex.com/

0


a source


If you are willing to pay, I recommend comparing Sql Compare and Sql Data Compare from Red Gate .



Very healthy products.

+2


a source


It’s a shame you don’t have Integration Services because you could use the Copy Database Wizard. I believe this creates an SSIS package that runs on the target server.

If you have Visual Studio 2008, you can try Data Comparison and Schema Tools.

0


a source


Your best bet is the schema and data comparison tool; there are various tools listed at http://www.mssqltips.com/tip.asp?tip=1069

0


a source


You don't specify the scope of your application, or the number of developers, etc., so it's a bit difficult to make any recommendations. However, if your development consists of multiple parallel projects and multiple developers, and you are copying from development to production, then I would recommend the following:

  • implement 3 "areas": dev, qa, production.
  • work out all changes in dev, create all changes in scripts, use something like cvs or sourcesafe to track changes on all objects.
  • When the changes are ready and tested, run your scripts in qa, this will confirm your scripts and installation procedure.
  • when ready to run scripts and setup during production

Note: qa is almost identical to production except for applied changes pending their final production setup. dev contains any work changes, additional debugging garbage, etc. You can periodically restore a production backup to qa and dev to resynchronize them (just make sure all devs are aware of this and plan accordingly) because (depending on the number of devs) they (production vs qa vs dev) will start experiencing more differences over time.

0


a source






More articles:


All Articles