Voice of the DBA

Category: Blog

Remove-DbaBackup with dbatools
I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One important item for any system administrator to manage is the removal of old files that aren’t useful. I know most of us hate to delete data, but there are log files, backups, and more that will clog up a drive over time if they’re not managed. I’ve had SQL Servers stop because old copies backups filled the disk and I’ve had IIS servers start throwing errors because 2 years worth of logs were on stored on the C drive.

Maintenance plans had a way to remove files and we have xp_delete_file, but there are limitations to ensure that only backup files are deleted. I think those are silly, but it wasn’t my decision to include restrictions, and I don’t get a vote on future changes.

In any case, dbatools has a cmdlet that can help: Remove-DbaBackup. I was interested to see if this worked on it’s own or had restrictions, but it seems to work wonderfully for me.

Required Parameters

Most cmdlets will allow quite a few parameters to be optional. In this case, however, there are some requirements. First, you need a path for the backup files. That makes sense and no big deal.

However, you also need a retention period. You can’t skip this, as if you do, you get a prompt.

The retention periods aren’t obvious, but not that hard to remember. There’s a numeric counter and a one character time period item. They are:
- h for hours
- d for days
- w for weeks
- m for months
That’s it and not a big deal, though for testing I need to play with my system clock a bit.

In any case, after this parameter, you need a backup extension. This is the file extension, without the period. You can put in anything, which is cool.

There are some other params, but not required.

For testing, I copied some backups and then changed some extensions. As you can see, my test folder has SQL backups with various extensions I’ve encountered as well as a few text documents.

If I run Remove-DbaBackup with some options, I’ll see what will happen with the –WhatIf parameter. I see plenty of files being marked for deletion as long as I have the right extension.

This is handy, and it makes perfect sense when you read it. This is exactly the type of maintenance job that you want to set up on a server to remove old files. I don’t know that I’d use this for general cleaning of files that I might need soon for a backup, since I always want to be sure that I have a good backup before I remove old files, but for managing very old files, this is helpful.

And, a little scripting logic would show you how to find the date of the most recent full backup and then remove files older than that. Or maybe older than the last two fulls.
April 13, 2018
Adding the KeyMap Extension in SQL Operations Studio

I’m playing with SQL Operations Studio a bit to get a feel for what works and what doesn’t. So far, I don’t love it over SSMS, but one of the main reasons is that many of my common keyboard shortcuts don’t work.

Then I saw this:

You can download the extension from here, but you can’t click it to install. You need to follow this process.

First, click the File menu and select “Install Extension from VSIX Package”.

Then find your download.

In a few seconds, you should see the message at the top of SOS.

Restart, and it should load.

As of when I installed it, only a few of the mappings were added. If you click the Extensions icon on the left (lowest box looking one) and then the extension, details pop in the pane. Click the Contributions link to see the commands.

So far things seem to work, but I need to add a couple more for myself.

Of course, until SQL Prompt gets ported, not sure I want to spend a lot of time in this tool.

April 12, 2018
Trekking Through Formatting Forest on the #SQLPrompt Treasure Map

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about Code Snippet Cove recently, and this post continues to move across the map.

Formatting Forest

Walking through a forest can be daunting. Living in Colorado, I’ve had the chance to hike and explore the mountains of the state. If you’re near the top and can view landmarks, it seems easy. Walking through some forests, when you can’t see a peak, you get a little worried about which direction to go without a trail. It’s serious, as people die every year in my state because they get lost in the woods.

Writing T-SQL isn’t a life or death endeavor, but it can be frustrating when we see code that’s formatted in a way that we don’t expect. I’ve seen truly ugly T-SQL code (from my perspective) in the SQLServerCentral forums, and I often need to copy and paste it into SSMS to get a sense of what’s happening. I used to reformat by hand, but CTRL+K,Y is an ingrained SQL Prompt habit that lets me get code into a style that I can work with.

The part of the treasure map that talks about formatting is long and detailed, and there are lots of resources that can help you format the code better. Likely, however, you’ll want to open the formatting dialog (shown below) and play with settings to get what you want.

One of the best features, at least for me, is that I can quickly switch styles from my own to any other, such as a corporate standard. With a right click, I can choose another style, CTRL+K,Y to get to some other format, and then reverse that later.

I do this before committing to Version Control, as you should. If you haven’t tried SQL Prompt formatting, get an eval and give it a try. It’s amazing. This alone saves me a ton of time when writing code.

In the next post, I’ll continue on to the Caves of Code Analysis.

April 11, 2018
T-SQL Tuesday #101–Tools
This month’s T-SQL Tuesday is brought to you by Jens Vestergaard. The invitation for #101 is for essential SQL Server tools. Since I work for a tool vendor, you can guess that I’m going to be shaded that way.

This is the monthly blog party on the second Tuesday of the month. You can participate by writing your blog, linking to the invite and leaving a comment there (or a pingback). This is the creation of Adam Machanic, and if you want to host, drop him a note.

My Essential SQL Server Tools

There are really three main tools I’d say that are needed for SQL Server work. At least, these are the ones I need.
1. SSMS – It’s free, and it’s the main tool for connecting to SQL Server. You might like VS Code or some other editor, especially on other platforms, but really SSMS is required for most people.
2. SQL Prompt – It’s expensive for some, but after using this for years, I think the the native intellisense hurts more than helps. Just formatting alone make this critical for me. I’m moving through quicksand, mixed with molasses, when I don’t have Prompt installed.
3. Git – Another free one, but an important one. As we write code, SQL Server will only keep the latest version. Much of what we do means that we need to understand where we came from with code. We do have integrations at Redgate for SQL code, but no matter what, just having a record of work is important, especially with multiple people. I love git, but if you want SVN or something else, use that. Just use something.
These are the main ones. Making these the main ones doesn’t mean that others aren’t important, but it just means these are the top of the list. After these, you can add in others. Here are the ones that I think are important for getting things done in a modern, multi-environment world.

SQL Search – From Redgate, but useful for finding things in a large environment. Way better than browsing.

Plan Explorer – I think SQL Monitor has a nice plan tool, but for those of you writing code and wanting to test it, this is way better than the native plan viewer.

DLM Dashboard – Free, and let’s you see and track those changes in production. Find out when code is being released and you aren’t aware of it.

Sublime Text – I tend to do some work in other languages, and in text, and having a quick, easy to use editor is nice. I think Sublime is great and worth the small fee.

Password Safe – Having long, secure passwords is important. I don’t try to remember them anymore, and I do want separate ones for every site. I like this open source tool, and have ports on my mobile devices.

Dropbox – Critical for me, since I use files on multiple machines. I keep copies of my presentations, password safe files, and more in sync across devices with Dropbox. Plus I can scan in docs from my phone if I need to send and share them.

dbatools – This is an amazing project. I hated working with the sqlps module and really avoided PoSh for many things before dbatools. Give this module a try and you may learn to love PowerShell.

SQLServerCentral – I’m biased, for sure. However, everyone could use a few things that we provide. A community, where you can get inspired and learn things (daily articles and editorial). A way to ask questions and get answers (our forums), and a place to keep scripts that are helpful for you (Scripts and Briefcase). These are important to rounding out your career, beyond just your current job.

There are plenty more choices, and certainly lots of tools within SQL Server. You should know about Extended Events, or Query Store (or Open Query Store) and more. Learn to use the resources you have, practice and test with them, and if you need a paid tool, don’t be afraid of asking. If the tool provides an ROI, it’s worth it. If it doesn’t, tell the vendor why.
April 10, 2018