The topic of this months T-SQL Tuesday #94 (hosted by Rob Sewell b/t) is all about PowerShell.

The question: What are you going to automate today?

There’s a lot of different ways to approach this task. Let’s assume you are not The Scripting Guy level of good with PowerShell. You want to learn it but you haven’t yet devoted the time to start from Hello World! and up through overflowing arrays, writing infinite loops, etc. Time passes and the challenge grows into something more formidable that it actually is. So you never do it.

The intimidation can seem overwhelming but I assure you that you can dip your toe in the water without getting bitten by a shark.

You don’t need that level of skill to start learning PowerShell and do useful things. Sometimes you need to see some working code to inspire confidence and remind you what you are trying to learn.

I have a great way to get into PowerShell and start automating tasks – dbatools! Let’s give it a try from scratch and see how easy it is to backup, restore, and run a consistency check on your databases.

PowerShell ♥ SQL Server

dbatools is free (as in speech and beer) open source software created by the SQL Server community that uses PowerShell to automate DBA work.

There’s over 200 different commands each with options to help you perform a multitude of DBA tasks.

1. Migrations Commands
2. Best Practices Commands
3. General Adminstration Commands
4. Utilities

It is very simple to get started – first you need to download the PowerShell module. Follow the method for your situation and get it installed.

If you want to setup a fresh environment then check out my SQL Server Starter Pack.

Use PowerShell via dbatools to Backup Databases

All the documented commands are well done and easy to understand. I started with backing up the databases on my instance:

Easy! Look at the file system to verify they are there.

If you want to get help besides the web site commands you can use the Get-Help cmdlet to show the usage.

Use PowerShell via dbatools to Test Backups

I’ve seen a lot of backups that were no good when the time came to restore them. Corruption is a terrible thing to learn about when trying to restore a database someone dropped. It isn’t enough to take backups – you need to test them. You can do this easily by running the Test-DbaLastBackup command.

This cool command restores your backups and performs dbcc checktable on them. Now you can have confidence that your backups will successfully restore and your data is not corrupt.

Evolve into PowerShell Man

This is just a start to get your mouth wet. There’s many resources you can consume to learn the inner workings and start developing your own PowerShell code. Using dbatools is a great start towards getting familiar at the command line and getting stuff done. Give it a try today!

I love seeing your comments!