I think PowerShell is a great addition to the Microsoft stack. Given the previous versions of VBScript, Perl and Python ports, and more on Windows, PowerShell is a great improvement. Even for someone that spent a lot of time in the C Shell and Bourne Shell as a student and young professional, I think PowerShell is an improvement. There are certainly still things that make me crazy about PoSh, such as the -eq, but I'm getting more comfortable with the structures and flow.

In SQL Server, we had the old SQLPS module, which was, well, a start. Then we got the SqlServer module, which is better. However, the best thing I've seen for us data professionals is the dbatools project from Chrissy LaMaire (@cl) and company. To me, this is incredibly useful for anyone that wants to use PoSh with SQL Server, but it's really indespensible for SQL Server migrations from instance to instance. I'm not sure I'd even try anything else at this point.

Not everyone wants to use PoSh with SQL Server, and that's fine. There are certainly plenty of places where T-SQL works very well to manage and interact with SQL Server. I still prefer it for many things, but the more I work outside a database, whether for administrative actions or manipulating parts of the platform away from SSMS, PowerShell has some advantages. Not the least of which are cleaner file operations and certainly better string manipulation (IMHO). 

Recently I saw post from Drew Furgiuele about finding linked servers in your object code with PoSh. It's a long, somewhat convoluted post, and some of you might wonder if it's worth the effort. Hint, it's not worth the effort because Drew has done it for you. He's got the function on Github, and if you need to move linked servers, just use his code to help.

My point is, however, that doing this in T-SQL would be a nightmare. Just going through search isn't helpful, though SQL Search is a great tool. The work to find the objects would be hard, and this isn't something you want to get wrong in a migration. It would be bad enough to have errors for non-existent linked servers, but it might be worse if you had code pointing to the wrong database. This is something you'd want to fix, and PoSh helps work with a complex problem here.

There are other issues like this, other problems or requirements that look across instances or more intensely at parts of our systems outside of what we want to do with T-SQL. Certainly anything at scale, that might be something you do for many instances is better with PoSh, and certainly any infrastructure as code items that work to stand-up and configure SQL Server really should use PoSh.

Don't make PowerShell a hammer, and try to solve all issues, but for the future, you really ought to be sure you are comfortable reading PoSh scripts and know how to use them with SQL Server. Learn when PoSh is a good alternative, and certainly know where you can get scripts or help with your code.