The yes/no e-mail and the rubber duck

yes no rubberduck

I recently read an article about delegation of work and this gave me an idea the yes/no e-mail and the rubber duck.

What is this article about

This article is about delegation of work. When you manage a team you could get lots of e-mails from people asking all sorts of questions for you to figure out.

Mostly those questions are in the line off “What do I need to do?”

This article describes that situation and how you could handle it in a more efficient way for both you and the employees.

What is the problem

The problem is that at some point you end up with a ton of e-mail, asking you the what-to-do question, and this can consume a considerable amount of time.
There is nothing wrong with those kinds of e-mails but I like to work efficient and try to avoid it.

Some questions may take 15 minutes, but some could take up to an hour and that’s where it becomes a problem.

At that point you’re no longer managing but you’re doing all the work which in turn does not benefit the rest of the people who you should manage.

What do you know? You’re not a manager

That’s correct, I’m not a manager,  I’m a database administrator. I have managed junior database administrators in the past and that’s where I started to get experience with management.

I too would receive the what-to-do questions and dive into them explaining everything which lead me to abandon my work getting into trouble with my deadlines.

The junior DBAs would also come into my office asking all sorts of things without explaining the details, which you may understand, when you get disturbed it takes some time to get back to whatever you were doing.

From that moment on I spend time on improving myself to make sure things ran smoothly. So I do have some experience in that field.

How did I solve this problem

The yes/no e-mail

There are days where I would get a ton of e-mail with all sorts of questions how to solve certain scenarios.

I would get a question with something like this from one of the DBAs

I noticed in the monitoring that one of the data disks only has 15% of disk space left. What should we do?

This is not a complex question but it would take me some time to investigate the disk, talk to the SAN admin (if there is one) and this would probably take about an hour of my time.

Instead the DBA could have done this

I noticed in the monitoring that one of the data disks only has 15% of disk space left. I looked at the databases which caused the growth and there wasn’t anything special going on. A normal growth event has happened because the data file was full.
I talked to the SAN admin to see if we can increase the disk space and he says he/she says that we add another 500GB to the 1TB disk.
Would that be a good idea?

Do you see the difference? Instead of asking what to do, the DBA went out of his way and learned something there. He/she investigated the problem and came up with a solution.
Whether or not I agree with it is up to me, but now I can just reply with a YES or a NO, be it with some explanation, and do not have to spend lots of time on it.

Do not get me wrong, In some situations this will not work. If you have employees that do not have enough knowledge and/or privileges to do things this will be harder to do.

It will take some time to adjust to the new way of communication, but in the end it will be worth it.

This approach does enable the employee to grow. It gives the person more responsibility and makes their work more efficient. It saves time for both the manager and the employee which makes it a win-win situation.

Even if the solution provided by the employee wasn’t the right one, I would not shoot it down. I would praise him/her for their effort and make this a teachable moment.

The rubber duck

This is another method I used which was normally used for people coming to your desk to ask a question.

The rubber duck you say, what does that do? The rubber duck was literally a rubber duck on my desk which acted as a symbol..

Whenever one of the DBAs would come and ask a question I would point them to the rubber duck. He/she would explain the problem in such detail that even the rubber duck would understand.

What did that do with the person asking the question? What happened was that the person was forced to explain the problem in such that everybody would understand it. In about 90% of the situations the answer would be clear to them and didn’t need me to answer it.

This worked so well that at some point they would enter my office, look at the duck and leave, already having figured out what to do.

Remember, I never turned them away, I only asked them to provide enough information to let anyone understand the problem. There was never a feeling they couldn’t ask me anything and that’s important.


The methods described above can:

  • increase productivity
  • let the a department work more efficient
  • help give responsibility
  • let employees be more responsible for their work

I hope this gave you some ideas, it surely worked with me.


Scanning for PII with dbatools


Recently a brand new command was released that could help you scan for PII (Personal Identifiable Information) in our databases.

What Is Personally Identifiable Information (PII)?

Personally identifiable information (PII) is like the name implies, data that can be used to identify a person. It is typically actively collected, meaning the information is provided directly by the individual.

Here are a couple of identifiers that qualify as PII-based data:

  • Name
  • Email address
  • Postal address
  • Phone number
  • Personal ID numbers (e.g., social security, passport, driver’s license, bank account)

Why is this command developed

The idea came from a line of commands that are present in dbatools to mask data. Although these commands are great, going through all of the tables and look through the data was a bit tedious for me.

