Setting the default file type for a new file in VS Code

Just a short post today. When you open a new file in VS Code (Using CTRL + N) it opens by default as a plain text file.

To change the language for the file use CTRL +K, M.

That’s CTRL and K together and then M afterwards separately.

then you can choose the language for the file. It looks like this

01 - Change language

However, if you just want your new file to open as a particular language every time you can change this in the settings.

Click File –> Preferences –> Settings

or by clicking CTRL + ,

02 - Open Preferences.PNG

This opens the settings.json file. Search in the bar for default and scroll down until you see file

03 - File defaults.PNG

If you hover over the setting that you want to change, you will see a little pencil. Click on that and then Copy to Settings which will copy it to your user settings in the right hand pane.

NOTE – You will need to enter powershell and not PowerShell. For other languages, click on the language in the bottom bar and look at the value in the brackets next to the language name

04 - langauge.PNG

Once you have entered the new settings save the file (CTRL + S) and then any new file you open will be using the language you have chosen

It looks like this

05 - Change settings.gif

and now every new file that you open will be opened as a PowerShell file (or whichever language you choose)

You will still be able to change the language with CTRL K, m

Just to be clear, because people sometimes get this wrong. That’s CTRL and K, let go and then M. You will know you are doing correctly when you see

(CTRL + K) was pressed waiting for second key of chord……

06 - waiting for key

If you get it wrong and Press CTRL + K + M then you will open the Extensions search for keymaps.


07 - incorrect.PNG

This is a brilliant feature enabling you to copy key mappings for the programmes you use all the time and save you from learning the Code key mappings. You can find the keymaps in the Extensions Marketplace as well as by pressing CTRL + K + M


Using Twitter with VS Code

So today I saw this tweet from Mathias in reply to Stefan and Amanda

01 - tweet.PNG


That looks cool. Twitter in VS Code, one less programme to open Here how it works

Open VS Code and hit CTRL + P and type ext install twitter or hit CTRL + SHIFT + X to open the extensions and search

02 - install.PNG


Hit install and then reload

03 - reload


Accept the prompt

04 - prompt.PNG

and you will have a Twitter button in the bar at the bottom

05 - bar

If you click it then the top bar will change to the set up wizard

06 - forst time.PNG

Follow it along and create a Twitter App.

07 - want to continue

08 - create an appWhich will open up the website.


09 - create an app.PNG

Just fill in the blanks

010 - fil in the blanks.PNG

and then  you will have this window

011 - app settings.PNG

Click on keys and Access tokens

012 - settings and appl

Check the App Permissions are set to read and write

014 - app permissions

and then click create my access token

Then go back to VS Code and click

016 - settings details

Now you are shown the settings.json (which you can always find by File –> Preferences –> Settings )

017 - settingsjson.PNG

This bit, the wizard doesn’t explain very well (hence this post) If you have already some settings between the curly braces, you will need to put a comma and then paste the below code. If you do not then paste the below code between the curly braces


and then paste the relevant keys and tokens from your twitter app between the double quotes

THEN PRESS CTRL + S to save the settings.json – Its in caps as when I showed someone they were too excited and didn’t save it!!

Now its all set up you can use the extension. Click the twitter button in the bar and

018 - Twitter actions

Now you can have your Home timeline in Code

019 - twitter home.PNG

Yes there are still a few seats left for the Europe PowerShell Conference You can search, see your mentions, your user page

020- mentions.PNG

and post 🙂 You can just press F1 and start typing twitter to get the commands

021 - comands

022 - tweet.png



023 - tweet.PNG

You can also message people using D message username. You can see the extension repo on GitHub which will be a good place to raise issues, bugs, feature requests

I’m not suire it will Increase my productivity !! but it really pleases the nerd in me!



Why VS Code Increases my Productivity

Last week I was showing a co-worker some PowerShell code and he asked what the editor was that I was using. Visual Studio Code I said. Why do you use that? What does it do?

This is what I showed him

Runs on any Operating System

Code (as I shall refer to it) is free lightweight open source editor which runs on all the main operating systems. So you have the same experience in Linux as on Windows. So there is less to learn


You can add new languages, themes, debuggers and tools from the extensions gallery to reduce the number of programmes you have open and the need to switch between programmes

You can add extensions using CTRL + SHIFT  + X and searching in the bar

01 - Extensions

or by going to the Extensions gallery searching for the extensions and copying the installation command

