How to update sql database during deployment, not create new

Question

I have a Visual Studio 2010 website project that I'm creating a Deployment Package for. My website relies on a SQL database that I also include in the deployment package. Importing the deployed package in IIS on the production server worked great during the first deployment, when there wasn't already a database. For the second+ deployments, I get an error during the import that says there is already an object named such and such. It looks like it's trying to create the database again instead of updating the database schema since the last deployment.

I'm creating the deployment package and copying the zip to the production server and importing directly into IIS via WebDeploy 2.1. How can I just update the database? I tried having a Visual Studio sql database project and having the package include just the generated .sql file from the "Deploy" of that project, but was meet with other un-related problems. It just can't be this difficult. How should I deploy database schema updates?

Answer 1

You'll need to deploy database changes using custom scripts (if you want to preserve data).

Or, if you just want to drop and recreate certain object you can do this using automated scripts.

Either way, it's not as easy as pushing out the original database.

If you dont have database changes you can exclude the database from the deploy (Package/Publish Web tab - untick database).

Please see the following article http://msdn.microsoft.com/en-us/library/dd465343.aspx

Pay special attention to the "Redeploying By Using Automatically Generated Scripts" and "Deploying Database Changes by Using Custom Scripts" sections.