Jeff, your question (if I can boil it down) is "Why should I use Powershell (if I can do it in T-SQL)?" is THE most common question I get around Powershell when I speak about it. It boils down to using the right tool for the job and there are a lot of things that SQL Server/T-SQL aren't very good at. To use your old saw, just because you can do it in T-SQL doesn't mean you should. 😀

This slide is taken from my presentations and helps illustrate the three main reasons I advocate using Powershell:

1. Multi-Server execution is built into the framework.

2. Powershell lives "outside" of the stack, giving you the ability to work against the OS, SQL Server, Active Directory, Exchange, or any other part of the Windows ecosystem equally.

3. Powershell is built on .Net, giving users a robust way to interact with the different components of the ecosystem.

Your example of xp_delete_file is a perfect illustration of item number 2. I've used this extended stored procedure in the past and, frankly, it's not very good. SQL Server is an application for managing and manipulating data, not the file system. My past experiences with xp_delete_file has been fraught with inconsistent execution and results. In fact, it's one of the primary reasons I turned to Powershell, as using that language gives me a more robust experience for finding and managing files in the OS.

This is not to say that Powershell is a replacement for T-SQL. There are many cases (which I'll actually demonstrate through the course of this series) where I will combine T-SQL with Powershell to accomplish tasks. Add with this the fact that working with Powershell and SQL Server commonly involves using the SMO, which executes T-SQL under the covers. It's all about using the right tool for the job, instead approaching every job with the same hammer to drive in a nail or a screw.

To respond to your comment on using LSNs for more accurate restore scripts, I completely agree. I actually have much more sophisticated code located on my GitHub repository for this.