02 - extensions gallery.PNG


There is a rich de-bugging experience built in

03 - debugging.PNG

You can learn about debugging from the official docs and Keith Hill wrote a blog post on Hey Scripting Guys about debugging PowerShell


An absolute must to make life simpler. Code has intellisense for PowerShell and T-SQL which I use the most but also for many more languages . Read more here

Git integration

I love the Git integration, makes it so easy to work with GitHub for me. I can see diffs, commit, undo commits nice and simply. Just open the root folder of the repository and its there

04 - git

This page will give you a good start on using git with Code

No distractions

With full screen mode (F11) or Zen mode (CTRL +K, Z) I can concentrate on coding and not worry about distractions

Stay in one programme and do it all

I have a Markdown document, a PowerShell script and a T-SQL script all in one Git repository and I can work on all of them and version control in one place. The screencast below also shows some of the new capabilities available in the insiders version I managed to leave the screen recording dialogue open as well, apologies and the mistake was deliberate!

I used the GitLens and SQL beautify extensions as well as the dbatools module in that demo

That’s why I am using Code more and more these days, hope it helps

Happy Automating!



Why Volunteer at SQLBits ?

WARNING – Contains Orange and light-hearted photos 😉

I have returned home from SQLBits 2017 The Disco Edition. I am exhausted, my body is pointing out to me in a variety of ways that this is the only week of the year that I spend so much time on my feet. Why would anyone do it?

Many months of work

First though, you need to know that the SQLBits conference is organised and run by volunteers. All of the committee spend many, many long hours, out of their own free time, for many months before and after the event to ensure that the attendees, sponsors, speakers and volunteers experience is trouble free. I think that they do an amazing and fantastic job and repeatedly pull off the best, most enjoyable conference that I have been to.

Thank you Simon, Chris, Darren, Allan, Alex, Jonathan, Annette

Thank you also to their families as well, who undoubtedly miss out on time with them whilst they are organising everything to do with the event, from finding venues, organising dates, speakers, marketing, website, sponsors, printing, audio visual, THE PARTY!! and all the other big and small things that it takes to make an event of that size occur.

Orange Shirted Wonderful Folk

There is another group of volunteers that you will have seen at SQLBits. For the last couple of years we have been the ones in the orange shirts.

Here is the Class of 2017


I think this is a brilliant colour as it makes us easy to spot (although a couple of attendees who also had orange tops on did get stopped and asked for directions 🙂 )

What do they do?

These folk come in early and get everything set up. Sometimes we have to explain that the event isn’t ready for you yet

WP_20170406_07_31_20_Pro.jpgWe sort out the registration desk and greet every attendee, speaker and sponsor and assist them.


We help the speakers get set up


and ensure they have everything they need.

aaron bertrand.jpg

Aaron Bertrand (above) and John Martin from SentryOne said that it is the best experience for a speaker that they have had anywhere.

We direct and assist the attendees to be in the right place, sometimes with some flair!


We ensure that any issues are resolved quickly and with as little distraction as possible. The room is too hot, too cold, too noisy or too quiet. The projector isn’t working or the speakers microphone has a buzz, there is too much light or too little. The water coolers are empty. The rubbish needs picking up. All these and many other minor complications are communicated and passed to the correct people to get resolved.

WP_20170404_17_51_33_Pro (2).jpg

Sometimes we have to resolve our own issues. We had folks who were called by their work and had to stop helping and go back to their day jobs for a few hours. We all understand what it is like for people working in technology and adapt and manage accordingly. In almost every photo I took, there is someone in an orange shirt to be seen.


We answer numerous questions from the 1500 or so attendees (and the odd sheep) who came this year.


From timings and locations to taxi numbers or restaurants. Unfortunately I did not beat last years “Best question I have been asked at SQLBits” which was

Excuse me, I have a Dalek in the van . What would you like me to do with it?

I was even asked questions on the way back to the hotel gone midnight on Saturday!!

We stay afterwards and help to get ready for the next day, putting out the new signs for the domes and the required paperwork.

So why do we do it?

I asked the guys and gals this question and in their own words, this is why they do it

Being a volunteer at SQLBits is not easy. I’m writing this three days after the event and my legs are still sore. Most days are 11 hours long and you will be standing for most of them. Very often the sessions are full, so you’ll be giving up your seat to one of the attendees. Lunches and breaks are shorter as you are either cleaning down the last session or getting ready for the next. When things go wrong, and they do, you’ll need to get them fixed as quickly as possible even if you have not had  coffee yet.

