Category Archives: Badass Tools

If you work on a computer, get these tools

So much of IT comes down to tools. I love getting a recommendation from a colleague that improves my workflows. Here are a few of my favorites that can benefit anyone, no matter what your specialty. I do database work, so my examples come from that area. I use these on Windows, but some tools are available for other platforms.

Keepass: Password storage. Put this auto-type behavior on the folder that holds all your sql logins and press control-v after you’ve just come from a sql query window. So great!

CREATE LOGIN [{UserName}] WITH PASSWORD=N'{Password}', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

For my personal info I use 1password. People without password managers drive me crazy. Watching them work the internet makes my head hurt.

Sublime Text: This text editor can do multiline editing better than SSMS does it. Say you have a list of tables you need to drop. Copy that list into Sublime Text, do a little key command to get your cursor on every line and then start typing the drop statement. Sure you could do this with some powershell, but some reason, this is just really fun. This one costs money.

Ditto Clipboard Manager: Back at the beginning of my career, I had a co-worker who wouldn’t stop raving about his clipboard manager. I ignored him and I was WRONG to do that. Eliminates so much extra tabbing and thinking. If you copied it – it’s in a little pop-up menu you can activate with a key command. I use ctrl-~.

Path Copy Copy allows you to copy UNC paths from your mapped drives. Instead of getting L:\folder, you’ll get the shareable UNC path like \\server\sharename\folder.

Running a plethora of T-SQL

Have you ever needed to run a bunch of scripts, over and over and over? And the SSMS tabs – it can be a mess. This is mind-numbing work – call in the computers. Check out this script I forked from GitHub and modified: https://github.com/propellor/Powershell-pasen/blob/master/Run-Sql.ps1

You point the script it at a folder of scripts and it will fire them off using invoke-sqlcmd. You can use it in a sort-of debugging mode, to get those scripts all working when you get a mess from a co-worker or vendor and have it pick up rerunning from the failed script. Alternatively, if someone else is responsible for the fixin’ and you are just the runner, you can have all the failed scripts rename themselves to .failure.

Also, my first GitHub fork.

UPDATE: Since doing this, I’ve started using RoundhousE, which does the same thing, but waaaay better. Using TFS and octopus deploy, we package up roundhouse with some sql scripts and let developers deploy database scripts to staging and then production.

Testing Job Candidates with VirtualBox

There’s this new technology called virtualization – being a database admin, you might not get the chance to play with it very often.  It lets you run computers inside other computers.  You can run smaller database servers as virtual machines, but serious databases seem to still go the hardware route.

I’ve been using a copy of Oracle’s VirtualBox on Windows 7 ‘host’ running a Windows Server 2008 ‘guest’ operating system.  I’m getting a little test together for a new DBA.  They’ll need to perform a few simple tasks to make it past the first day.  This is something that some headhunters can do for you, but I don’t know if you can really trust those results.

So we install the VirtualBox, export a OS I prepared on my workstation and import it on my testing machine.  You use the import command in the VirtualBox GUI to set it up – it’s all pretty easy.  At this point, you should have your SQL Server all setup – or whatever you are in the mood for.  You’ll make sure to take a snapshot and name it “Test Begin”.  Then you are ready to begin testing these fools!  VBoxManage is in your virtualbox install directory.  The following commands are run from the command line.

–Start TestingRounds–
Vboxmanage snapshot”SQL Test” restore “Test Begin”
VBoxManage startvm “SQL Test”
— Candidate Takes Test and ShutDown
vboxmanage snapshot “SQL Test” take”Candidate 5″
–Repeat–
So you restore the snapshot, start the virtual machine up, let them take the test and then make a snapshot with their name or number.  Restore back to the “Test Begin” state, start it up and you’re ready for another round.
You may have more latitude in your place of work – you could watch them or record the actions for later review.  
I’m planning on locking the internet access down and allowing books online.  This won’t start up the VirtualBox GUI, so they won’t be too tempted to get in there unless they have a certain amount of initiative which you probably don’t want.  You can review logs in the /users/userxyz/VirtualBox VMs/Logs to see if anyone got up to any restores you didn’t intend to be done – such as a user checking another testee’s answers.  You should also see a time of how long the current altered snapshot has been running.  Compare that to the test start time to see if they did any unauthorized shutdowns.  I didn’t get much feedback on the VBox forums on how to lock this down, so you need to have a certain amount of trust or diligence.  Create some answers that won’t be identical and then check for plagiarism.  Many people cheat in university, I wouldn’t put it past them in the workplace.

