If you have ever created a .bat file to run BCP commands - then Powershell is probably something to look into. It is a command line utility that gives you options for automating processes.

Here is one example where I use it...

In one of our databases, we store the image file names in a table - and the image warehouse locations in another table. What I need is the full path to the file so I can identify which warehouse the image file actually resides in. Note: the application is built to search all available warehouses for the image file - so we don't have any relationship between the image and the specific warehouse.

Using powershell, I can query the database and return the list of image files I am interested in. Once I have that list, I can then loop over the list and check each warehouse to see if the file exists in that warehouse. Once found - I can output that data for the next step.

Example warehouse would be: \\someserver\someshareExample image file: somefolder\somefile.tif (note: folder is a different column in the same table)

So now I have a script where I can pass in some identifying information (e.g. customer number) - and the script will output the list of files with the full path.

Another example is not related to SQL Server...but I have a set of scripts I use to add printers to my print servers. These scripts allow me to build a list of printers on one server - and then install them on another server. Someone will probably think print migrator...and that is correct, but print migrator fails when trying to migrate printers from x86 to x64 systems.

So...Powershell is a useful tool to know, but not necessarily a tool that is designed to replace SSMS or T-SQL.