Posted by Erland Sommarskog on 08/31/07 21:42

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]