SQL Powershell

I’m getting a little tired of always clicking around in SSMS. Here goes a shot at powershell. I’ve done a few tutorials on powershell, but don’t use it enough to remember. The only thing I remember is Get-Child-Items and it’s alias GCI. That’s a start. Follow along with me.

User asks if a database was backed up last night. Of course it was, but let’s just make sure.

Right click on the databases node of the server I’m interested about in ssms and ‘Start PowerShell’

I end up at the databases path and try to copy the text out of the window with a mouse swipe to paste it in here. No dice, so I pop into properties and enable quick edit mode – I prefer it on in the CMD window. A highlight and a right-click will copy text out of the window.

PS SQLSERVER:\SQL\SERVER1\DEV08\Databases>

You can see the Server, instance name and then the databases node.

I enter my one command: GCI

A long list of databases fill the screen, but not the backup information I’m looking for. I know there is a powershell cmdlet (command-let a powershell command object) which will tell me more about the children of the database node – the databases. Fortunately, everyone on the web likes to talk about it. It’s the Get-Member cmdlet, which will tell you the methods and properties of whatever object you feed or ‘pipe’ it. Let’s pipe those database objects to the Get-Member alias.

GCI | GM

Whoah. That’s alot of output. I could use a little help sorting through that. Maybe I can pipe that list to another cmdlet and search for a string. There is probably a cmdlet called Find.

GCI | GM | FIND ‘backup’
FIND: Parameter format not correct

So how do I know what a cmdlet is expecting?

find /?

That gives me some help info, but changing the single quotes to double quotes doesn’t seem to do the trick.

Get-Help find

There’s a listing of somewhat unrelated commands. What did I just do there? What happens if I don’t give it a parameter.

Get-Help

TOPIC
Get-Help

SHORT DESCRIPTION
Displays help about Windows PowerShell cmdlets and concepts.

LONG DESCRIPTION

SYNTAX
get-help { | }
help { | }
-?

I expected to see a help page a bit more like this when I typed in Get-Help find, but instead I saw a listing. Find is being interpreted as a TopicName. So let’s type Get-Help find again – maybe there’s a more appropriate cmdlet I can use to find my search string in output of Get-Member. Select-String stands out to me – Finds text in strings and files.

GCI| GM | Select-String “Backup”

System.Void DropBackupHistory()
System.Data.DataTable EnumBackupSetFiles(int backupSetID), System.Data.DataTable EnumBackupSetFiles()
System.Data.DataTable EnumBackupSets()
System.Boolean IsDbBackupOperator {get;}
System.DateTime LastBackupDate {get;}
System.DateTime LastDifferentialBackupDate {get;}
System.DateTime LastLogBackupDate {get;}

There it is – LastBackupDate – and even more.

So I’ll accomplish my task at hand.

GCI | SELECT NAME, LASTBACKUPDATE, LASTLOGBACKUPDATE

But I’m only interested in a couple databases which start with “Risk”.

GCI | where { $_.name -like “Risk*”} | SELECT NAME, LASTBACKUPDATE, LASTLOGBACKUPDATE

The $_ represents the object being sent from the previous cmdlet. So you can filter on any property you found in the get-members listing.

Mission accomplished! That gives me what I need and I can feel smug about not clicking around in SSMS. True, it took me five minutes to learn how to do this and would take me a little longer than clicking on the two databases which were returned, but learning this will let me complete other more complex tasks.

Oh yeah, one other thing. Out-Host -Page will let you page through the sometimes lengthy manual pages. OH -p also works.