You do get to attend sessions but you might not always get your first choice. This can be both a good and bad thing. Very often I have filled in on sessions that I normally wouldn’t attend as they are outside my direct area of work, only to find them the most interesting as I get to see how the other half lives.

So why do I keep coming back? Well it’s fun. We have a laugh, even on reception when it’s busy you get to joke with the attendees, speakers and other helpers. There is pizza, beer and jokes while bag packing. Odd expresso calls!  Working along side some else is a great way to get to know them. I live outside the normal SQL community structures, my nearest user group is a 150 miles away. So I don’t get to interact with other SQL family members as often as others. But even so, I know as soon as I walk into SQL Bits, there will be a chorus of, “Hey Conan, how have you been?” from people I haven’t seen in a year. There is also something about wearing a bright orange shirt that seems to attract interactions from the attendees.

All because of the of the experience that is being a volunteer.

Conan Farrell

WP_20170406_10_33_15_Pro (2).jpg

I owe a lot to the SQL Community. It was at SQLBits a few years ago that someone convinced me to start speaking. That encouragement and acceptance from the #SQLFamily put into motion a series of events that lead to me quitting the best job of my life last year in favour of an adventure to set up my own company. It’s been a wonderful journey and if it had not been for SQLBits (and SQL Relay, and SQL Saturdays, and others) I wouldn’t have taken it.

I have a debt to pay. And it’s wonderful to be able to contribute towards giving other people the same opportunities that the community has given me.

Also, for similar reasons, I recently joined the committee for SQL Relay. While I’ve been a sponsor and a speaker a lot before, I am fairly inexperienced at organising events. Helping out with the awesome SQLBits team has been a great learning curve. I hope to take what I have learned and apply it to my role for SQL Relay.

Finally, frankly, there are so many great people in the SQL Community it is just wonderful to be able to work with them during the day. (And share some beers with them in the evening!)

Alex Yates


I volunteer at SQLBits and in the wider Data Platform community for various reasons. The community is the glue in our industry and being part of that glue is both a privilege and an honour. Without volunteers these awesome events couldn’t function and we couldn’t share knowledge as freely. There also would not be the same opportunities to network with such great people and experts in the same field. I am proud to be part of the SQL Family and facilitate the learning that being a volunteer offers to professionals just like me. Stronger together.

Paul Andrew

WP_20170404_16_03_17_Pro (2).jpg

When I volunteered, I felt I’d like to give back to the SQL Community a bit of the much I’ve received from them. I wanted to become more engaged.
I didn’t even dream it would be like this, the terrific team I found here was so great that I can barely wait for the next one.

Miguel Oliveira

Volunteering gives you the experience of the effort & reward of running an event.. You build an appreciation of generating success, creating networks of colleagues and friends, being quick to react, and helps you walk away with the feeling of “I contributed to this”. Everyone should volunteer, even if its just once.

Ben Watt

WP_20170405_09_31_24_Pro (2).jpg

This year was my 10th sqlbits (remembering we skipped unlucky 13) and 7th I’ve been on the helping team for. I started helping when I learned my original conference companions were moving on to different technologies. Something I’ve never understood.

Historically I’ve worked in multiple companies as a lone dba, something I’ve found many at SQLBits can relate to. Through user groups and SQLBits I’ve met lots of others in the same boat over the years. It can be a frustrating job defending servers, implementing best practice and writing sql when all your application team want to do is add columns and ship the next release!

Yes, there are good networking opportunities and the parties are great but at the core is great quality training. I’ve had a great time playing a small part in helping deliver the experience over the years. Occasionally I have to shout about feedback forms or seating arrangements but on the whole folk are fine with it.  If I’m honest they are long days. A typical day involves a start and end meeting in addition to sessions you are monitoring. Add to that hotel commutes, catching up with the friends you made last year, meals, drinks etc and its 1am. Oh, and tomorrow’s meeting is 7.30 eek….

It’s been a great journey watching the progression of events. Each one adds to the last, they run a lot slicker now. The sponsors are first class and always embrace the theme with their stalls and giveaways. I do wish more folk would enter the prize draws and stop me winning though, it’s getting embarrassing now 🙂

Richard Doering 


The best Data Platform conference in Europe! I couldn’t miss the opportunity to be much more than just an attendee.

