Migrating Database changes to Production

We often have to migrate changes to sql server 2000 databases from
development to production. Normally we dump the sql from Enterprise Manager
for production and development and do a diff (using CSDiff - downloadable for
free).

From the diff information we create some scripts to add new tables or alter
existing tables as required. This is time consuming and error-prone.

I am now trying the following:
1. Backup up the Development database
2. Delete the Development database
3. Restore the Development database as Development_Backup
3. Use the the sql script to create the new Development database
structure from scratch
4. Use Data Transformation Services to migrate the data from
Development_Backup

The problem is that many of the 'sys' tables are empty. For example table
'sysforeignkeys' is empty.

Is there an easy way to rebuild the data in the 'sys' tables?

Thanks

Re: Migrating Database changes to Production

On Aug 30, 8:10 pm, "RichardLamont" wrote:
> We often have to migrate changes to sql server 2000 databases from
> development to production. Normally we dump the sql from Enterprise Manager
> for production and development and do a diff (using CSDiff - downloadable for
> free).
>
> From the diff information we create some scripts to add new tables or alter
> existing tables as required. This is time consuming and error-prone.
>
> I am now trying the following:
> 1. Backup up the Developmentdatabase
> 2. Delete the Developmentdatabase
> 3. Restore the Developmentdatabaseas Development_Backup
> 3. Use the the sqlscriptto create the new Developmentdatabase
> structure from scratch
> 4. Use Data Transformation Services to migrate the data from
> Development_Backup
>
> The problem is that many of the 'sys' tables are empty. For example table
> 'sysforeignkeys' is empty.
>
> Is there an easy way to rebuild the data in the 'sys' tables?
>
> Thanks

Hey Richard,

you should check out xSQL Software's xSQL Object and xSQL Data Compare
(http://www.xsqlsoftware.com) - they allow you to both compare and
synchronize the two databases you are working with. The process is
very simple and straight forward - you run a compare, view the
differences, automatically generate the script that will push the
changes on the direction you want to and finally execute the script.
You can also the associated command line utilities to automate the
process of propagating the changes from one environment to the
other.

And here is the best part - xSQL Bundle which includes xSQL Object and
xSQL Data Compare is:
- free for SQL Server Express;
- free for smaller databases (based on the number of objects in the
database)
- and of course you have the two weeks of free trial for any SQL
Server Edition and any size database

Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Re: Migrating Database changes to Production

RichardLamont (u37070@uwe) writes:
> We often have to migrate changes to sql server 2000 databases from
> development to production. Normally we dump the sql from Enterprise
> Manager for production and development and do a diff (using CSDiff -
> downloadable for free).

A more polished procedure would be keep all code under version
control, and set a label every time you move changes to production.
You then build a change script by including all that has changed
since the previous label.

> I am now trying the following:
> 1. Backup up the Development database
> 2. Delete the Development database
> 3. Restore the Development database as Development_Backup
> 3. Use the the sql script to create the new Development database
> structure from scratch
> 4. Use Data Transformation Services to migrate the data from
> Development_Backup
>
> The problem is that many of the 'sys' tables are empty. For example table
> 'sysforeignkeys' is empty.
>
> Is there an easy way to rebuild the data in the 'sys' tables?

If sysforeignkeys is empty, that is because your build script
apparently does not include any definition of foreign keys.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Migrating Database changes to Production

Hi Richard,

Erland is quite right - having the scripts that create your entire
schema under source control is the most fundamental step in taking
control of database changes. But how do you propagate changes to
CREATE scripts through to your test and production databases easily?

That's where DB Ghost (http://www.dbghost.com) comes in. It is not
like the myriad of "diff tools" out there that just write you an
upgrade script of the differences between two databases as it enables
your "source database" to be a set of CREATE scripts thereby
automatically giving you all the benefits of source control, but for
SQL (versioning, history, diffs - basically who changed what, when and
why).

The other big differentiator for DB Ghost is that, when it does
produce a delta script it is guaranteed to work - a feat not managed
by most products on the market.

Kind regards,

ML
Innovartis Ltd. (http://www.innovartis.co.uk)
DB Ghost, build, compare and synchronize from source control =
database change management for SQL Server