TSQL-Tuesday #104: Code You Would Hate To Live Without


This month’s TSQL Tuesday is hosted by Bert Wagner (w | t) and he asks us to write about code you’ve written that you would hate to live without.

Over the years I’ve used other people’s code regularly. It could have gone from Github, Stack Overflow, blogs, etc.

I have always appreciated the way the SQL Server community (and other communities) share their code to help others.

I have worked alongside lots of people who were not very keen on sharing. People told me that their code was theirs and only theirs and that nobody is to see it. Although I don’t agree, I do understand people not wanting to share their code. They may have worked hard on it and the person could have an advantage over other people who don’t have that “special” piece of code. There is a couple of caveats to that:

  1. Your code could have bugs that you never noticed
  2. Your code could be improved by other people with different views

I am completely the opposite. If my code can help one person to make their life easier, I have already won.

In the last few years that has been proven because I have been contributing to dbatools. Because I contributed I have learned a lot about open source projects, learned a lot about SQL Server. I met new people from all over the world who in their turn shared their code to make my life easier.

Code that I couldn’t live without

Back to the task at hand and tell you about code that I’ve written that I cannot live without.

Anything I create it is usable for other DBAs tends to end up in the dbatools module. The reason I do that is that it’s an amazing platform and it makes it easy for me to reach a large audience.

Testing Backups

One that I cannot live without is the dbatools command called Test-DbaLastBackup. Testing your backups is one of the most important things you can do as a DBA. I did not write that command but I did use it in one of my commands. It’s a wrapper around that command that sends me an email with all the tested backups and the results in a CSV file. The code is freely available on my blog.

Log Shipping

Some other things I’ve written are the log shipping commands in dbatools. At my current workplace, we have log shipping enabled. Everybody who has set up log shipping knows it’s a PITA if you have lots of databases.

I recently presented a session about the log shipping commands on the PASS DBA Virtual Chapter. The session was recorded and if you’re interested you can watch it through this link.

One log shipping command in particular that I use regularly is the Test-DbaLogShippingStatus.

This command makes it easy for me to check up on my log shipping servers and see the status of each database.

SQL Server Agent

I don’t like to click around and if I can do something in a console I choose to do that. In most cases, it is faster than the alternative.

The same goes for the SQL Server Agent. You have your jobs, schedules, job steps, etc. For all these objects I have created commands in dbatools.

The commands enable you to create, edit or remove any of those objects with ease. So these are the script I cannot live without. Maybe these scripts can help you in your journeys through SQL Server.