Hubert Kobierzewski

I volunteer for sqlbits, to get experience from a multi day event that has a lot of attendees. Lots of take aways to make local community events run smoother for the delegates.
Thats my main reason

Jens Vestergard

There is no way I could not use this next photo celebrate this years Most Valuable Helper.

WP_20170407_20_45_12_Pro (2).jpg

Thank you Shaun Atkinson you were amazing and thank you for looking after my former intern James as a first time volunteer

How can I do it next year?

We always welcome new people to our team. If you would like to volunteer your time to help at the next SQLBits please send an email to with a subject of I would like to volunteer or something similar. Nearer the time of next years SQLBits (No I don’t know when or where it is, I will know when you do) the awesome, amazing, fantastic, brilliant, organising amazeballs also known as Annette will get in touch with you with further details

You can expect to have a lot of fun, make new friends and help make the best data platform conference the best data platform conference

Oh and sometimes theres free pizza 🙂


Another Thank You

Some lovely people, (I am really sorry but I didn’t get everyones name) brought sweets, biscuits, cakes and other goodies for the crew. Thank you, they were very much appreciated.

Pictures because smiles 🙂

Not all of the volunteers wanted to give feedback publically but I had some cracking photos so I thought I would share them as well. Enjoy

This slideshow requires JavaScript.

Export SQL User Permissions to T-SQL script using PowerShell and dbatools

There are times when DBA’s are required to export database user permissions to a file. This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with the same permissions as another user or perhaps nearly the same permissions. I was having a conversation with my good friend and MVP Cláudio Silva and we were talking about how Export-SqlUser from dbatools could help in these situations and he suggested that I blogged about it so here it is.

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

Cláudio wrote Export-SqlUser to solve a problem. You should always start with Get-Help whenever you are starting to use a new PowerShell command

Get-Help Export-SqlUser -ShowWindow

01 - get help.PNG

The command exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions and also the Create Role statements for any roles, although the script does not create IF NOT EXISTS statements which would be an improvement. It also excludes the system databases so if you are scripting users who need access to those databases then that needs to be considered. Cláudio is aware of these and is looking at improving the code to remove those limitations.

It takes the following parameters
  • SqlInstance
    The SQL Server instance name. SQL Server 2000 and above supported.
  • User
    Export only the specified database user(s). If not specified will export all users from the database(s)
  • DestinationVersion
    Which SQL version the script should be generated using. If not specified will use the current database compatibility level
  • FilePath
    The filepath to write to export the T-SQL.
  • SqlCredential
    Allows you to login to servers using alternative credentials
  • NoClobber
    Do not overwrite the file
  • Append
    Append to the file
  • Databases
    Not in the help but a dynamic parameter allowing you to specify one or many databases

Lets take a look at it in action

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Users.sql
Notepad C:\temp\SQL2016N2-Users.sql

02 - Export user server.PNG

Lets take a look at a single database

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Fadetoblack.sql -Databases Fadetoblack
notepad C:\temp\SQL2016N2-Fadetoblack.sql

03 single database.PNG

This is so cool and so easy. It is possible to do this in T-SQL. I found this script on SQLServerCentral for example which is 262 lines and would then require some mouse action to save to a file

We can look at a single user as well. Lets see what Lars Ulrich can see on the FadeToBlack database

04 - export lars.PNG

USE [FadetoBlack]
DENY INSERT ON [dbo].[Finances] TO [UlrichLars]
DENY SELECT ON [dbo].[RealFinances] TO [UlrichLars]
GRANT SELECT ON [dbo].[Finances] TO [UlrichLars]

So he can select data from the Finances table but cannot insert and cannot read the RealFinances data. Now lets suppose a new manager comes in and he wants to be able to look at the data in this database. As the manager though he wants to be able to read the RealFinances table  and insert into the Finances table. He requests that we add those permissions to the database. We can create the T-SQL for Lars user and then do a find and replace for UlrichLars with TheManager , DENY INSERT ON [dbo].[Finances] with GRANT INSERT ON [dbo].[Finances] and DENY SELECT ON [dbo].[RealFinances] with GRANT SELECT ON [dbo].[RealFinances] and save to a new file.

