Visual Studio 2010 Database Projects

I’ve never used a database project in a Visual Studio solution before. In most of the places I’ve worked, the database is managed by full time DBA’s, who deal in sql scripts taylored to make specific schema updates.

So i thought I would have a look. BLARG.

How about this gem in the generated deployment script? (And yes, the option to recreate the database was turned off now that you ask…)

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON
    PRIMARY(NAME = [Bonky], FILENAME = N'$(DefaultDataPath)Bonky.mdf')
    LOG ON (NAME = [Bonky_log], FILENAME = N'$(DefaultLogPath)Bonky_log.ldf')
    COLLATE Latin1_General_CI_AS
GO

Or the that changing the deployment Action to “create script and Deploy” installs the schema in the Master catalog? (and yes another database was selected in the “target database name” – it overwrote that silently when the deployment ran)

Ok the last issue does pop up a dialog, saying it will deploy to master at some point, but after changing the deployment target you would think…

Nah.

[UPDATE]

Ok not as bad as it first looked – the generated deploy script is isnsitive to the target – i.e. it only generates the update – so when I changed the databsae name from the one I was working with to the production one which it didn’t have access too it generated the DB drop/create statements. Pretty unpleasant but not as quite as illogical.

Pointing it at the dev database (which is up to date) generates a deploy script that does nothing apart from check the satate of the DB.

[UPDATE 2]

No this tool does not work for me. Next issue was – check solution into version control, drag out on another machine and use the “Database Projec”t to build a deployment script. Not sure what happened, but the script it built was missing more than half the tables. The individual scripts were still on the project but were not added to the new database. A “Visual Studio” schema compare between the project and the generated db was able to add the missing tables and indexes etc. but Just Not Good Enough.

I might shell out for Redgate tools that I have used before and been completely happy with.