SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL DBA With A Beard

Rob is a SQL Server DBA currently supporting several hundred databases ranging from SQL 2000 to SQL 2012. Rob has a fabulous beard and loves to use Powershell to make his life easier.

Pester for Presentations – Ensuring it goes ok

Whilst I was at PSCONFEU I presented a session on writing pester tests instead of using checklists. You can see it here

During the talk I showed the pester test that I use to make sure that everything is ready for my presentation. A couple of people have asked me… Read more

3 comments, 221 reads

Posted in SQL DBA With A Beard on 16 May 2017

Pester Test Inception and the Show Parameter

My fantastic friend Andre Kamman b | t  and I presented at PSConfEu last week

and whilst we were there we were chatting about running Pester Tests. He wanted to know how he could run a Pester Test and not lose the failed tests as they scrolled past him. In… Read more

0 comments, 128 reads

Posted in SQL DBA With A Beard on 9 May 2017

PowerShell Function – Validating a Parameter Depending On A Previous Parameter’s Value

I was chatting on the SQL Community Slack with my friend Sander Stad b | t about some functions he is writing for the amazing PowerShell SQL Server Community module dbatools. He was asking my opinion as to how to enable user choice or options for Agent Schedules and… Read more

0 comments, 127 reads

Posted in SQL DBA With A Beard on 26 April 2017

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… Read more

0 comments, 405 reads

Posted in SQL DBA With A Beard on 24 April 2017

Using Twitter with VS Code

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


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 +… Read more

0 comments, 575 reads

Posted in SQL DBA With A Beard on 19 April 2017

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… Read more

3 comments, 157 reads

Posted in SQL DBA With A Beard on 13 April 2017

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.… Read more

3 comments, 204 reads

Posted in SQL DBA With A Beard on 11 April 2017

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… Read more

0 comments, 312 reads

Posted in SQL DBA With A Beard on 10 April 2017

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… Read more

0 comments, 736 reads

Posted in SQL DBA With A Beard on 8 April 2017

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… Read more

1 comments, 192 reads

Posted in SQL DBA With A Beard on 7 April 2017

Using Pester with Get-DbaLastGoodCheckDb from dbatools

In my last post I showed Get-DbaLastGoodCheckDb  from dbatools. This 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 https://dbatools.io

In a similar fashion to my post… Read more

0 comments, 155 reads

Posted in SQL DBA With A Beard on 6 April 2017

Getting SQLServers Last Known Good DBCC Checkdb with PowerShell and dbatools

As good SQL Server DBA’s we want to ensure that our databases are regularly checked for consistency by running DBCC CheckDB. This will be frequently scheduled using an Agent Job or by using Ola Hallengrens Maintenance Solution

We can check for the last known good DBCC Check using the undocumented… Read more

1 comments, 196 reads

Posted in SQL DBA With A Beard on 5 April 2017

Test the SQL Server database collation with PowerShell and dbatools

If your server collation is different to your database collation then you may find that you get an error similar to this

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

when your queries use the tempdb

It would be useful to be able to… Read more

0 comments, 144 reads

Posted in SQL DBA With A Beard on 3 April 2017

Max Length of a column in a DataTable in PowerShell

Whilst I was writing my Test-DbaLastBackup Posts I ran into a common error I get when importing datatables into a database

I was using this table

and when I tried to add the results of the Test-DbaLastBackup I got this

Exception calling “WriteToServer” with “1” argument(s): “The given value of…

Read more

0 comments, 158 reads

Posted in SQL DBA With A Beard on 1 April 2017

Getting SQL Server File Sizes and Space Used with dbatools

I read a great blog post about answering the question how big is the database using T-SQL on SQL Buffet and wondered how much I could do with the dbatools module

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community… Read more

0 comments, 163 reads

Posted in SQL DBA With A Beard on 29 March 2017

Test your Sqlserver backups on Linux with PowerShell and dbatools

I have written about Test-DbaLastBackup in posts here, here and here. They have been Windows only posts.

With SQL Server vNext CTP 1.4 now available and providing SQL Agent capability on Linux, I wrote here about using Ola Hallengrens scripts on Linux SQL Servers so can Test-DbaLastBackup work… Read more

1 comments, 128 reads

Posted in SQL DBA With A Beard on 27 March 2017

Using Pester with dbatools Test-DbaLastBackup

In previous posts I have shown how to use Test-DbaLastBackup from dbatools and how you can make use of the results. Today we will look at using  Pester with the results

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell.…

Read more

3 comments, 1,080 reads

Posted in SQL DBA With A Beard on 25 March 2017

Backing up SQL Server on Linux using Ola Hallengrens Maintenance Solution

With the release of SQL Server vNext CTP 1.4 SQL Agent was released for use on Linux. To install it on Ubuntu you need to upgrade your SQL Server to CTP 1.4. On Ubuntu you do this with

sudo apt-get update
sudo apt-get install mssql-server

Once you have CTP 1.4… Read more

1 comments, 1,264 reads

Posted in SQL DBA With A Beard on 22 March 2017

Taking dbatools Test-DbaLastBackup a little further

In a previous post I showed how easy it is to test your backups using Test-DbaLastBackup

Today I thought I would take it a little further and show you how PowerShell can be used to transmit or store this information in the manner you require

Test-DBALastBackup returns an object of… Read more

6 comments, 1,318 reads

Posted in SQL DBA With A Beard on 22 March 2017

Testing Your SQL Server Backups the Easy Way with PowerShell & dbatools

In a previous post I wrote about how easy it was to restore a whole SQL Servers user databases from a  directory using the dbatools module. Maybe it is a good idea to look at for disaster recovery scenarios but even PowerShell is going to be useless if your backups… Read more

4 comments, 393 reads

Posted in SQL DBA With A Beard on 20 March 2017

Older posts