locked
genearating schema comparison script automatically RRS feed

  • Question

  • Hi there,

    I am working on one click publish thing in visual studio. In that I want to include Publish SQL as well. This is for deploying changes of schema without droping and creating tables and procedures again to save the data on production server.Auto script schema in Package/Publish SQL tab supports only initial deployment when you want to create everything from the scratch . To deploy changes to the schema I need to create a custom script.

    Visual studio has got comparision tool in which we can compare two databases and export the script . Is there any way so that it will automatic refresh the schema comparison and changes the script which I can use in Package/Publish SQL tab.

    Or if anyone knows a better way to create a custom schema comparison script automatically.

    Any help would be appriciated. I need to develop this urgently.

    Thanks

    MMC


    MMC


    • Edited by MMC123 Tuesday, March 19, 2013 4:56 AM
    Tuesday, March 19, 2013 4:33 AM

Answers

  • Hi MMC123,

    You can edit the project file to set the ScriptDropsFirst attribute of the PreSource element of the automatically generated script to True to redeploy a database by using automatically generated scripts, just refer to http://msdn.microsoft.com/en-us/library/dd465343(v=vs.100).aspx and http://msdn.microsoft.com/en-us/library/ff398069(v=vs.100).aspx. But this method may cause data loss, as it simply drop & recreate the database objects. If you don't care about data loss, you can do it like this.

    Also I will provide you another method. Just use SQL->Schema Compare, set the source database and target database, and click compare, then update will become avaiable.  You acn click on it to apply database changes or click Generate Script to get the T-SQL script, then add the script to  in the Databse scripts section in Package/Publish SQL will apply database changes after next publish.

    I hope my understanding to your problem is correct and could help you solve the problem.

    Best Regards,

    Louis

    Thursday, March 28, 2013 5:52 AM

All replies

  • Not sure what version you're on, but I think you either want to:

    use vsdbcmd to deploy a .dbschema file

    use sqlpackage.exe to deploy a .dacpac file



    (Twitter | Blog)

    Tuesday, March 19, 2013 2:21 PM
  • I am using visual studio 2012 and sql server 2008 R2.

    I am not sure if you understood my question. I want to use Package/Publish SQL tab in web application project's property.


    MMC

    Tuesday, March 19, 2013 9:48 PM
  • Below is the screenshot of the peroperty page of web application project in visual studio 2012


    MMC

    Tuesday, March 19, 2013 10:34 PM
  • Hi,

    Could you see this MSDN library? It shared the functions of Package/Publish SQL tab.

    How to: Deploy a Database With a Web Application Project.

    Thanks,

    Wednesday, March 20, 2013 10:55 AM
  • Hi Xin Jin,

    Thanks for you reply. I already had gone through this post. It doesn't include how I can create custom script to deploy changes to a database and preserve data.

    After deploying the database for the first time when I want to deploy changes to a database like (Add new rows in the table/ chnage the stored proc/ add new stored proc) and preserve the data I will need a custom script to mention in the Databse scripts section in package/Publish SQL. Project I am working on gets changes in the database almost everyday so I want to know if there is any way which creates custom script according to changes done to the database in daily basis. I don't want to create a custom script everyday to deploy changes.

    Please let me know if you are clear with the question.

    Thanks,


    MMC

    Wednesday, March 20, 2013 11:38 PM
  • Hi MMC,

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Best Regards,


    Jack Zhai [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 21, 2013 9:46 AM
    Moderator
  • Hi MMC123,

    You can edit the project file to set the ScriptDropsFirst attribute of the PreSource element of the automatically generated script to True to redeploy a database by using automatically generated scripts, just refer to http://msdn.microsoft.com/en-us/library/dd465343(v=vs.100).aspx and http://msdn.microsoft.com/en-us/library/ff398069(v=vs.100).aspx. But this method may cause data loss, as it simply drop & recreate the database objects. If you don't care about data loss, you can do it like this.

    Also I will provide you another method. Just use SQL->Schema Compare, set the source database and target database, and click compare, then update will become avaiable.  You acn click on it to apply database changes or click Generate Script to get the T-SQL script, then add the script to  in the Databse scripts section in Package/Publish SQL will apply database changes after next publish.

    I hope my understanding to your problem is correct and could help you solve the problem.

    Best Regards,

    Louis

    Thursday, March 28, 2013 5:52 AM
  • Hi Liu,

    Thanks for your reply.

    I don't want to lose data because destination database has a real data.

    I already know about SQL->Schema Compare tool . To use that I have to perform compare and genrate script by clicking a provided button of generate script. In my project I have to publish database schema almost everyday because I get changes every now and then in the database. So I wanted to avoid comparing and generating script everyday and was after some way that refreshes schema comparision and generate script automatically (without performing any button click) and saves that script in a path provided in the add script section of Package/Publish SQL tab. But it seems I have to run SQL->Schema Compare and update the script everytime I want to publish the schema change.

    Thanks for you help.


    MMC

    Monday, April 1, 2013 10:59 PM