locked
How to generate incremental sql scripts from Ms SQL Server 2008 RRS feed

  • Question

  • We have requirement to generate db script for subsequent release.

    1.    SQL script should be incremental

    2.    SQL script can be checked into TFS

    3.    TFS should not directly communicate with database. A db project should be fine

    4.    In case of alter table, No table drop is permissible. Only alter statement is permissible

     

    I am trying to generate via vsdbcmd command. But the problem is it drops all database objects and recreates them. I am looking for just an alter statement which can reviewed by implementer or DBA before executing on target server  

     

    Step 1: Generate schema for source data base

    vsdbcmd.exe /a:Import /cs:"Server=PhysicalBoxname_Source;Integrated Security=true;Pooling=false;Initial Catalog=xxx;" /dsp:Sql  /model:"xxx_source.dbschema"

     

    Step 2: Generate schema for destination data base

    vsdbcmd.exe /a:Import /cs:"Server=PhysicalBoxname_target;Integrated Security=true;Pooling=false;Initial Catalog=xxx;" /dsp:Sql  /model:"xxx_target.dbschema"

     

    Step 3: Generate schema for differential scripts

    vsdbcmd.exe /a:deploy /dd:- /dsp:sql /model:xxx_source.dbschema /targetmodelfile:xxx_target.dbschema /DeploymentScriptFile:Incremental.sql /p:IgnoreIncrement=true /p:TargetDatabase="xxx"  

     

    Tuesday, December 20, 2011 11:18 AM

Answers

  • Hi,

    I accomplished something similar to the incremental scripts by keeping the production version of the database in the trunk of the source control and the the development changes in a branch.  Then I compare the production version with the development version to get the delta script.  Granted there would only be one change script instead of incremental ones but it has worked good.  Here's a post I did with the vsdbcmd statements.

     

    http://www.codesmartnothard.com/2011/10/31/ImplementingContinuousIntegrationCIAndDeliveryForSQLServerDatabases.aspx

    I hope this helps.  Let me know if you have any questions about it.

    Mike


    blog - http://www.codesmartnothard.com
    Wednesday, December 21, 2011 12:53 PM

All replies

  • Hi Shanir, 

     

    Thanks for your post.

     

    What’s the version of your TFS?

     

    We can use TFS Source Control to control sql script and database project. And we can execute command line during Team Build, for Team Build 2010, we can use InvokeProcess build activity in build process template to execute your vsdbcmd command. For more information about InvokeProcess build activity, please refer to: http://msdn.microsoft.com/en-us/library/gg265783.aspx#Activity_InvokeProcess.   

     

    For Team Build 2008, use the MSBuild exec task in TFSBuild.proj file to invoke your vsdbcmd command, please refer to this article(invoke devenv.exe command TFSBuild.proj): http://msdn.microsoft.com/en-us/library/ms404859(VS.90).aspx.   


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 10:10 AM
    Moderator
  • Thanks John, I am using TFS 2010. Is there any possibility to generate differential sql scripts from database or db project or last labeled code from db project? we don't want scripts with drop and re-create statement. Problem I am facing with vsdbcmd is that it drops all tables from database and re-create. Apperciate if you can provide me any work around to create differential scripts.
    Shanir Anshul
    Wednesday, December 21, 2011 10:53 AM
  • Hi,

    I accomplished something similar to the incremental scripts by keeping the production version of the database in the trunk of the source control and the the development changes in a branch.  Then I compare the production version with the development version to get the delta script.  Granted there would only be one change script instead of incremental ones but it has worked good.  Here's a post I did with the vsdbcmd statements.

     

    http://www.codesmartnothard.com/2011/10/31/ImplementingContinuousIntegrationCIAndDeliveryForSQLServerDatabases.aspx

    I hope this helps.  Let me know if you have any questions about it.

    Mike


    blog - http://www.codesmartnothard.com
    Wednesday, December 21, 2011 12:53 PM