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

By:   |   Comments (11)   |   Related: > DevOps


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.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




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

 Hello Greg,

Thank you so very much.

Xavier

 

 

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

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 (55129)

 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 (46293)

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

 

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

 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 (26378)

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 (26325)

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 9, 2013 - 1:39:05 PM - Jeremy Kadlec Back To Top (26193)

Rick,

Congrats on your first tip!  Welcome to the community!

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, August 7, 2013 - 8:24:53 AM - Giedrius Back To Top (26131)

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 5, 2013 - 11:54:58 AM - Rick Krueger Back To Top (26108)

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 5, 2013 - 10:35:30 AM - Jeff Back To Top (26107)

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.














get free sql tips
agree to terms