This is a good question for me to ask. I’ve been learning it, and working through examples in my PowerShell Challenge, but it wasn’t until months later that I found a real reason to try PowerShell.
When I realized I had lost 12 databases, I could have spent around 10 minutes attaching them all manually. That felt like a pain, and a waste of time. I could have probably scripted something in T-SQL, specific to this situation, in about 15-20 minutes tops.
However scripting something general, like an attach T-SQL script that figures out which databases to attach based on a path, would have taken me an hour, perhaps more. It would have been a bit of a chore to test and debug, and I’d worry it wasn’t rock solid because I’d be trying to work inside, and outside, of T-SQL.
Powershell felt like a better fit, and this seemed like something built for PowerShell and SMO. I spent around 3 hours one night searching around and writing the script. This included some false starts, and some experimenting with things. I spent at least 30 minutes trying to restore the mdf before I realized that I should be attaching it. Then it took me a bit to work through some examples I’d seen about attaching that didn’t make sense. Part of it was my unfamiliarity with PoSh.
However at the end, 3 hours+ in, I had a script that attached databases quickly. I tried it on a few VMs and it worked great. I dropped it on GitHub, so it’s always available to me on multiple machines, and I can clean it up over time.
I can also share it. When I googled around, I didn’t find a good, generic script like this. I also had to compile information from multiple places. Writing this series of posts allows me to potentially help others, but also it allows me to showcase a bit of knowledge and double check myself. As I broke the main script down, I also rewrote pieces, cleaned it up, practiced a bit of programming, and learned more about PoSh. I bet if I had to re-write this script, it would take me an hour to 90 minutes, tops.
Was it worth it? If I never run this again, that’s debatable. However if I ever need it again, I think it’s easily worth it. Especially with the knowledge gained.
Voice of the DBA 
That’s how a lot of love affairs with new programming environments start. PowerShell has been invaluable to me in the areas of running Policy Based Management policies against all instances in my environment regardless of version, automating database “refreshes” (full only as well as point-in-time), automating Log Shipping, checking disk space, and most recently a colleague pulled off an automated AlwaysOn configuration script…PowerShell and SMO have it all covered. Thanks for sharing your experience. I hope it serves you well going forward.
LikeLike
Your welcome, and when are you going to write me a piece on how you use PoSh?
LikeLike
Do I think PoSH is worth the time? Absotively. I is so powerful and easy to do tedious jobs quite easily. I am in the process of building a training series that goes over some tough things I have learned over time. While there are some nuances that you have to get over, the flexibility alone with the ease of performing rudimentary tasks very simply (read contents of a file and iterate over each line) are invaluable and make this a true work horse. I give it 2 thumbs up.
LikeLike
Good question Steve. I’ve been wanting to pick up PowerShell too. Another DBA mentioned taking a class. I’m just going to start with a book and tidbits from DBAs like you blazing the trail.
Here’s another question: Which would be a better tool? SQLCLR or PoSH for the DBA?? I’ve put off both but it seems like now is a good time to pick one of them up.
Rick
LikeLike
I say PoSh because the overhead of sometimes getting a CLR assembly working with permissions and setup can be a pain. I also worry about embedding something inside an instance that isn’t necessarily related to the functioning of that instance. For me, it would depend, but I’d lean PoSh.
LikeLike
I agree with you that PoSh is useful for many DBA tasks. However, you would not believe some of the spagetti code monstrosities I have stumbled over while doing production support. When I am scripting something in PoSh, I almost always wish that I were writing a nice, encapsulated c# object instead. Just because you can puzzle out a way to lash together a clanking mess in PoSh doesn’t mean that it is always the correct solution.
LikeLike
I’d agree, though I think some of this is learning how to write code better. I’ve refactored my script a few times, and likely would do a bit more if I were using it regularly. I’d try to clean it up so it’s more maintainable.
LikeLike
I am new to PowerShell and when I saw the title I was hoping for more of a is PoSh worth learning and using. Finding a one time use for it after several months I am guessing in your environment it would be questionable. Though the comments have shed more light on more situations in which PoSh is useful.
LikeLike