2005 Copy database objects from one server to another

[Jer]

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.
Thanks!
Jer

[Erland Sommarskog]

Jer (je***********@saic.com) writes:

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.

The recommended method is to have all SQL objects under version control
and build all install kits from the version-control system.

At http://www.red-gate.com you can find SQL Compare, which many people
use for this sort of thing.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Malcolm]

Jer wrote:

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.
Thanks!
Jer

A better tool for handling changes via source control is DB Ghost from
Innovartis (http://www.dbghost.com/ggroups) - Red Gate's tools do not
work with source control and thus provide no audit trail whatsoever so
Erland's advice is slightly misleading (sorry Erland, this isn't a
personal attack you just need to understand what Red Gate's tool's
capabilities and limitations are).

Regards,

Malcolm (www.innovartis.co.uk)