Click here to Skip to main content
15,742,357 members
Articles / Database Development
Posted 23 Jan 2011


9 bookmarked

Manage SQL Server Database

Rate me:
Please Sign up or sign in to vote.
4.60/5 (4 votes)
23 Jan 2011CPOL3 min read
Manage SQL Server Database within Visual Studio Team System


In this article, I am going to show how VSTS is useful for database developers. Note that I do not have a full idea about all things, but it’s very helpful to a DBA and developer who is working on a large project with a large number of team members. Sometimes, when we release our project/product to client, problems arise that our dataset version does not match with the current application version. There is always a problem when there are a number of people working on the same project and updating database object frequently and we miss updated object at the time of release. But now with the help of the VS team system 2008, we can easily resolve this issue and keep the database changes in VSS. In this article, I am going to show how to create Database project and the option you get after creating the database. You get the following advantages:

  • Manage DB change through schema management "One version of the truth" Offline sandbox for multiple developers
  • Source control for DBs ability to store different versions as change sets
  • Schema and data compare Generate scripts/apply updates

Start with Create DataBase Project:

  1. SQL Server 2000
  2. SQL Server 2000 Wizard
  3. SQL Server 2005
  4. SQL Server 2005 Wizard
Image 1

Sql Server 2005 Wizard

Select database instance you installed on your machine or from your network.

Image 2

Welcome note by wizard

Image 3

Select your schema or the object type

A database schema is a way to logically group objects such as tables, views, stored procedures, etc. Think of a schema as a container of objects. You can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Image 4

Select database collation and some other options

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

  • Each SQL Server collation specifies three properties:
    • The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
    • The sort order to use for non-Unicode character data types (char, varchar, and text).
    • The code page used to store non-Unicode character data.
Image 5

Create new database or import database schema from an existing one by selecting from combo box. Choose important options according to the needs:

Image 6

Provide information to connect with the database

Image 7

Build and deploy Configuration

Image 8
Image 9

Once you are done with the database creation, project gets created with the folder you see in below screen:

  • Stored Procedures
  • Functions
  • Triggers
  • Arbitrary SQL
Image 10
You can find the below options which allow you to compare data or schema of the database.
  • Allows comparisons of: Project -> database; database -> database
  • Object level script difference between DBs
  • Notifies when data loss may occur
  • Generate script or apply changes directly
  • It’s smart! Understands constraints, creates temp tables to hold data, more
  • Compare security settings Users, roles and permissions
Image 11
Image 12

After you are done with adding and changing database object, you can build and deploy project as you can see in the below project:

Image 13
Image 14


Database project and the related utilities to support it by VSTS are very important, save time and make database maintainable.


  • 23rd January, 2011: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
India India

Microsoft C# MVP (12-13)

Hey, I am Pranay Rana, working as a Team Leadin MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.

For me def. of programming is : Programming is something that you do once and that get used by multiple for many years

You can visit my blog

StackOverFlow -
My CV :-


Comments and Discussions

GeneralMy Vote of 5 Pin
RaviRanjanKr23-Jan-11 22:56
professionalRaviRanjanKr23-Jan-11 22:56 
GeneralRe: My Vote of 5 Pin
Pranay Rana23-Jan-11 23:40
professionalPranay Rana23-Jan-11 23:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.