Especially when you’re dealing with databases  that have hundreds to thousands of tables, you easily run into the thousands to tens of thousands of columns.

So that’s how I came up with the command to scan for PII and it’s called Invoke-DbaDbPiiScan and is present in dbatools from version 0.9.819.

The command returns all the columns that potentially contain PII. I must say potentially, because the results still need to be assessed if it indeed contains PII. But it takes care of eliminating the majority of the columns saving you a lot of time.

This information is very valuable when you have to deal with the GDPR, but also when you have to deal with things like HIPAA.

How does the command work work

I recently wrote about the command to ask for help from the community to come up with patterns and known names to improve the scan.

It’s setup in such a way that to improve the scan, we only need to look at the known name and the patterns. The known names and patterns are setup using regex or regular expressions in full.

Regular Expressions is a sequence of characters that defines a search pattern. It can be used to match a series characters from simple to very complex.

The files with the regular expressions are located in the bin\datamasking folder.

During the scan the command will go through two phases:

  1. Scan for known names
  2. Scan for data patterns

If the command comes across a column that matches in phase one, it will skip that column for phase 2. Because it already flagged this column to potentially have PII, it would not make sense to also try to match all the patterns on it. Avoiding this makes the process fast and efficient.

Known Names

The file that contains all the known column names is called pii-knownnames.json.

A known name has the following properties:

  1. Name
  2. Category
  3. Pattern

