Transfer objects and data between SQL 2005 databases

Question

I am wanting to transfer objects (tables, stored procedures, data etc) between two servers (Dev box and Live box) and was wondering what the best approach for doing this is?

In SQL Server 2000, you could transfer all objects and data between databases. Now all there is is 'copy data' and 'write a query'. Where has the second option gone?

Both databases are SQL 2005 (with service pack 2). When transferring, primary keys and relationships should be kept intact as well as all the views and other associated data with regards to ASP.NET authentication. Integration Services is not setup up on the live server, so that is not an option.

The only way I can think of is generating scripts, then running them on the other server, but that is more time consuming than the old way (this is how I am doing it now).

Answer 1

Database Publishing Wizard

http://sqlhost.codeplex.com/

Answer 2

It's a shame you haven't got Integration Services installed as you could use the "Copy Database Wizard". I believe this creates an SSIS package that runs on the destination server.

If you have Visual Studio 2008, you could try the Data comparison and Schema comparison tools.

Answer 3

Your best bet is probably a schema & data comparison tool; there's various tools listed at http://www.mssqltips.com/tip.asp?tip=1069

Answer 4

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

Very useful products.

Answer 5

You don't mention the scope of your application or the number of developers, etc., so it is a little hard to make any recommendations. However, if your development consists of multiple concurrent projects and multiple developers and you are copying from a Development to Production I would recommend something like the following:

• implement 3 "areas": dev, qa, production.
• develop all changes in dev, create all changes in scripts, use something like cvs or sourcesafe to track changes on all objects
• when changes are ready and tested, run your scripts in qa, this will validate your scripts and install procedure
• when ready run your scripts and install procedure on production

note: qa is almost identical to production, except applied changes waiting for their final production install. dev contains any work in progress changes, extra debug junk, etc. You can periodically restore a production backup onto qa and dev to resync them (just make sure all developers are aware of this and plan accordingly), because (depending on the number of developers) they (production vs. qa vs. dev) will start to incur more differences over time.