views:

531

answers:

2

Visual Studio 2008 Database project for SQL Server 2008

The project has placeholders for Pre-Deployment and Post-Deployment SQL scripts. They work - well, most of the time anyways.

The project has an option to Always-Recreate-Database: drop the existing database and create a fresh hot-from-the-oven database every time.

When I deploy my database the entire SQL Script is put together and executed.

My database is used for replication, and as a part of the Post-Deployment script, I designate the server as a distribution, create the replication and add articles to it.

Therefore, I have to shut off replication. And the logical place to put that was in the Pre-Deploy.

VS2008 wiped the smug grin off my face pretty quickly. If Always-Recreate-Database is checked then it puts the script to drop and recreate the database, then puts my Pre-Deployment script, and then everything else.

Is there any way for me to change the template of the database project so that the Pre-Deployment SQL scripts are executed where they are meant to execute - before any deployment occurs.

+1  A: 

This might not be exactly what you're after but it might help you to work around your problem. after a quick look, I think the sequencing of the pre- and post- deployment scripts might be too difficult to change.

As I understand it, there are some hooks in the build project that will allow you to execute your own code before the deployment begins.

  1. Define a PreDeployEvent property in your .dbproj file.
  2. Define a BeforeDeploy target in your .dbproj file.

Either of these should be executed at the right point in time, I think.

If you use the PreDeployEvent property you'll need to specify the single command line to be executed. A crude example:

<PropertyGroup>
  <PreDeployEvent>sqlcmd.exe -i myscript.sql</PreDeployEvent>
</PropertyGroup>

If you want more control, use the BeforeDeploy target which will allow you to run one or more custom msbuild tasks. Here's another crude example:

<Target Name="BeforeDeploy">
  <Message Text="BeforeDeploy" Importance="high" />
</Target>

By the way, there are plenty of custom tasks freely available, one example being those at www.msbuildextensionpack.com.

Dave Cluderay
I tried putting the PropertyGroup in all sorts of places in the project, but nothing has worked so far. Is there a sample you know of that I can play with?
Raj More
http://pastie.org/575352 - the first few lines should be enough.Check the Output window after a deploy - on my VSTS2008 I get output saying the database exists before and after deploy (indicating that the command runs before the DB is dropped and recreated).
Dave Cluderay
A: 

Try also to give a look to this article Pre deployment scripts

Alkampfer