Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment.

Apparently, the data was transferred correctly, but the database is missing its PK/FK contraints. Luckily, the database hasn't been touched yet as the connected application is still in migration, which means that if the PK/FK constraints are now enabled, there shouldn't be any issues because no rows were created/altered/deleted.

We stil have the original database (which is now staging). The structure is the same, but the content has already changed numerous times during dev testing.

Is there a way in which I can keep the current PROD database, and add the PK/FK constraints to it (based on the staging database)?

Extra info:

  • we have no backups readily available unfortunately, else I would've fixed it that way
  • Using SQL Server 2008 via SSMS
  • I'm not the one who performed the data migration. It is office procedure to move databases by using .bak files, but I fear someone used the import/export option, hence losing the PK/FK constraints.
share|improve this question
1  
Which RDBMS? How did you transfer the schema? How did you transfer the data? –  Mark Storey-Smith Feb 19 '13 at 9:09
    
I'll update the question. –  Flater Feb 19 '13 at 9:10
    
Are permissions ok? The users were imported successfully? Better try to do a db compare using some tool, than just script everything again. There's Visual Studio, RedGate DB Compare, and many others. –  Marian Feb 19 '13 at 9:57
2  
Stop modifying your database directly. Apply all changes through scripts under source control. This way you can deploy a new database by running the scripts. –  Remus Rusanu Feb 19 '13 at 15:31
    
We're not using Ruby on Rails, and the only reason we are modifying is because an error occurred in a production database that was unable to be backed up for a short time, and we want to keep the data. If it can't be done, so be it, but 'you should have used this program' is not an answer to the question I'm asking. –  Flater Feb 20 '13 at 8:59

1 Answer 1

up vote 2 down vote accepted

Script out the constraints from the staging database and apply to the problem database. Lots of options, just make sure you have backups of staging and prod before you start:

  1. Manually, one by one. Right click the objects in SSMS and opting to 'Script'.
  2. Manually, all together (with a bit of editing). Right click database in SSMS and Tasks / Generate Scripts / Select 'Tables'. Under Scripting Options / Advanced include PK/FK. The script created will include table definitions, you'll have to edit to leave just PK/FK.
  3. Use a schema comparison tool to sync staging to production.
  4. Use a script to generate the ALTER statements for you.

    SELECT
        *
        , 'ALTER TABLE ['+ FKTableSchema +'].[' + FKTableName + '] DROP CONSTRAINT ' 
            + ForeignKeyName AS FKDrop
        , 'ALTER TABLE ['+ FKTableSchema +'].[' +FKTableName + '] ADD CONSTRAINT ' 
            + ForeignKeyName + ' FOREIGN KEY(' +
          FKColumnList +') REFERENCES ['+ PKTableSchema+'].['
            + PKTableName + '] ('+PKColumnList +')' AS FKCreate
        , 'ALTER TABLE ['+ PKTableSchema +'].[' +PKTableName + '] DROP CONSTRAINT ' 
            + PrimaryKeyName AS PKDrop
        , 'ALTER TABLE ['+PKTableSchema +'].[' +PKTableName + '] ADD CONSTRAINT ' 
            + PrimaryKeyName + ' PRIMARY KEY('+PKColumnList+')' AS PKCreate
    FROM
        (
        SELECT DISTINCT
            FKTableSchema = 
                (
                SELECT DISTINCT 
                    table_schema 
                FROM 
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE 
                    constraint_name=rc.constraint_Name
                ),
            FKTableName = 
                (
                SELECT DISTINCT 
                    table_Name 
                FROM 
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE 
                    constraint_name=rc.constraint_Name
                    ),
            rc.constraint_name AS ForeignKeyName,
            FKColumnList = 
                (
                SELECT 
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList' 
                FROM
                    (
                    SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE constraint_name=rc.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                ),
            cu.table_schema AS PKTableSchema,
            cu.table_name AS PKTableName,
            cu.constraint_Name AS PrimaryKeyName,
            PKColumnList = 
                (
                SELECT 
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList' 
                FROM
                    (
                    SELECT 
                        Column_Name + ',' 
                    FROM 
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE 
                        constraint_name=cu.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                )
        FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
        INNER JOIN
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
        ON  rc.Unique_Constraint_name= cu.Constraint_name
    ) AS tab
share|improve this answer
    
Thank you for the complete answer :) I'll be able to test your resolution in a few hours, will let you know if it worked. –  Flater Feb 19 '13 at 10:41
    
I checked the script you added, thank you for that! One remark however, it seems this only generates PK statements for the keys that have a FK connected to it? Is there any way to generate the other PK's? –  Flater Feb 19 '13 at 11:50
    
The customer agreed to restore an older backup due to time restraints. But really thanks for your help in this! If you could still give me an answer on how I can get all PK's, that would be great... :-) –  Flater Feb 20 '13 at 9:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.