Pre-Deployment in Database project

Question

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.

Answer 1

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.