In this article, we are going to learn about SQL Server Database Project (Template) available in Visual Studio. I will cover the following points,

  • Introduction of SQL Server Database Project.
  • Create New SQL Server Database Project.
  • Import database schema from an existing database, a .sql script file or a Data-tier application (.bacpac) the Project.
  • Publish to create new Database in SQL server.

The database plays a most important role in any application and it becomes difficult to manage the project when the number of tables, views, stored procedures increase. 

Consider scenarios where multiple developers are working on a project for the next release, some are working on bugs or adding new features which again requires some or many changes in the database. Most of the time developers take note of DB (Database) changes manually. Sometimes they miss some changes which cost in production. There are many cases that Dev, UAT and production DB are different which is again hassle to identify the differences. There are a number of tools available in the market for comparing DBs but are costly or paid solution. So, in this article we will discuss and learn about SQL Server Database Project with is available in Visual Studio which is free. Yes — Free!! 

Prerequisites Visual Studio (2013, 2015, 2017, or 2019) and MS SQL Server. I am using VS2019 and SQL Server 2017 Developer for illustration. 

Introduction

 You can create a new database project and import database schema from an existing database, a .sql script file or a Data-tier application (.bacpac). You can then invoke the same visual designer tools (Transact-SQL Editor, Table Designer) available for connected database development to make changes to the offline database project and publish the changes back to the production database. The changes can also be saved as a script to be published later. Using the Project Properties pane, you can change the target platform to different versions of SQL Server (including SQL Azure). (copied MS Docs)

Create New SQL Server Database Project

  1. Open Visual Studio and create a blank solution.

2. Add a Project.

3. Select SQL Server from Left panel and SQL Server Database Project. Give a name to the project (here, I am giving name as SampleAccount.). This template is used for creating a SQL server database.

Import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac) the Project

4. Next step is to import database scheme from an existing database. Import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac) the Project. So, right click on the project and select import. There will be three options: Data-Tier Application (.dacpac), Database, Script (.sql). Here, I am using the Database option because I will import from MS SQL database, as portrayed below.

5. Now you will see the screen as below. This option is available to provide connection string. There are options to select database from local, network or Azure, so based on your database you can choose the option. Alternatively, you can enter server name, authentication type and credentials to connect directly to database server. And then choose the database which you want to import and keep in your project.

6. Next, we have to set import settings. There are some options, 

  • Import application-scoped objects: this will import tables, views, stored procedures likewise objects.
  • Imports reference logins: login related imports.
  • Import Permissions: this will import related permissions.
  • Import database settings: this will import database settings.
  • Folder Structure: option to choose folder structure in your project for database objects.
  • Maximum files per folder: limit number files per folder.

In my case, I selected the following options as depicted.

7. Click Start which will show the progress window as:

After Finish we will see the tables, views and stored procedures in our project.

Finally, we have successfully created SQL server Database Project. Now we can publish and create new dataset from solution itself using this project. Now, I will show how to publish and create new database in SQL server from this SQL server Database Project. 

Publish to create new Database in SQL server

 We can easily create a new database directly from Visual Studio using the SQL Sever Database Project with the same schema. Let us proceed. 

  1. Right click on the project and choose the publish option.

2. Provide Connection: Server name, Authentication Type , credentials. If we want to publish as new database then choose default database, or choose specific database to publish.

3. We can generate script or publish directly. You can explore advance options as well to apply rules on publish.

Let’s explore advanced options with more rules and options. Options are: Deployment behaviors

  • Deploy database properties.
  • Always re-create database.
  • Block incremental deployment if data loss might occurs.
  • Execute deployment script in single-user mode.
  • Backup up database before deployment.
  • Do not user alter assembly statements to update CLR types.

Additionally, there are several advanced deployment options like: compare using target collation, disable and reenable DDL triggers, do not alter change data capture objects, do not alter replicated objects, drop statistics not source, include composite objects, include transactional scripts, populate files o fileGroups likewise several options as shown. Then, click ok in advanced options and publish the database.

Now our new database is created or generated script can used to create.

Now, your database is created in SQL server, you can login to SQL server and check as shown. Finally, we created a database from Visual Studio, SQL server Database project successfully.

