Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a Visual Studio Database Project for an existing SQL Server Database


By:   |   Read Comments (11)   |   Related Tips: More > Source Control

Attend these FREE MSSQLTips webcasts >> click to register


Problem

Continuous Integration has become standard practice for many development projects. Every time a developer checks in a piece of code, the entire project or solution is built and deployed to an environment to make sure it didn't 'break the build'. Developers have been using source control for a long time, but there just wasn't a good way to get database objects under source control. As a result, there are many applications out there with the 'application code' in source control, but the database code is unmanaged. The preferred route for getting the database into source control at many shops will be Database Projects. If you are in this position, and your shop is using Visual Studio, there is a relatively easy path to get your databases into Database Projects, and ultimately source control. 'Reverse engineering' your database into a Database Project will help you start leveraging the features of Visual Studio Database Projects available in VS Premium and VS Ultimate (including source control, continuous integration, and code analysis).

Solution

We can use Visual Studio to run the Import Database Wizard and populate an empty Database Project.

Download the sample AdventureWorks database (AdventureWorks2008_Database.zip was used for this example) from CodePlex, and attach the AdventureWorks2008 database to follow along with this tip.

NOTE: The screenshots are from Visual Studio 2010, but the same general process will work with Database Projects in Visual Studio 2005/08. Visual Studio 2005/08/10 all use VSDBCMD.exe for deploying Database Projects, while 2012 went to a new method for deployment.

Create an Empty Database Project

  1. Open Visual Studio 2010.
  2. From the File Menu, select New > Project...
  3. In the Installed Templates tab, expand Database > SQL Server > Advanced
  4. Select the SQL Server 2008 Database Project, and enter the Name of your database

    Open Visual Studio 2010

  5. Click OK and an empty Database Project will be created.

Run the Import Database Wizard

  1. From the Project Menu, select Import Database Objects and Settings...
  2. Click New Connection...
  3. Select your local Server name, credentials, and database name. TIP: Click Test Connection here before clicking OK.

    Run the Import Database Wizard

  4. Click Start. I recommend keeping the provided defaults, unless you understand the implications of not accepting the defaults.
  5. You will see a progress bar as Visual Studio inspects the selected database, and populates the Database Project with all of the database objects.
  6. Click Finish.

    select Import Database Objects and Settings...

Next Steps

Congratulations! Your database has been imported into a Database Project, and you should now start managing your database from Visual Studio and make sure you get source control set up. You will also want to learn how to deploy those changes from Visual Studio to SQL Server. I have been using Database Projects for a long time now, and I haven't come across many limitations. There are times I still prefer to write my code in SQL Server Management Studio, and then import the code into Visual Studio, but that is mostly a personal preference.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Rick Krueger Rick Krueger is a Lead Development DBA, BI Developer, and speaker with 18 years of experience, based in the Twin Cities.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, April 24, 2017 - 8:32:10 AM - Xavier Back To Top

 Hello Greg,

Thank you so very much.

Xavier

 

 


Monday, April 24, 2017 - 8:07:03 AM - Greg Robidoux Back To Top

Hi Xavier,

check out this link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

-Greg


Monday, April 24, 2017 - 7:49:05 AM - Xavier Back To Top

 Rick,

Thank you so very much for your presentation.   I have used MS-SQL before but as a DBA for many other SQL based databases.  Is there a link I can download a single version of SQL Server Management Studio?

 

Thank you so very much.

Xavier

 

 


Monday, February 13, 2017 - 3:42:39 PM - Shawn Back To Top

Thank you. Quick easy explantion of what I was looking to do. 

 


Monday, December 12, 2016 - 5:30:32 AM - maher Back To Top

 OK...

so How to deploy SQL database after importing..

to be installed on the clinet pc sql server 2008 R2

with login and symatric key and encryption

Im trying meny time for nothing

 


Monday, August 19, 2013 - 8:25:25 AM - Rick Krueger Back To Top

Jeff, since the test connection succeeds, I would be very surprised if it's a firewall issue. Which version of SQL Server are you connecting to, and which version of Visual Studio (including service pack level) are you using?


Thursday, August 15, 2013 - 11:31:17 AM - Jeff Back To Top

I get a connection succeeded when I test the connection.  But when I actually run it, it says "Could not connect to the database to retrieve the configuration.

When I look at the firewall that sits between our workstations and servers, I can see that there is no traffic from that machine to the server.  So I think it is the windows firewall on the machine. Is there anything I have to open up on the windows firewall on the computer?


Friday, August 09, 2013 - 1:39:05 PM - Jeremy Kadlec Back To Top

Rick,

Congrats on your first tip!  Welcome to the community!

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, August 07, 2013 - 8:24:53 AM - Giedrius Back To Top

Be careful with "Database settings". If You will not check "Import database settings" (which is off by default), then project will get default settings (Recovery mode, Local/Global Cursors etc.). And if You will deploy/publish Your database project on same database later, then it can change settings of real database. Unless You will set do not deploy database settings (don't remember where You are able to set it)


Monday, August 05, 2013 - 11:54:58 AM - Rick Krueger Back To Top

What happened when you clicked Test Connection in Run the Import Database Wizard: Step 3? If you connected successfully, but it failed in Step 4, you probably don't have the appropriate permissions (VIEW DEFINITION) to read the schema of the database you are connecting to.


Monday, August 05, 2013 - 10:35:30 AM - Jeff Back To Top

I tried this, but we get a "Could not connect to the database to retrieve the configuration."  We can create connections to this database in Visual Studio and those work fine.  Any ideas?

Thanks.


Learn more about SQL Server tools