$LarsPermsFile = 'C:\temp\SQL2016N2-Lars-Fadetoblack.sql'
$ManagerPermsFile = 'C:\temp\SQL2016N2-Manager-Fadetoblack.sql'
Export-SqlUser -SqlInstance SQL2016N2 -FilePath $LarsPermsFile -User UlrichLars -Databases Fadetoblack
$ManagerPerms = Get-Content $LarsPermsFile
## replace permissions
$ManagerPerms = $ManagerPerms.Replace('DENY INSERT ON [dbo].[Finances]','GRANT INSERT ON [dbo].[Finances]')
$ManagerPerms = $ManagerPerms.Replace('DENY SELECT ON [dbo].[RealFinances]','GRANT SELECT ON [dbo].[RealFinances]')
$ManagerPerms = $ManagerPerms.Replace('UlrichLars','TheManager')
Set-Content -path $ManagerPermsFile -Value $ManagerPerms

I will open this in Visual Studio Code Insiders using

code-insiders $LarsPermsFile , $ManagerPermsFile

if you are not using the insiders preview remove the “-insiders”

05 - code insiders.PNG

You can right click on the Lars file and click select for compare and then right click on the Managers file and select compare with Lars File and get a nice colour coded diff

06 - compare.gif

Perfect, we can run that code and complete the request. When we impersonate Lars we get

07 - lars.PNG

but when we run as the manager we get

08 - the manager.PNG


Excellent! All is well.

It turns out that there is another Fadetoblack database on a SQL2000 instance which for reasons lost in time never had its data imported into the newer database. It is still used for reporting purposes. The manager needs to have the same permissions as on the SQL2016N2 instance. Obviously the T-SQL we have just created will not work as that syntax did not exist for SQL 2000 but Cláudio has thought of that too. We can use the DestinationVersion parameter to create the SQL2000 (2005,2008/20008R2,2012,2014,2016) code

We just run

Export-SqlUser -SqlInstance SQL2016N2 -Databases FadetoBlack -User TheManager  -FilePath C:\temp\S
QL2016N2-Manager-2000.sql  -DestinationVersion SQLServer2000
Notepad C:\temp\SQL2016N2-Manager-2000.sql

and our SQL2000 compatible code is created

09- manager 2000.PNG

Simply awesome. Thank you Cláudio

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use


Testing SQL Server Access to a share with PowerShell using dbatools

A good security practice is to backup our SQL Servers to a network share but not allow users to be able to browse the share. How can we ensure that our SQL Server has access or test it if it has been set up by someone else?

Lets set this up.

First lets create a share for our backups

Description='The Place for SQL Backups'
New-FileShare @FileShareParams

This will create us a share called SQLBackups on the D drive of the server beardnuc, but without any permissions, lets grant permissions to everyone

 Name = 'SQLBackups'
 AccessRight = 'Modify'
 AccountName = 'Everyone'}
Grant-FileShareAccess @FileSharePermsParams

01 file share.PNG

The share is created and I can access it and create a file

02 - create a file.PNG

and as we can see the permissions are granted for everyone

03 -permissions.PNG

OK, that’s not what we want so lets revoke that permission.

Revoke-FileShareAccess Name SQLBackups AccountName 'Everyone'

04 revoked.PNG

Now lets add permissions just for our SQL Server Service Accounts

