You can use SQL Source Control and SQL Compare to deploy a database from source control to a server.

You can also use the SQL Server Management Studio Integration Pack add-in to make schema and data deployment simpler.

Deploying with SQL Server Management Studio Integration Pack

To deploy a database schema, in the Object Explorer, right-click a database, select Schema Compare/Deploy, and click Set as Source.

The SQL Server Management Studio Integration Pack Schema Compare/Deploy tab is displayed:

You can deploy the current database version or specify a version from source control.

You can deploy to a target database, create a new database, or create a change script to update a target source control version.

For more information, see Getting started with the SQL Compare add-in.

Deploying without SQL Server Management Studio Integration Pack

To deploy a database:

1. Create a local copy of the scripts folder
2. Migrate the local copy to the target server using SQL Compare

Optionally, you can also deploy any relevant static data using SQL Data Compare.

In this example the database WidgetDev is already in source control.

The example uses the SQL Compare and Subversion command line interfaces.

You can also deploy the database using the SQL Compare graphical user interface and a source control client like TortoiseSVN.

1. Create a local copy of the database

At a command prompt, type:

cd C:\program files\subversion\bin

svn update http://<your repository path>/WidgetDev "C:\WidgetDevScripts"

Where:

• http://<your repository path>/WidgetDev is the URL for the database in your Subversion repository
• "C:\WidgetDevScripts" is the file path for the directory where the local copy will be created

A local copy of the scripts folder is created. This is a Subversion working copy, and is associated with the Subversion repository.

2. Migrate the local copy to the target server

At a command prompt, type:

cd C:\program files\red gate\SQL Compare 8

     /sqlcompare /scr1:"C:\WidgetDevScripts"

     /S2:WidgetServer /U2:<username> /P2:<password>

     /db2:"WidgetTest"

     /sync

Where:

• /scr1:"C:\WidgetDevScripts" specifies the local copy, WidgetDevScripts, as the source for a SQL Compare deployment
• /S2:WidgetServer /U2:<username> /P2:<password> specify the server, user name, and password you are using
• /db2:WidgetTest specifies WidgetTest as the target of a SQL Compare deployment
• /sync performs the SQL Compare deployment, making the schema of WidgetTest the same as the schema in WidgetDevScripts

The database is updated. Its schema is now the same as the version you checked out of source control.

For more information, see Simple examples using the command line.