Deploy SQL Server Schema Changes using SQLCMD

tags:

sql-server

views:

502

answers:

+1 Q:

Deploy SQL Server Schema Changes using SQLCMD

I'm using Visual Studio Team System 2008's Database tools to develop my databases. On my local dev machine, when I want to deploy schema changes to the SQL Server instance on my machine, I just use the Data --> Schema Compare feature of VS2008.

But with live databases I can't do this because I can't connect to the database directly from my machine and the server haven't got VS2008 installed.

So I was thinking about the SQLCMD tool. Isn't that what VS2008 uses "under the hood"?

I want to use as part of an automatic deployment strategy. I want to be able to publish SQL scripts generated by VS2008 to the server and have an application run scripts on the live database to update the schema.

UPDATE

I'm trying to achieve automatic change script generation by taking the deploy script VS2008 Database Edition generates and comparing it against a live database. Only I want to do it through code, no tool or anything. It must be able to run from a Windows Service on the server.

SqlCmd would work. If you also want automatic versioning support you should check out Tarantino.Net, a database management tool that keeps track of which sql-files have already been run.

Akselsson 2009-05-25 22:24:34

Do you know how to use SQLCMD to update the schema like VS2008 does?

MartinHN 2009-05-25 23:23:51

You need only run the script using SQLCMD - the script is what actually "updates" the schema. Here's an intro to SQLCMD:http://msdn.microsoft.com/en-us/library/ms170207.aspx

Aaron Alton 2009-05-26 03:31:11

Yes I'm aware of that ;). But running the deployment script deletes the whole database and re-creates it with the new schema. It must do a comparison to see what the differences are, generate an update script which will update the database schema.

MartinHN 2009-05-26 04:47:44

There is a tool from Red Gate called SqlCompare that does just that: http://www.red-gate.com/products/SQL_Compare/index.htm. Another option is to use the "Generate Change Scripts" feature of Sql Server each time you make a change to the database schema.

Akselsson 2009-05-26 05:19:03

I updated my question, since it was rather un-clear what my question was.

MartinHN 2009-05-26 05:34:45

ansaurus

tags:

views:

answers:

Deploy SQL Server Schema Changes using SQLCMD

related questions