SQL Server Deploy Script

I use sql server 2005 for an asp.net project. I want to run a SQL file that contains all DB changes from the last release to easily bring a DB up to the latest version.

I basically just have a bunch of alter table, create table, create index, alter view, call stored proc, etc statements. But I开发者_JAVA百科 would like to wrap it in a transaction so if any part of it fails, none of the changes will go through. Otherwise it could make for some really messy debugging where it finished.

Also, if you know of a better way to manage DB deployment let me know!

I do something similar with a Powershell script using SMO.

Pseudocode would be:

$SDB = SourceDBObject
$TDB = TargetDBObject

ForEach $table in $SDB.Tables
{
   Add an entry to a hash table with the name 
   and some attributes (rowcount, columns, datasize)
}

# Same thing for $TDB

# Compare the two arrays, and make a list of all the ones that exist in the source but not in the target, or that are different

# Same thing for Procs and Views

# Pass this list to a SMO.Scripter as an UrnCollection object, and it will script them out in dependency order (it's an option), with drops

# Wrap the script in a transaction and execute it on target server

# Use SQLBulkCopy class to  transfer data server-to-server

What version of Visual Studio do you use? In Visual Studio 2010 and from what I can remember Visual Studio 2008 - in the menu under "Data" there are two options - "Schema Compare" and "Data Compare". That should move you in the right direction.

BEGIN TRANSACTION @TranName;
USE AdventureWorks2008R2;
DELETE FROM AdventureWorks2008R2.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;

COMMIT TRANSACTION @TranName;

You should execute everything within a transaction

Note that some DDL statements have to be the first statement in a batch (batches are separate from transactions). (GO is the default batch separator in SSMS and SQLCMD).