An example of a known name is:

    "Name": "Name",
    "Category": "Personal",
    "Pattern": [

In this example, if the name of the column matches anything like firstname, fname, lastname etc, it will return in the scan.

Data Patterns

The file that contains all the data patterns is called pii-patterns.json.

A pattern has the following properties:

  1. Name
  2. Category
  3. Country
  4. CountryCode
  5. Pattern
  6. Description (not yet in production at the time of writing this article)

The pattern has a little more information than the know name. The reason for that is that the known name is not bound to countries and only applies to language. Because a language can be used in multiple countries, adding a country to the known name wouldn’t make sense.

The second reason why there is a country and countrycode property is that this enables the user to filter on specific countries. Imagine you have a database with only data from a specific country, going through a very large set of patterns would be a very long process.

With the country and country code, the command is able to filter on the patterns and only try to match those that make sense for the user.

An example of a pattern is:

    "Name": "Creditcard Mastercard",
    "Category": "Financial",
    "Country": "All",
    "CountryCode": "All",
    "Pattern": "(5[1-5]\\d{14})|(5[1-5]\\d{2}[-| ]\\d{4}[-| ]\\d{4}[-| ]\\d{4})",
    "Description": ""

Running the command

Enough talk how it all works, let’s get to the point to execute the command.

The easiest way of running the command is by executing the following line (replacing the brackets of course)

Invoke-DbaDbPiiScan -SqlInstance [yourserver] -Database [yourdatabase]

The result would look something like this

As you can see the database has a variety of columns that comes into the category of being PII.

In the example above the command finished within a couple of seconds. But when you have a wide range of tables and columns this process can take a little longer. To known the progress the command will display a progress bar to show you the current status of the scan

The example above uses the Out-GridView commandlet to output the results to a GUI matrix. This makes it easy to look at the results. The command would like this

Invoke-DbaDbPiiScan -SqlInstance [yourserver] -Database [yourdatabase] | Out-GridView

The result would look something like this

What’s next?

The next step with this command is to implement the functionality in the New-DbaDbDataMaskingConfig command. I want the user to be able to go straight to the usual suspects in the database and only create a config for those columns that potentially have PII.

The command has several other parameters to make more specific scans. Take a look at the help from the command to get to know more about the other parameters.

Get-Help Invoke-DbaDbPiiScan

There are also several examples that in the help that can also get you very far.

I hope this helps you out a bit. Especially when you’re dealing with the entire GDPR jungle finding all the little pieces within your organization that holds PII.

If you want think you’re missing some patterns or know names please help us out. With all of you we can make this scan really thorough. Please read my previous blog post to know how to help out.


Help needed for new PII command

together we create


The location of the JSON files has been changed to development branch

I’ll cut right to it, I need your help.

I’m developing a new command for dbatools to scan for PII.

I already have a wide variety of different patterns and ways to check on possible personal information but I want to be as thorough and complete as possible.

The command is called Invoke-DbaDbPiiScan and it does two things:

  1. it scans the columns in the tables and sees if it is named in such a way that it could contain personal information
  2. it retrieves a given amount of rows and goes through the rows to do pattern recognition

pii scan result

How does it work

The command uses two files:

  1. pii-knownnames.json; Used for the column name recognition
  2. pii-patterns.json; Used for the pattern recognition

You can find the files here in the GitHub repository.

The patterns and known names are setup using regex to make the scan really fast.
Also, using regex this way with the JSON files makes the solution modular and easy to extend.


An example of a known name regex is this:


What this does is, it tries to match anything with “name”.


The pattern regexes tend to be more complex than the know names. This is because we have to deal with more complex data.

An example of a pattern:

(3[47]\d{13})|(3[47]\d{2}[-| ]\d{6}[-| ]\d{5})

This particular pattern is used to find any MasterCard credit card numbers.

How can you help

What I need from you is to see if you can come up with more patterns that could lead to a more exact result.

I opened an issue in the Github repository where you can leave a comment with the pattern.

If this pattern is only used in a certain country, make sure you include which country this applies to.

I want to thank beforehand for any input.

If you have any questions leave a comment here, contact me through SQL Community Slack Channel or Twitter both as @SQLStad.


The double-edged sword of open-source


I got involved into a discussion about open-source software. Apparently a maintainer of an open-source project handed over the rains to another person and the other person changed the software to include a coin/mining exploit.  This really got me thinking about the double-edged sword of open-source.

Where did open-source originate from?

A little history lesson about open-source projects. Open-source came to be in 1998 where it was developed after Netscape’s announcement that the software for the Navigator software was going to publicly released.

The term got more momentum during the Freeware Summit organized by Tim O’Reilly in April 1998.

The highlight was when the Open Source Summit which is known as the birth of open-source.

So what’s the problem with open-source?

At the same time there is one and there isn’t one.

Open-source is a blessing in my opinion. It enables developers to help out with projects and collaborate. It gives companies the choice to either go buy proprietary software or to choose the option to go with open-source which is mostly free.

The problem with open-source, and which most people have forgotten, is that as soon as the software is released the maintainer has no responsibilities towards anyone. The software is provided “AS IS” which is mentioned very clearly in the license.


This also is both a good and a bad thing.

It’s a good thing because it releases the maintainer of any duties that have to do with the software product. Other people can contribute and create their own features which the maintainer is not reliable for. I certainly would not want to be responsible for a bug in one of my open-source projects and having to deal with large claims from companies because they lost revenue.

On the other hand, because the maintainer has no responsibilities, you are the one responsible from the moment you download the software.

The maintainer of the open-source project doesn’t even to reply to any issues, give you support or any other interaction with the software user. Everything that a maintainer does outside of releasing the software is big bonus and shows the character and passion of the maintainer.

What happened in the discussion?

Apparently the new maintainer changed the code of the project and included a bitcoin miner into it. So everyone who actually used that software and updated it, would get a bitcoin miner installed which would cause a lot of problems.

Immediately you would think that the original maintainer would be the person responsible because he didn’t check the person that wanted to take over the rains.

It is very scary that open-source projects are vulnerable to these kind of practices because at some point we trust the software and put it into our environments. That causes a threat to our production environments.

In reality, maintainers of an open-source project, are in no way responsible for these kind of issues. As soon as a piece of open-source software is released, the maintainer has done it’s work.

How can we avoid this?

I don’t think we can actually avoid these kind of problems. If for some reason such a thing happens mostly of the time people will find out quickly.

News like this will go quick especially with social media platforms like Facebook and Twitter, projects that are compromised will become flagged very soon.

I think we need to be diligent when we start using open-source software. Make sure you use software that is used a lot or is developed by someone with a good reputation.

It’s pretty easy to look into a Github repository and get basic information like who the maintainer is and do some research on the person. If you don’t trust it, don’t install it. Get more information, go ask on forums and ask questions before you start to rely on these projects.

My two cents on the responsibilities

I’m a contributor to a couple of open-source projects and I rely heavily on them in my day-to-day work.

I’ve contributed a bunch of commands to the dbatools projects. I feel responsible for these commands but I’m completely fine if someone else finds a bug and fixes it. When someone finds a bug in a command and notifies me, I will try to fix it. I feel responsible because I created them but it’s in no way only my responsibility.

Maintainers and contributors have no obligation to help after a piece of software is released. Everything that’s done after the release is a big bonus.

I do think that the way the projects are run reflects the types of people who are involved. Regularly I get questions from people all over the world about a command I created.

I will always reply to these questions because I think that it’s important to give support in these projects and it helps the projects being.


Open-source software is great and most of the time you get a reliable version for free or a small amount of money. You, as the user, have to make sure that the project can be trusted.

Closed software is great too but it can be costly. The cost for the software gives a sense a security and a lot of companies prefer to pay for software instead of using open-source.

Both have their pros and cons and you must decide what’s best for your use-case. I use both open-source and closed source and use them for my use-cases.


T-SQL Tuesday #110 – Automate All the Things


This month’s T-SQL Tuesday is about automating all the things.

I love automating processes, especially when it comes to managing SQL Server, and I think that most processes should be automated.

So technically there are two tasks for this month:

  • What is your go-to technology for automation?
  • What do you want to automate or what automation are you proud of completing?

For me, a process can be automated when:

  • It’s repetitive
  • Takes a long time to execute
  • And needs human interaction

I will go far to automate a process. I’d rather spend a week developing a process to get it automated, than to execute it twice and spend lots of time on it.

What is your go-to technology for automation?

In the last couple of years I’ve automated a whole bunch of processes using different technologies to accomplish that. My go-to technologies for automating are PowerShell, DevOps, tSQLt and dbatools.

You might say; “dbatools is PowerShell right?”, and you’re correct, but this module has grown so much that it has to be mentioned on it’s own.


I’ve been working with PowerShell since version 1. I fell in love with it especially because it filled a hole that we needed something else than a GUI to manage our infrastructure.

PowerShell is my hammer to automate about anything. It crosses multiple domains within the Windows universe, from managing your domains, your users, servers, services.

The PowerShell team has done a great job to make this platform as versatile as possible for anyone to use.


This part is still kind of new for me and I find that this technology uses different technologies to accomplish the job.

I used Jenkins and Octopus Deploy within my CI/CD pipeline to automate releases. The thing is that within that pipeline I used several technologies to accomplish that.

Technologies like Groovy for the pipeline script, PowerShell to execute certain tasks, dbatools to execute some other.

I like Jenkins because it let’s me define exactly what I want to do and it doesn’t have a big learning curve.

Octopus Deploy hooks into the part where Jenkins is finished. Creating releases, deploying them to different servers makes it this CI/CD process complete.


This technology has saved my life. I never did a lot of development but I know how to develop a database. In the past I made changes, pushed the changes to a development server and crossed my fingers if I didn’t break anything.

With tSQLt I have the ability to create unit tests for SQL Server databases to test every little aspect of my database.

If a small change in the schema breaks a procedure I will know before I release it.

I will be presenting about this subject in the near future and help people along the way because I think this needs to be used a lot more.


Last but not least, dbatools. This project changed my life in several ways.

First, it taught me how to develop in a lage open-source project. To have standards and how to handle commits. I did not know Git that well before I started contributing and now I’m one of the major contributors.

Second, it gave me the ability to help others. Having several scripts laying around, that could easily be transformed to proper functions/commands that could be used by other people. If I had that problem there is bound to be someone else that has the same and I could save this person a lot of headache.

Third, it made my automate a lot of processes for myself. Think of the backup testing command “Test-DbaLastBackup“. This command tests your backup by restoring it, doing a DBCC check and returns the result back to you.
You will know if you’re backup can be restored and if that data within the backup is not corrupted.

If you have not worked with dbatools yet, make sure you start with it today. It will change your life as a database administrator or developer.

Fourth, and this one comes with contributing to the project, is that I met a lot of incredible people from the community. I love the community how they share their knowledge and help other people out.

What do I want to automate

I’ve been working on automating our releases for database development.

This process has taken a considerable amount of time because I had no knowledge and experience in that area.

I have almost finished that process and I’m glad to say that this year I’ll have a CI/CD pipeline with Visual Studio, Git, Jenkins and Octopus Deploy.

My next project will be to implement data masking making it possible to ship databases to other servers and people without exposing production data.

What automation are you proud of completing

These are several projects that I’m proud of:

The pattern is getting obvious. Automate repetitive, pain-staking processes.

Change your mindset to embrace automation and you never want to go back.

I have less and less time to get things done.
My agenda is filled with meetings, project deadlines and we get more and more things on my plate.

Start automating your work today. You have to put in some energy in getting to automate your processes, but you’ll never look back.


Deterministic masking with dbatools


The dbatools module recently got a couple of new commands mask data in their databases.

One feature with the masking commands that was not yet put in was deterministic masking.

What is deterministic masking

Deterministic masking is the process of replacing a value in a column with the exact value across tables.

In example, a database has multiple tables with a column that has first names. With deterministic masking the first name that’s present will always be replaced with the same value.

Let’s assume the first name “Chris” will be replaced with “Jeff”. The value “Chris” will be replaced with “Jeff” in a column, regardless of the table or column name.

This is very important when you’re dealing with a database that is not normalized using relationships like reporting or business intelligence related tables.

How does it work

When creating the masking configuration file you have the option to set a column to be deterministic like this:

deterministic masking config

During the process of masking the data the command Invoke-DbaDbDataMasking will create a dictionary with the values from the columns that are set to deterministic.

Every time it processes a row it will check the dictionary if the value is already present. If it is, the new value of the the particular column and row will be set to the value from the dictionary.

If it cannot find the value a new value will be generated and added to the dictionary for later use.

But won’t this make my database less secure

You may think that, when you always use the same value every time, that it will be less secure but in this case it isn’t.

This is because the masking command does not rely on any particular key to regenerate the value. Every value that needs to be replaced will get a random new value.

This value is then put in the dictionary and basically has no reference to the old value.

That’s fantastic! But what are the downsides?

With every feature and extra check comes the fact that extra processing is needed to determine the value for a particular row.

Extra processing means that extra time is needed to process the table.

You also need more memory for the process because the dictionary will hold all the unique values from all the columns that are set the to deterministic.

This can lead to a large amount of values when you’re dealing with terabytes of data.

I would advise to use this feature only for columns that really need to be deterministic and not use it lightly across all the columns.

This feature enhanced the command to create some really sophisticated masking strategies.

For more information about the command read the blog post by Chrissy LeMaire has written about the new feature.

Data Masking with dbatools


Recently I developed a few PowerShell commands to make it possible to enable data masking for databases.

The commands were originally written for the module PSDatabaseClone to enable users to automatically mask the data for a database image. The reason the commands were created was because the cloning process would otherwise expose production data to other users which is not preferable.

The commands were released and picked up by Chrissy LeMaire who implemented them in dbatools and even improved them.

I decided that because the PSDatabaseClone module already relied on dbatools, that I would remove the original command from my module and map to the command in dbatools version.

Why are these commands created

There are actually various pieces of software available that offer functionality to mask data in a database like DataVeil, DataMasker and JumbleDB.

I have no experience with these products but looking at the features they seem to do the job.

Relying on the features of a commercial product was not an option because the PSDatabaseClone module is open-source. Most of the products do not allow the use of their code within other software.

I wanted to make this process as easy and straightforward as possible. One command to generate a configuration file how the masking should be executed. Another command to execute the data masking.

If a user created an image he/she would have the chance also ask the data within the image. That way a user would be able to create a clone from that image never exposing production data to the world.

Generating a configuration file

The first thing I had to think of was the data structure to save information about the tables and columns. Important information like the name, schema, column type, the minimum value, the maximum value and the masking type and sub type.

I’m a fan of JSON to create data structures in text files. It’s easy to read and works really well with various systems. PowerShell is one of those systems.

The first command will generate the masking configuration file containing all the tables and columns that should be masked.

I wanted the command to be able to distinguish certain column names to associate them with a particular way to generate random data. For that I created a file that contained all the synonyms.

In example the command would be able to find columns with first names if the column name would be something like “Firstname” or “Forename”.

If it didn’t find the name of the column in the synonym list, it would then look at the data type and based on that decide what kind of data masking type should be applied.

It was renamed to New-DbaDbMaskingConfig in dbatools. Executing it will result in a similar output like below

New-DbaDbMaskingConfig -SqlInstance [yourinstance] -Database [yourdatabase] -Path [directory-to-export-to]

Your file will be written to the directory you chose. It will have the the instance name and the database in the file name like below

The content of the file will look similar to this

This made things a lot easier for users to create the initial configuration. Without this command it would take a lot of time to create the masking configuration which would also be subject to errors.

The second is command is to use this content and execute the actual masking.

Masking the data

The next command ended up taking some more research than I first expected.

I had to figure out how to generate random data like names, streets, zip codes, e-mail addresses, credit cards. The columns that could possible have some sensitive information.

At first I wanted to create my own library to generate the different types of random data.

Fortunately there were several projects out there that can help with this so I would not have to reinvent the wheel.

During the process I tried out several of these libraries like Fare and Bogus. In the end I chose for Bogus which had a wide variety of items that could be generated and worked well how I thought this process could be implemented.

The command would rely on the masking configuration and based on that data perform the data masks.

It will load the configuration file and look through each of the tables and columns. With each column the command will execute a certain masking action based on the masking type and sub type.

During the look the command generates an UPDATE statement that will change the value to the new value.

After all the updates have been performed the command returns an overview of the performed actions.

It will look similar to the window below

Invoke-DbaDbDataMasking -SqlInstance [yourinstance] -Database [yourdatabase] -FilePath [path-to-masking-config]

The result can be seen below

You’ll have a a database with the data masked precisely as the you requested.

CI/CD for databases: Setting Up The Project


This series has been a long time coming. I have been struggling with continuous integration and continuous development for a while and I want to share my process, techniques and tips and tricks with you.

I will publish several blog posts about this process because we cannot go through all of it at once. It would lead to a TL;DR article that nobody will read.

  • The first part will be setting up the project/solution for your database.
  • The second part will be about creating unit tests for your database objects.
  • The third part will be to put the project through a build server and eventually a deployment server

Why this series

I never had to do a lot of work with source control, visual studio and all the related parts because I would get a script, deploy it in test. If it worked fine, if not I would have a backup and restore that.

That was my previous process and works on some level. But when you have to deal with rapid development, things start to break.

In my current job there is another problem. I’m both the DBA and, for most database projects, also the developer. That means I wear two hats when developing and deploying my solutions.

That’s like I’m marking my own paper and it’s always approved. I don’t want that responsibility and reliability so things have to change.

The last reason I think this is really important is that I like to automate everything. Automation prevents human errors and makes out lives easier.

In the end I only want to work on a project, push the changes to source control, let some service build the objects and push it along without me having to interfere.

Setting up the project

This part has taken a considerable amount of time for me to switch over to. For me to switch over from the SQL Server Management Studio (SSMS) to having to deal with Visual Studio (VS) was like night and day.

The thing is that when you’ve never worked with VS a lot you kind of have to find your way around it. The SSDT projects are different from say a C# project and it all feels a bit big.

I assure you, if you’re going to switch over, that feeling is only going to be there in the beginning. I’m going to help you setup your project in mere minutes instead of the hours I had to put into it.

The first thing I wanted to do is setup my project. I wanted to separate the model from the tests in separate projects but within the same solution. The following post helped me a lot and was glad with the framework. I did change a couple of things to make things run smooth in the end.

Setting up this framework every time is going to be a paint in the but and I was not going to spend all that time setting up database projects.

Fortunately my good friend Friedrich/Fred Weinmann (t | g) has created a few commands that make it possible to create templates. not just of files, but also entire folders.

This was the solution to my problem. I setup the solution in such a way that it would automatically generate the right files with the right names. Fred helped make the final changes under the hood to dot all the i’s.

Generating the solution

There are a couple of things you need to have installed before you can generate the SSDT solution.

  1. Install the PowerShell module PSModuleDevelopment
  2. Download the SSDT solution
  3. Generate the template locally

Install PSModuleDevelopment

Open a PowerShell window and enter the following command

Install-Module PSModuleDevelopment -Scope CurrentUser

Download the SSDT solution

I have created the SSDT solution for you so you don’t have to. Go to the repository and download the files.

If you have Git installed, go to your preferred location to download the content and execute the following command

git clone

You can also download the zip file manually from the Github repository. When you download the zip file, unpack it in your preferred location.

Generate the template

For you to be able to generate the solution you have to create the template on your computer first. This only needs to be done once and you can reuse it every time.

Execute the following command to create the template

New-PSMDTemplate -ReferencePath [path-you-downloaded-the-files-to] -TemplateName [name-for-the-template]

For example

New-PSMDTemplate -ReferencePath C:\Temp\SSDT-With-tSQLt-Template\ -TemplateName SSDTWithtSQLt

After that you no longer need the files and you can remove them if you like.

The last step is the most exciting one, generating the solution.

Execute the following command to generate the solution based on your just created template

Invoke-PSMDTemplate -TemplateName [name-of-the-template] -OutPath [path-to-output-to] -Name [your-solution-name]

For example

Invoke-PSMDTemplate -TemplateName SSDTWithtSQLt -OutPath "C:\Temp\" -Name DBProject

Navigating to the solution it will look something like this:

SSDT solution generation result

Opening the solution this is what you’ll have

SSDT solution explorer

Now you’ll be able to generate your SSDT projects, including all the content needed for unit testing in mere minutes.