New DB project with SSDT and import existing database

New DB project with SSDT and import existing database

DevOps & databases with SSDT in practice

With this episode I would like to commence long-awaited new series
It’s about DevOps with databases. I’ll start with SSDT, but not exclude other tools (like Redgate ReadyRoll) in the future.
Starting from the very basic things, we will travel through entire process to see and understand how to work, develop, tests and maintenance database project within company.
In this very first episode I’ll introduce SSDT and show up how to create empty database project and how to import existing databases from physical server with 3 methods:
1) Import from database (connect to server)
2) Import from DACPAC
3) Import from script

Let’s get the ball rolling

What is SSDT?

SSDT – SQL Server Data Tools.
It is a free add-in? from Microsoft to Visual Studio which allows you create a database project (like a Desktop Application or Console project in VB/C# but for database).

Why do we need SSDT?

Have you ever written a code in VB(.NET), C#/++ or other to build application? Imagine that you need to write entire application only in your mind or using notepad. Skipping the fact, that a code was being written in that way (C) and writing HTML code is a different kettle of fish and doesn’t count. Hence, using SSDT you have a tool to maintain a database project and it allows you:

  • verify query statements (T-SQL)
  • validate data/column types
  • validate compatibility with specific version of SQL Server (SQL Server 2005! – Azure SQL Database v12)
  • prepare repeatable build process
  • compare and find the differences (i.e. between project and database)
  • apply standards & best practices onto your enterprise or customer
  • and eventually, create an automated process of deployment

Certainly, there are much more than those mentioned. If you know another one that I’ve missed – leave it in comments below, please.

How to get it?

You have two options:
1) Choose it from Visual Studio installer. Now it’s one of the modules you can simply select during installation.
Have Visual Studio already installed? No worries. Just run the installer again, do add required module.
2) Go to the website and download appropriate extension depending from your Visual Studio. Afterwards install it and run Visual Studio.

How to check whether I have SSDT on board?
Run Visual Studio and go to the menu: “Help” -> “About Microsoft Visual Studio” Opened window will reveal what modules you’ve installed.
Do find “SQL Server Data Tools” on the list and you’ll find out the version as well.

Other way is to create new project trying to find “SQL Server Database Project” node. Can you see it? Good. You can use SSDT within VS.

New (empty) database project

Once you open Visual Studio – go to the menu: File  -> New -> Project.

New window will be opened and then find the project type: SQL Server Database Project.
Having an empty database project you can either create new objects and create new database from the scratch or import existing one.
You can import the databases following one of 3 methods.

Import database from Data-Tier Application (dacpac file)

One of less popular option, in my opinion. You can create dacpac file using SSMS, for example.
DACPAC file is a zipped file which contains all information about database model. I’ll describe it more in separate post.
Anyways, it’s not a perfect method as export DACPAC file might be very tough, especially when your database has got errors, i.e. unresolved references.
But let’s assume that you already have one.
Right-click on database node in your new project and select Import -> Data-tier application.
Find needed *.dacpac file, decide whether you want to import permissions and/or database settings and click Start.
New window will be opened and the process commences. The summary of import will be created in a log file.

Import database from server

This kind of import is useful when you have access to the SQL server with database to import. The limitation is that you can do it only once.
If you already have some object in the project – this option is disabled. To re-enabled it, you must delete all objects (files) from the project.
Anyways, I use this option whenever I can. Why? You do not miss any objects or properties and you do not rely on other people who might have done the dacpac file (or script) wrongly. Thus, if I can do something faster and without additional risk, why not?
Right-click on database node in your new project and select Import -> Database….
In the Import Database window you need to choose a connection to source database. Even if you don’t have any – click Select Connection…

Pick up one of the existing connections you’ve used previously (tab History) or create new one (tab Browse).
Put a server name (including its instance name when required), select appropriate authentication method and database name (list should be populated when all above information are correct and your access is valid).
Then do click Connect and you’ll be ready to click Start to commence the import process.

Import database from script (sql file)

When you have no access to source server – this is option is for you. The script can be generated by someone else and you can use it to import a model. This option is very handy also in a case when part of a databases has been imported already or if you want to import only selected objects. In other words – this option is reusable.
Further more, one or more files might be imported during one pass. All files are being interpreted and the database objects defined in those scripts are added to your project.
What happens once your objects you’re importing already exist? Don’t worry. SSDT will not overwrite them entirely with a new version. Objects will be merged. Cool, isn’t it?

How create SQL script of database?

Simple. Use our favorite tool – SSMS.
Open SQL Server Management Studio, do right-click on the database name, Task -> Generate scripts.
Then you’ll be able to select specific database objects you want to script. Or simply select Script entire database and all database objects option. In the next step (“Set Scripting Options”) choose between:

  1. Save script to file and
    a) create one (big) file or single file per object
    b) Target directory
    c) Encoding of file(s)
  2. Save (copy) script to Clipboard
  3. Save to new query window – Open new window with generated script in SSMS

Also, you have more control over what the script containing. There is small, shy button Advanced, but very underestimated and powerful.
Before you click Next and generate the script, I would suggest to check all options behind the scenes.
The following options are very important in our case, so set them as TRUE:

  • Script Owner
  • Script Indexes
  • Script Triggers

These options also can be important – depends on your database:

  • Script Collation
  • Script Change Tracking
  • Script Data Compression Options