$FileSharePermsParams = @{
Name = 'SQLBackups'
AccessRight = 'Modify'
AccountName = 'SQL_DBEngine_Service_Accounts
Grant-FileShareAccess @FileSharePermsParams 
and explicitly deny our DBA user accounts from accessing them.
$BlockFileShareParams = @{
Name = 'SQLBackups'
AccountName = 'SQL_DBAs_The_Cool_Ones'
Block-FileShareAccess @BlockFileShareParams
In the GUI our permissions look like this
and when I try to access as THEBEARD\Rob I get this

07 -no permissions.PNG

So how can I check that I have access from my SQL Server? Sure I could get the password of the SQL Service account and run a process as that account, not saying that’s a good idea but it could be done. Of course it couldn’t be done if you are using Managed Service Accounts or Group Managed Service Accounts but there is a way

Enter dbatools to the rescue 😉 The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

There is a command called Test-SqlPath As always start with Get-Help

Get-Help Test-SqlPath -Full

08 - get help.PNG

So it uses master.dbo.xp_fileexist to determine if a file or directory exists, from the perspective of the SQL Server service account, has three parameters Sqlserver, Path and SqlCredential for SQL Authentication. Of course if that stored procedure is disabled on your estate then this command will not be of use to you. With that in mind, lets run it and see what it does
Test-SqlPath -SqlServer sql2016n1 -Path \\beardnuc\SQLBackups
09 - path test

That’s good I have access, lets back a database up

Backup-SqlDatabase -ServerInstance SQL2016N1 -Database DBA-Admin -CopyOnly -BackupAction Database -BackupFile '\\BeardNuc\SQLBackups\Test-DBA-Admin.bak'
Ah, I cant show you as I don’t have access. Better get in touch with the data centre admin to check 😉 Luckily, I am my own data centre admin and have another account I can use 🙂
10 - check

So what if we want to test all of our servers for access to the new share? I tried this

$SQLServers = (Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
Test-SqlPath -SqlServer $SQLServers -Path '\\BeardNuc\SQLBackups'
but unfortunately I hit an error
11 - error.PNG
It seems that at the moment (version 0.8.942) this command only accepts a single server. This is what you should do if you find either a bug or have an idea for dbatools. Raise an issue on Github
Navigate to the GitHub repository and click on issues. I generally search for the command name in the issues to see if someone else has beaten me to it
12 - issues
If those issues don’t match yours then click the green New Issue button
There is a template to fill in which asks you to specify your Windows, PowerShell and SQL versions with the commands that you need to do so included. Please do this and paste the results in as it will help the folks to replicate the issues in the case of more complicated  bugs
I created this issue with a potential fix as well, you don’t have to do that, just letting the folks know is good enough
Until that issue is resolved, you can check all of your servers as follows
$SQLServers=(Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
foreach($Server in $SQLServers)
$Test = Test-SqlPath -SqlServer $Server -Path '\\BeardNuc\SQLBackups'
Server = $Server
Result = $Test
13 - servers.PNG
and if I remove one of the service accounts from the group and restart the service an run the command again
14 - one fails.PNG
So that’s how to use dbatools to check that your SQL Server have access to a Network share and also how to create an issue on GitHub for dbatools and help it to get even better

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use


Testing the Identity Column usage in SQL Server with PowerShell and dbatools

SQL Server uses identity columns to auto generate values, normally keys. When you create an identity column, it has a data type and that data type has a maximum number of values.

  • BigInt 9,223,372,036,854,775,808
  • Int 2,147,483,647
  • SmallInt 32,767
  • tinyint 255

What happens when you try to insert a value in an identity column that is greater than the maximum value? You get an error and a failed transaction. Lets do that

Using AdventureWorks, I know (I’ll show how in a minute) that the HumanResources.Shift column is a tinyint. So the highest value for the ShiftID column is 255.

If we run

USE AdventureWorks2014;
INSERT INTO [HumanResources].[Shift]
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
WAITFOR DELAY '00:00:00.050';
GO 252
Adding a number after GO says run this that many times, so we have added 252 rows to the existing 3 rows.
01 - maxx value.PNG


So what happens if we try to add another row?

USE AdventureWorks2014;
INSERT INTO [HumanResources].[Shift]
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
02- error.PNG
We get an error
Msg 8115, Level 16, State 1, Line 4
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
If that is a table that is important to your system, a logging table or worse, an order table then there is quickly going to be phone calls, visits to your desks, arm waving etc until you get it resolved. Lets clean up our mess
USE AdventureWorks2014
DELETE FROM HumanResources.Shift
WHERE ShiftId > 3
DBCC CHECKIDENT ('HumanResources.Shift', RESEED, 3)
It would be very useful to be able to quickly see what the current values of the identity columns are and how close they are to being full so that we can plan for and be able to take action before we end up with shouty smart suits at our desk. If we could do it with just one line of code that would be even easier.
Step forward dbatools.  This PowerShell module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit
There is a command called Test-DbaIdentityUsage This command was created by Brandon Abshire. You can find Brandon blogging at Thank you Brandon
As always with a new PowerShell command you should always start with Get-Help
Get-Help Test-DbaIdentityUsage -ShowWindow
03 - get help.PNG


The command has a few parameters

  • SqlInstance – One or many Instances
  • SqlCredential – for SQL Authentication
  • Databases – to filter for databases ( This is a dynamic parameter and doesn’t show in the Help)
  • Threshold – define a minimum percentage for how full the identity column is
  • NoSystemDB – to ignore the system databases

So we can run the command against one instance

Test-DbaIdentityUsage -SqlInstance sql2014ser12r2


04 - one server.PNG

This returns an object for each identity column in each database on the instance. The object has the following properties

ComputerName   : SQL2014SER12R2
InstanceName   : MSSQLSERVER
SqlInstance    : SQL2014SER12R2
Database       : AdventureWorks2014
Schema         : HumanResources
Table          : Shift
Column         : ShiftID
SeedValue      : 1
IncrementValue : 1
LastValue      : 3
MaxNumberRows  : 254
NumberOfUses   : 3
PercentUsed    : 1.18

We can use the objects returned from this command in a number of ways, this is one of the beauties of PowerShell that we can interact with numerous systems. I have blogged about some simple ways of doing this here but your only limit is your imagination.

I love to use Out-GridView as it enables quick and easy sorting of the returned data

06 - ogv filter.gif

The databases parameter is dynamic so it will prefill the names of the databases on the instance. This is what it looks like in VS Code

07 vscode tab.gif


and in ISE

08 ise tab.gif


We can use the threshold parameter to only show results for the identity columns whose value is above a percent of the max value for the column. Lets fill the ShiftId column to above 90% and show this

USE AdventureWorks2014;
INSERT INTO [HumanResources].[Shift]
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
WAITFOR DELAY '00:00:00.050';
GO 230

and now run

Test-DbaIdentityUsage -SqlInstance sql2014ser12r2  -Threshold 90

08 - threshold.PNG

Don’t forget to use the cleanup script. You can pass a whole array of SQL instances to the command. We can pass an array of SQL servers to this command as well and check multiple servers at the same time. In this example, I am querying my Hyper-V server for all VMs with SQL in the name,except for my broken SQL2008 box ,that are running. Just to get some results I will set the threshold to 1

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
Test-DbaIdentityUsage -SqlInstance $SQLServers -Threshold 1 | Out-GridView
10 ogv thredshold.PNG
As you can see this function does not support SQL instances lower than SQL 2008 and you will get warnings for availability group databases
It’s quick too, finishing in less than 2 seconds in my lab of 10 SQL Servers and 125 databases. The WarningAction SilentlyContinue supresses the yellow warnings
11 - measure command.PNG
This is ideal for using Pester to test.
 Describe "Testing how full the Identity columns are" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}
It "<Name> databases all have identity columns less than 90% full" -TestCases $testCases {
(Test-DbaIdentityUsage -SqlInstance $Name -Threshold 90 -WarningAction SilentlyContinue).PercentUsed | Should Be
12 pester test.PNG
An excellent quick test but it doesn’t show us which databases have failed. We can iterate through our servers and databases like this
Describe "Testing how full the Identity columns are" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($SQLServer in $SQLServers)
        Context "Testing $SQLServer" {
            $dbs = (Connect-DbaSqlServer -SqlServer $SQLServer).Databases.Name
            foreach($db in $dbs)
                It "$db on $SQLServer identity columns are less than 90% full" {
                    (Test-DbaIdentityUsage -SqlInstance $SQLServer -Databases $db -Threshold 90 -WarningAction SilentlyContinue).PercentUsed | Should Be
This is using the Connect-DbaSqlServer to create a SMO object and then gathering the databases on the server into a variable and iterating through them
It looks like this when it is running
13 - pester test.png
and at the end gives you a little overview of the number of tests that have failed
14 end of pester test.png
In a previous post I showed how you can output these results to XML or even make a HTML page showing the output
But perhaps that isn’t granular enough for you and you want a test for each column. This is how you could do that
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
foreach($SQLServer in $SQLServers)
    Describe "$SQLServer - Testing how full the Identity columns are" {
            $dbs = (Connect-DbaSqlServer -SqlServer $SQLServer).Databases.Name
            foreach($db in $dbs)
                Context "Testing $db" {
                $Tests = Test-DbaIdentityUsage -SqlInstance $SQLServer -Databases $db -WarningAction SilentlyContinue
                foreach($test in $tests)
                    It "$($test.Column) identity column in $($Test.Table) is less than 90% full" {
                        $Test.PercentUsed | Should BeLessThan 90
Which looks like this, a test for each identity column in each database in each server in your environment
15 every pester teest.PNG


The other question that we have to answer these days is – Does it work with SQL on Linux? We will have to pass a SQL authentication credential and this time I will use Format-Table for the output

 Test-DbaIdentityUsage -SqlInstance LinuxvNextCTP14 -SqlCredential (Get-Credential) | Format-Table

16 - on Linux.PNG

Happy Automating!

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use