Post reply

Moving Test DB to Live Environment

  • November 18, 2009 at 8:51 am

    #140043

    I have a test database that I have made changes to and is ready for deployment into live. What's the best way of doing this, while still retaining all the data in live?

    For example:

    1) backup live database and restore to a temporary area

    2) generate and execute scripts from test database to create new live empty database

    3) develop SSIS package to populate the new structure with live data from the temporary copy of the live database?

    Any ideas to simplify this process would be apprciated.

    Thanks,

    Steve

  • November 18, 2009 at 9:11 am

    #1080979

    What are the deltas between qa and prod? (Proc, data, views)?

  • November 18, 2009 at 9:20 am

    #1080988

    Tables and sp's.

  • November 19, 2009 at 6:41 am

    #1081510

    Another option is do buy one of the comparison tools: RedGate SQLCompare, ApexSQLCompare, xSQLCompare. These do a schema comparison and will make the changes or generate the scripts necessary to make the changes so you can review it and do it manually.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 19, 2009 at 6:56 am

    #1081521

    I assume these tools actually create a series of ALTER TABLE / VIEW commands to be run over the live database, as I notice the "Script Table As - Alter To" option is greyed out in SQL for the table / view objects (do you know when these would be enabled by the way?)

    So without these tools, the way forward i outlined above would be a good way to proceed?

    Thanks.

  • November 19, 2009 at 7:06 am

    #1081531

    I'd really recommend spending a couple hundred $'s on one of the tools. This is likely something you'll be doing on a regular basis and the tools also help you find all changes and dependencies.

    Now to answer your question. If you are only adding columns,etc... I would script the changes, ALTER TABLE ADD column_name defintion and ALTER Procedure scripts and apply them to the live database after having backed up the database. The only issue with this will be if you need data in the new columns.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 20, 2009 at 2:33 am

    #1082039

    Personally I use Red Gate SQL Compare for the Schema Changes. I also use Red Gate SQL Data Compare once the schema changes have been loaded to add / update any referential changes that the new schema needs to allow the application(s) to function.

  • November 20, 2009 at 3:18 am

    #1082065

    First thing is what are the changes made in test environment

    1.Like DDL changes (alter,create etc)or even DML changes(insert,update and delete to existing tables).

    2.If only DDL changes, it shouldn't really matter, a script can be prepared and deployed.

    3.If both DDL and DML, then may be you need to go for a tool!

    Have safe backups before implementing any changes.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply