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

Research and Technology Lead | Software Architect | Full Stack .NET Expert | Tech Blogger | Community Speaker | Trainer | YouTuber. Follow me @ https://rijsat.com Md Rijwan Ansari is a high performing and technology consultant with 10 plus years of Software Development and Business Applications implementation using .NET Technologies, SharePoint, Power Platform, Data, AI, Azure and cognitive services. He is also a Microsoft Certified Trainer, C# Corner MVP, Microsoft Certified Data Analyst Associate, Microsoft Certified Azure Data Scientist Associate, CSM, CSPO, MCTS, MCP, with 15+ Microsoft Certifications. He is a research and technology lead in Tech One Global as well as leading Facebook community Cloud Experts Group and SharePoint User Group Nepal. He is a active contributor and speaker in c-sharpcorner.com community, C# Corner MVP and his rank at 20 among 3+ millions members. Additionally, he is knee to learn new technologies, write articles, love to contribute to the open-source community. Visit his blog RIJSAT.COM for extensive articles, courses, news, videos and issues resolution specially for developer and data engineer.

158 thoughts on “How to Create SQL Server Database Project With Visual Studio”
  1. I’m gone to convey my little brother, that he should also visit
    this website on regular basis to get updated from most recent gossip.

  2. I love your blog.. very nice colors & theme.
    Did you create this website yourself or did you hire someone to do it for you?
    Plz answer back as I’m looking to construct my own blog and would like to know where u got this from.

    kudos

  3. I think what you published made a great deal of sense. However, what about this?
    suppose you composed a catchier post title? I am not saying your
    information is not good, however what if you added a title to maybe grab a person’s attention? I mean How
    to Create SQL Server Database Project With Visual Studio – Rijwan & Satya’s Blog is kinda boring.
    You should peek at Yahoo’s home page and see how they create article headlines to grab people to
    open the links. You might add a video or a related pic or two to grab readers interested
    about everything’ve got to say. Just my opinion, it would
    make your posts a little bit more interesting.

  4. 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!

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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!

  10. 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.

  11. 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!

  12. 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.

  13. 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!

  14. It’s really a cool and useful piece oof info.

    I am happy that you shared this useful informatio with
    us. Please stay us up to date like this. Thank you for sharing.

  15. 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.

  16. 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.

  17. 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!

  18. 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.

  19. 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!

  20. 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.

  21. 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.

  22. 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

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

  24. 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!

  25. 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!

  26. 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!

  27. Today, I went to the beach with my children. I found a sea shell
    and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.”
    She placed the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear. She never wants to go
    back! LoL I know this is totally off topic but I had to tell
    someone!

  28. Hi there, I believe your website might be having browser compatibility issues.
    Whenever I take a look at your web site in Safari, it looks
    fine however, when opening in I.E., it’s got some overlapping issues.
    I merely wanted to provide you with a quick heads up!
    Apart from that, great website!

  29. Just desire to say your article is as astonishing.
    The clearness in your post is just nice and i could assume you’re an expert on this subject.

    Well with your permission let me to grab your RSS feed to keep up to date with forthcoming post.

    Thanks a million and please continue the gratifying work.

  30. This design is incredible! You most certainly know how to keep a
    reader amused. Between your wit and your videos, I was almost moved to start my own blog
    (well, almost…HaHa!) Fantastic job. I really loved what you had to say, and more than that, how you presented it.

    Too cool!

  31. We are a bunch of volunteers and starting a new scheme in our community.
    Your website provided us with helpful info to work on. You have
    performed an impressive job and our entire neighborhood can be thankful to
    you.

  32. you’re truly a just right webmaster. The website loading velocity is incredible.

    It kind of feels that you’re doing any distinctive trick.
    In addition, The contents are masterpiece.

    you have done a great job in this subject!

  33. Hi there! Someone in my Myspace group shared
    this website with us so I came to give it a look.
    I’m definitely loving the information. I’m bookmarking and will be tweeting
    this to my followers! Outstanding blog and excellent design.

  34. Hey! Quick question that’s totally off topic. Do you know
    how to make your site mobile friendly? My web site looks weird when browsing from my iphone.
    I’m trying to find a template or plugin that might be able to correct this problem.
    If you have any suggestions, please share. Thank
    you!

  35. I have been surfing online greater than three hours nowadays, but I by no means discovered any interesting article like yours.
    It is beautiful value enough for me. In my view, if all web owners and bloggers made excellent content
    material as you probably did, the web will likely be a lot more helpful than ever before.

  36. I’ve been surfing online more than 4 hours today, yet I never found any interesting article
    like yours. It’s pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content
    as you did, the web will be much more useful than ever before.

  37. 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 could do with a few pics to drive the message
    home a bit, but instead of that, this is fantastic blog.
    An excellent read. I will definitely be back.

  38. Thank you for any other informative site. The place else could I am getting that type of
    info written in such an ideal means? I’ve a challenge
    that I am simply now operating on, and I’ve been on the glance out for such info.

  39. Superb blog! Do you have any tips and hints for
    aspiring writers? I’m hoping to start my own website soon but I’m a little lost on everything.

    Would you advise starting with a free platform like WordPress or
    go for a paid option? There are so many choices out there that I’m completely confused ..

    Any ideas? Appreciate it!

  40. My brother recommended I might like this web site. He was once entirely
    right. This put up actually made my day. You cann’t consider simply
    how so much time I had spent for this info! Thanks!

  41. Undeniably imagine that that you said. Your favourite justification seemed to be at the web the easiest factor to
    take note of. I say to you, I definitely get annoyed at the same time as people
    consider issues that they plainly don’t understand about.
    You controlled to hit the nail upon the highest and outlined out the whole
    thing without having side-effects , other people can take a
    signal. Will likely be again to get more. Thank you

  42. Great beat ! I wish to apprentice while you amend your website, how can i subscribe for a blog site?
    The account aided me a applicable deal. I had been tiny bit familiar of this your broadcast provided bright transparent concept

  43. Hello, i think that i saw you visited my weblog thus
    i came to “return the favor”.I am trying to find things
    to improve my website!I suppose its ok to use a few of your
    ideas!!

  44. I’m not certain the place you’re getting your information, but good topic.
    I must spend a while finding out much more or understanding more.
    Thank you for wonderful info I used to be in search of this info for
    my mission.

  45. Hi there just wanted to give you a quick heads up. The words in your post seem to be running
    off the screen in Opera. I’m not sure if this is
    a format issue or something to do with web browser
    compatibility but I thought I’d post to let you know. The design look great
    though! Hope you get the problem solved soon. Thanks

  46. 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!

  47. 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.

  48. With havin so much written content do you ever run into any problems of plagorism or copyright infringement?
    My site has a lot of exclusive content I’ve either written myself or outsourced but it
    looks like a lot of it is popping it up all over
    the web without my permission. Do you know any methods to help prevent
    content from being ripped off? I’d definitely appreciate
    it.

  49. Hmm it looks like your site ate my first comment (it was super long) so I guess I’ll just sum it up what I had
    written and say, I’m thoroughly enjoying your blog.
    I too am an aspiring blog blogger but I’m still
    new to everything. Do you have any recommendations for beginner blog writers?

    I’d genuinely appreciate it.

  50. 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.

  51. 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!

  52. 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.

  53. 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.

  54. 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

  55. 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.

  56. 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.

  57. 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.

  58. 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!

  59. It’s really a cool and helpful piece of info. I am glad
    that you just shared this useful info with us. Please stay us informed like this.
    Thanks for sharing.

  60. 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!

  61. I’m gone to tell my little brother, that he should
    also visit this web site on regular basis to obtain updated from newest information.

  62. 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.

  63. hi!,I like your writing so so much! proportion we keep in touch extra about your post
    on AOL? I require an expert in this house to
    solve my problem. May be that’s you! Having
    a look forward to see you.

  64. It’s wonderful that you are getting ideas from this article as well as
    from our dialogue made at this place.

  65. I have been exploring for a bit for any high-quality articles
    or blog posts in this kind of area . Exploring in Yahoo I finally stumbled upon this
    website. Studying this info So i’m glad to show that I
    have an incredibly good uncanny feeling I came upon exactly what I needed.
    I such a lot undoubtedly will make certain to don?t disregard this website
    and give it a look on a relentless basis.

  66. I do believe all the ideas you have offered for your post.
    They’re really convincing and will definitely work.

    Nonetheless, the posts are very short for beginners.
    Could you please prolong them a bit from next time?
    Thank you for the post.

  67. Thanks a bunch for sharing this with all folks you really realize what you
    are talking approximately! Bookmarked. Please additionally
    discuss with my site =). We may have a hyperlink change agreement between us

  68. Hi, i think that i saw you visited my web site so i
    came to “return the favor”.I’m trying to find things to improve my web
    site!I suppose its ok to use a few of your
    ideas!!

  69. This is a topic that’s near to my heart…
    Thank you! Exactly where are your contact details though?

  70. This is very interesting, You’re a very skilled blogger. I have joined your rss feed and look forward to seeking more of your great post. Also, I have shared your web site in my social networks!

  71. Thanks for your article on the travel industry. I would also like to include that if your senior thinking of traveling, it really is absolutely crucial that you buy travel cover for golden-agers. When traveling, retirees are at high risk of having a health care emergency. Obtaining the right insurance policies package for one’s age group can safeguard your health and provide peace of mind.

Leave a Reply

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