Voice of the DBA

Tag: SQLNewBlogger

Removing SSMS Completion Time–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

If you’ve upgraded your SSMS lately, you might have seen this. Queries now have a completion time on the Messages window.

On one hand, I think this is great, because I can tell that I’ve run this recently. On the other hand, I might have multiple batches, so I can get confused or mis-informed. I also just don’t like this.

Microsoft didn’t agree, because at first, I couldn’t remove this. That was really annoying. However, with SSMS v18.3.1, I can.

If I go to the SSMS Options, and then go to Query Execution | SQL Server | Advanced, I see this:

At the bottom of the first column is the Show completion time checkbox. Uncheck this, and suddenly, I’m happy.

One caveat, I do need to open a new window. That old window, even if I change the connection, still shows the time. Fortunately, CTRL+A, CTRL+C, CTRL+V help me move on.

SQLNewBlogger

This was one of those annoying things I needed to fix because it impacted my productivity. It’s a perfect post of my finding a way to be more productive and learning on my own.

October 30, 2019
Arrays in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This isn’t necessarily an expert post, but more a recognition that I learned something. I knew about objects in PowerShell, but I’d never actually used an array. Never had a need.

However, while writing my article about striped backups, I ended up using arrays. This was mainly from the example shown by Anthony Notencino in his Restoring Backups from Azure Blob with dbatools.

Here’s a way to initialize an array:
```
$a = @()
```
If I look at this, I get nothing back, which makes sense. There’s nothing in there.

I can “add” values to this array with +, as in:

$a += 1
$a += 2

If I now look at the variable, I see this:

One interesting thing here is that += is different than Add(). Look at this:

So why does += work? In the array doc, there’s this quote:

When you use the += operator, PowerShell actually creates a new array with the values of the original array and the added value. This might cause performance issues if the operation is repeated several times or the size of the array is too big.

Worth knowing this. I think for most scripts this isn’t a big issue, but be aware that you are allocating a new array.

Working with arrays is a topic for another day, but this is a short look at how you might store something like a list of files in a single place.

SQLNewBlogger

While working on solving the problem for a customer, I realized this was a little thing I learned. A quick post to show that here, just about 10 minutes or writing and structuring code.

A good chance to give an interviewer something to ask you. Write your own post on arrays.
October 23, 2019
Setting Permissions for a SQL Server backup folder–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

While testing a script recently, I needed to set a few backup folders for my instance. This was a striped backup, and using one folder wouldn’t make sense. In modern OSes, we can’t just create a folder and expect that our processes can read it. We need to explicitly set permissions.

It’s fairly easy for SQL Server, but since it wasn’t obvious, I decided to take a minute and document this.

If I create a folder, say c:\sqlbackup, I see this kind of thing in properties on my Window 10 machine.

If I click “Edit”, I get a similar view.

Now, my SQL Server process can’t access this. If I try to restore a backup from here, I’ll get a permissions error.

That’s fine. In the Permissions, I can click Add and I’ll get this dialog.

From here, I can enter “NT Service\MSSQLServer” or “NT ServiceMSSQL$SQL2017” for a named instance. My named instance is SQL2017. Note the space in “NT Service”

If I click the “Check Names”, this will resolve for the built in service account.

Then I can click OK and set the appropriate permissions.

If your service account is something not built in, it’s usually easy to find and add, but for build in accounts, you need the “NT Service”.

SQLNewBlogger

You can take a simple thing here that you needed to solve and write about it. This took my about 5 minutes to solve, playing with different names, and then about 5 minutes to write.

Showcase your knowledge today.

October 16, 2019
Just Getting Specific Files in PoSh–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Just a quick one today, but a tip I hope I remember. While working on a script, I wanted to just get backup files in PoSh. I tried this:
```
Get-ChildItem -Path $BackupPath -File -Include *.bak
```
That got me this:

Nothing.

Why not? Apparently, you need the –Recurse parameter, even though there are no subdirectories in this folder. A bug, IMHO, but who knows. In any case, adding the parameter makes things work. Thanks, SO.

SQLNewBlogger

This was a quick 5 minute thing. After researching and testing for 10 minutes, and finding the solution, I wrote this up.

You could do that with anything you get working.
October 9, 2019