Mirgration Q.

1 Jul 2005 11:28 AM

Ed

Hi,
I have a database migration question. If I have a brand new database, I
believe I can easily use dettach/attach or backup/restore to move database
from Development to Production Server. If I already have an existing
database, and a developer just added let's say 5 more tables on the
development server. What is the best way to move those 5 tables.

I tried DTS move database objects but I found out it is only good for table
schema including all the datatype like identity column but the bad thing is
it cannot transfer the relationships between tables since those 5 new tables
need to join some of the existing tables.

I have been using generate the SQL Script to script the table schema and run
it on the production server and add the relationship manually. Is that a
correct way to do it or any better idea?

Thanks

Edmund

1 Jul 2005 12:49 PM

Rudi

Not saying it is the best way, but I surely do use scripts.
We also store these in our source control software.
It is more tedious work, but you can tweak the scripts to your liking, and
rebuild/update databases from a commandline.

Some pointers to help you out:
Use VS.NET database projects and the possibility to generate scripts when
changes are made to the tables.
Use a tool like SQL Data Compare to generate the scripts.

Rudi

Show quote

"Ed" <E*@discussions.microsoft.com> wrote in message
news:2254FE93-2DCE-4F5D-AD06-1A3846CDDEEA@microsoft.com...
> Hi,
> I have a database migration question. If I have a brand new database, I
> believe I can easily use dettach/attach or backup/restore to move database
> from Development to Production Server. If I already have an existing
> database, and a developer just added let's say 5 more tables on the
> development server. What is the best way to move those 5 tables.
>
> I tried DTS move database objects but I found out it is only good for
> table
> schema including all the datatype like identity column but the bad thing
> is
> it cannot transfer the relationships between tables since those 5 new
> tables
> need to join some of the existing tables.
>
> I have been using generate the SQL Script to script the table schema and
> run
> it on the production server and add the relationship manually. Is that a
> correct way to do it or any better idea?
>
> Thanks
>
> Edmund