5

Suppose you have a database project and you do NOT have "Always re-create database" checked off in your Database.sqldeployment settings. And suppose you deploy to a server that already has a database by the name of the one you are deploying.

Under what other circumstances will the database deploy generate a script with a "DROP DATABASE" statement?

If you don't ever, ever, ever want your database to be dropped by the deployment script generated by right clicking your database project and selecting "Deploy", what are some of the steps you can take to prevent this?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Mike Suarez
  • 162
  • 1
  • 8
  • Any chance you could post the project and settings files at all? AFAIK the option you mentioned is the only one that would cause a DROP DATABASE to be scripted. – Dan Nolan Sep 11 '12 at 01:21
  • Unfortunately, not, because the project belongs to the customer. Also, I can't even reproduce this. The project now generates the script without the drop. I am trying to figure out what might have caused it to generate the script with a drop previously. Frank, below, had a good thought about the Target Connection. Even though the target connection was set, it's possible that it was set only in my project settings, and not in my isolated environment settings. I also considered that maybe having a target connection defined, but not being VPN'd could have caused it. idk. – Mike Suarez Sep 11 '12 at 20:01
  • Ah that makes sense. Yeah the isolated dev vs project settings can be a bit confusing. Glad they ditched that idea in SSDT. Do you recall if the deployment done interactively, or did a build server run it for you? If it was the latter then there might be something helpful captured in the build log. – Dan Nolan Sep 12 '12 at 05:53
  • Deploy was run manually in VS. And I opted to "Generate script" only.. I opened up the script in SSMS and that's when i saw the drop statement. Ironically, I prefer doing "generate script" because I feel its safer than letting VS generate and run the script for you. However, if it was a matter of not having Target Connection set properly, then running directly from VS would fail because it wouldn't connect, which is much safer than assuming the database is not there and generating a drop for it. – Mike Suarez Sep 12 '12 at 17:41

1 Answers1

3

In addition to the "Always re-create database" NOT being checked off, you should also check the Development tab on your database project's Properties page. Make sure you define a target connection. When you don't define one the project will always and only deploy as-if the target database does not exist. This behavior is by design. see this link for more details.

My suggestion is to create the connection using Windows Authentication so each user would have access to the extend they are supposed to.

Also please note that you will have to do this for each Deployment Configuration (e.g. Debug, Release, etc.)

I personally set the deploy action to just create a script and run it manually to be on the safe side!

Frank Goortani
  • 1,407
  • 17
  • 26
  • 1
    Thank You Frank. As it turns out, I did have a Target Connection defined for that project configuration, it was a valid connection string, and I did set the deploy action to just generate the script so I could run manually from SSMS. Yet the script still had a Drop statement in it. You make some great suggestions, and even after reading through the great link you provided, I still feel like there may still be some other reason that my deployment script generated a drop database. – Mike Suarez Sep 06 '12 at 19:18