Why did I say that the Advanced Scripting Options are underestimated?
Due to “Types of data to script”. The property, by default, has value “Schema only”, but you might select one out of:

  • Data only
  • Schema and data
  • Schema only

Data. Yes, you can see it correctly. SSMS CAN script DATA for you. Maybe not in a very efficient way, as it’s scripted as many INSERT statements (one per row), but it’s free. Bear in mind that the script with data is completely useless in our case. Why? SSDT cannot keep data in a project. How to workaround this, I will explain in a future post in this series.

Which an environment should I do import?

The best source of the database would be a production server. In most cases, enterprises which didn’t adopt DevOps culture – the databases between environments are slightly different. In many places, for many reasons. It’s a common scenario, don’t worry. But production is production; the environment which the customers, stakeholders work on. If that works (because it works, right?) – this should be your image, your only version of the truth. Afterwards, you can create branches and import rest of environments to reflect their changes.

Import complete

When the whole process comes to the end you will see the summary of import and copy of that information will be created in a log file. All the objects from the source (depend on which way did you choose) have been created in the project under the folders (see below).

Any questions?

That’s all in this episode. Do you have any question about above or another topic around DevOps and/or databases? Put them in comments or send via a contact form. And please do remember that next episode of the series will appear soon.

Thanks for reading.

Updated @ 08/11/2019
This post is already part of the whole series about SSDT.

Previous ASF 010: Dejan Sarka interview
Next Last week reading (2018-06-03)

About author

Kamil Nowinski
Kamil Nowinski 200 posts

Blogger, speaker. Data Platform MVP, MCSE. Senior Data Engineer & data geek. Member of Data Community Poland, co-organizer of SQLDay, Happy husband & father.

View all posts by this author →

You might also like

Two methods of deployment Azure Data Factory

Azure Data Factory is a fantastic tool which allows you to orchestrate ETL/ELT processes at scale. This post is NOT about what Azure Data Factory is, neither how to use,

DevOps 40 Comments

Deployment of Azure Data Factory with Azure DevOps

In this blog post, I will answer the question I’ve been asked many times during my speeches about Azure Data Factory Mapping Data Flow, although the method described here can

How to create DACPAC file?

In this post, I will explain what the DACPAC is and how to create it. In the previous post in DevOps series, I described how to create a new, empty

17 Comments

  1. Kamil Nowinski
    October 02, 21:29 Reply

    I’ve created a database project in VS 2016. I need to change the “Import From” database. I don’t want to start a new project because this is all checked into TFS. I’d rather modify the existing configuration.

  2. […] I described in the previous post (New DB project with SSDT and import existing database) in this series, usage the DACPAC file is one of the methods of importing objects to an empty, SSDT […]

  3. Atul pandey
    November 11, 07:01 Reply

    Very nicely explain How to import database files to our other database file. The way you have shared the information is really good. Thanx for sharing this information.

  4. Anu
    September 07, 17:38 Reply

    Hi, I am trying to automate the database deployment using SSDT from Jenkins. How will I fetch the Data changes also since DACPAC file has only schema changes?
    What are the steps that I should follow? This should include data scripts as well.

  5. SQL_Rookie
    November 12, 16:42 Reply

    Hi Kamil,
    I followed your post & videos and imported the database via SSDT(SQL server object explorer ) to create a new project and to add it into Azure devops but I dont want the security folder to be added into the project. From the production copy, I want to create a a new DEV database. How can I remove Security folder ?

    Thanks
    Sandy

    • Kamil Nowinski
      April 14, 08:16 Reply

      SQL_Rookie,
      You can remove every single object (file) which you do not need anymore. It will reveal any potential dependencies and it works as long as the database project compiles. Remember, the security folder contains schema objects as well.

  6. ab
    March 31, 18:46 Reply

    Hi Kamil,

    Thank you the nice post , I have few question if you can provide suggestion based on the experience .
    – Should I be including view and store proc in the object when I import? is it good practice or any suggestion ?

    – Any suggestion on dealing with security object ?

  7. Pierre
    June 08, 21:32 Reply

    Hello Kamil,
    Does SSDT have a deploy once option like Sql Change Automation (ReadyRoll)?
    To only run a script once? The only solution I have found is to do a post deployment script.

    I appreciate the articles!

    Thanks,
    Pierre

    • Kamil Nowinski
      June 21, 18:33 Reply

      The SSDT does use a state-based approach, which means it will create the differential script first, and then the script is your “one-off” script to be executed. Apart from that, you’re right – as a hybrid model you can use pre or post-deployment script but they are always executed. However, you can use some tools like Flyway to control the deployment of migration scripts. Just today I published short video about Flyway:
      https://youtu.be/9eOhcAsl17c

  8. Tbanyan
    December 20, 15:56 Reply

    After the import, the Security folder had an extra credential for the login (login_1) used for the import with an encrypted password. Why and is this a security risk?

    • Kamil Nowinski
      April 11, 15:24 Reply

      Probably you imported it twice to the same folder where the previous object (login) already existed.

  9. kahanu
    September 21, 21:29 Reply

    You didn’t address how the SQL script that was created using SSMS was imported using the SSDT tools. It didn’t like the scripts I created using SSMS.

  10. Vaishnavi
    November 15, 05:50 Reply

    Hi, I am currently trying to update the changes from VS2019 to SQL-Server DB.
    I have noticed schema-compare update and Publish the code, can you please let me know which is the better way to update the DB in SQL server from VS2019.

Leave a Reply

Click here to cancel reply.