Conclusion

 In this article, I have shown how to create SQL server database project in Visual Studio and import the sql database. We can import development database and finally publish into UAT, production or another server from Visual Studio itself with this project template. Additionally, we have explored some advanced options during imports and publishing of the database. There are some additional useful options or use cases we have with this project like to compare and identify the differences in two databases (development and production), then we can generate updated script or update directly from the project. I will explain more about comparing and updating in the next article.

By Rijwan Ansari

MVP, CSM, CSPO, MCP, MCTS. Sr. Software Developer. Expertise in ASP.NET MVC, Angular, AngularJs, .Net Core, C#.NET, Azure, Cognitive Services, Machine Learning, SharePoint, Azure Services, and tools, Requirement Analysis, Team Management, Pre-Sales and more. Ability to work independently and Team, handle pressure and meet commitment. Excellence Communication and adjust in any environment

90 thoughts on “How to Create SQL Server Database Project With Visual Studio”
  1. Hi! Do you know if they make any plugins to assist with Search
    Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but
    I’m not seeing very good results. If you know of any please share.
    Many thanks!

  2. certainly like your website however you have to test the
    spelling on quite a few of your posts. Several of them are rife with spelling problems and I
    to find it very troublesome to tell the truth however I will definitely come
    again again.

  3. hi!,I really like your writing so so much! proportion we communicate
    extra about your article on AOL? I need an expert in this area to solve my problem.
    May be that is you! Looking ahead to peer you.

  4. Attractive section of content. I just stumbled upon your website and in accession capital to
    assert that I acquire in fact enjoyed account your blog posts.
    Any way I will be subscribing to your augment and even I
    achievement you access consistently rapidly.

  5. Have you ever thought about publishing an ebook or guest authoring on other
    blogs? I have a blog centered on the same ideas you discuss and would love to have you
    share some stories/information. I know my visitors would value your work.
    If you are even remotely interested, feel free to shoot me an e-mail.

  6. Hello! I’m at work surfing around your blog from my new iphone 3gs!
    Just wanted to say I love reading your blog and look forward to all your posts!
    Carry on the excellent work!

  7. Attractive element of content. I simply stumbled upon your web site and in accession capital to assert
    that I get actually enjoyed account your blog posts. Any way I’ll be subscribing on your augment and
    even I fulfillment you get right of entry to persistently fast.

  8. I was suggested this website by my cousin. I’m
    not sure whether this post is written by him as nobody else know such detailed
    about my difficulty. You are amazing! Thanks!

  9. Magnificent goods from you, man. I’ve understand your stuff previous to and you’re just extremely magnificent.

    I really like what you have acquired here, certainly like what you are saying and the way in which you
    say it. You make it entertaining and you still take care of to keep it sensible.
    I can’t wait to read much more from you. This is actually a great website.

  10. Hello there! I know this is kinda off topic however , I’d figured I’d
    ask. Would you be interested in exchanging links or maybe guest authoring a blog article or vice-versa?

    My website addresses a lot of the same topics as yours and I feel we
    could greatly benefit from each other. If you’re interested feel free to send me an email.

    I look forward to hearing from you! Superb blog
    by the way!

  11. Do you have a spam issue on this site; I also am a blogger, and I
    was wondering your situation; mazny of us have created some nice methods and we are loopking to trade techniques with others, be sure to shoot me an email if interested.

  12. Generally I don’t read post on blogs, however I would like to sayy that this write-up very
    commpelled me to try and do it! Your writing taste has been amazed me.
    Thanks, very nice post.

  13. Hey would you mind sharing which blog platform you’re using?
    I’m planning to start my own blog soon but I’m having a hard time selecting between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design seems different then most blogs and I’m
    looking for something unique. P.S Sorry for being off-topic but I had to ask!

  14. Link exchange is nothing else but it is simply placing the other person’s weblog link on your page at
    suitable place and other person will also do similar in support of you.

  15. Do you miond if I quhote a few of your articles as long as Iprovide credit and sources back to your website?
    My website is in the very same area of inerest as
    yours and my visitors would certainly benefit from a lot of the information yoou provide here.
    Please let me know iff this alright with you. Thanks!

  16. I’ll right way take hold of your rss feed as I can not to find your
    e-mail subscription link or newsletter service. Do you’ve any?
    Kindly allow me recognize in order that I could subscribe.
    Thanks.

  17. Thanks for every other informative website. Where
    elsde may jus I am getting that type of info written in such an ideal way?
    I’ve a mission that I am just now operating on, and I’ve been on the look out for such information.

  18. Thank you a lot for sharing this with all of us you actually recognise what you’re speaking approximately!
    Bookmarked. Kindly additionally seek advice from my site
    =). We will have a hyperlink change contract among us

  19. I am regular visitor, how are you everybody?
    This paragraph posted at this site is in fact nice.

  20. Hello there, just became aware of your blog through Google,
    and found that it is really informative. I’m gonna watch out for brussels.
    I’ll appreciate if you continue this in future. A lot of people will be benefited from your writing.
    Cheers!

  21. It’s a shame you don’t have a donate button! I’d certainly donate to this excellent blog!
    I guess for now i’ll settle for book-marking
    and adding your RSS feed to my Google account. I look forward
    to brand new updates and will share this website with my Facebook group.

    Chat soon!

  22. Greetings from California! I’m bored at work so I decided to
    check out your website on my iphone during lunch break.
    I really like the info you present here and can’t
    wait to take a look when I get home. I’m amazed at how quick your blog loaded
    on my cell phone .. I’m not even using WIFI, just 3G ..
    Anyways, fantastic blog!

  23. Hey great website! Does running a blog such as this require
    a great deal of work? I’ve absolutely no understanding of coding but I was hoping to start my own blog in the near future.

    Anyways, if you have any ideas or tips for new blog owners please share.

    I know this is off subject nevertheless I simply needed to ask.
    Kudos!

  24. Your style is unique in comparison to other folks
    I’ve read stuff from. I appreciate you for posting when you have the
    opportunity, Guess I’ll just bookmark this blog.

  25. Appreciating the dedication you put into your site and
    detailed information you present. It’s great to come across a blog
    every once in a while that isn’t the same unwanted rehashed material.
    Fantastic read! I’ve saved your site and I’m
    adding your RSS feeds to my Google account.

  26. Hey there this is somewhat of off topic but I was wondering if
    blogs use WYSIWYG editors or if you have to manually code with HTML.

    I’m starting a blog soon but have no coding expertise
    so I wanted to get guidance from someone
    with experience. Any help would be enormously appreciated!

  27. Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something.
    I think that you can do with some pics to drive the message
    home a bit, but other than that, this is excellent blog.
    A fantastic read. I will definitely be back.

  28. Its like you read my mind! You appear to know so much about this, like you wrote the book in it or something.
    I think that you can do with a few pics to drive the message home a little
    bit, but instead of that, this is magnificent
    blog. An excellent read. I will certainly be back.

  29. Hey there just wanted to give you a quick heads
    up. The words in your post seem to be running off the screen in Safari.
    I’m not sure if this is a formatting issue or something to do with internet browser
    compatibility but I thought I’d post to let you know.
    The design look great though! Hope you get the issue resolved soon. Kudos

  30. Simply desire to say your article is as amazing.
    The clarity in your post is just cool and i could assume you are an expert on this subject.
    Well with your permission allow me to grab your RSS feed to keep updated with forthcoming
    post. Thanks a million and please continue the rewarding work.

  31. Hello, i read your blog occasionally and i own a similar one and i
    was just curious if you get a lot of spam remarks?
    If so how do you protect against it, any plugin or anything
    you can suggest? I get so much lately it’s driving me insane so any help is
    very much appreciated.

  32. I am not sure where you’re getting your information, but great
    topic. I needs to spend some time learning much more or understanding more.
    Thanks for great information I was looking for this information for my mission.

  33. Good day! This is my 1st comment here so I just wanted to give a
    quick shout out and say I really enjoy reading your blog posts.
    Can you suggest any other blogs/websites/forums that deal with the same subjects?

    Thank you!

  34. Hi there! I could have sworn I’ve been to this blog before but
    after reading through some of the post I realized it’s new to me.
    Nonetheless, I’m definitely glad I found it and I’ll be bookmarking and checking
    back frequently!

  35. Hi there, i read your blog from time to time and i own a similar one and i
    was just wondering if you get a lot of spam
    remarks? If so how do you prevent it, any plugin or anything you can suggest?
    I get so much lately it’s driving me crazy so any support is very much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *