T-SQL Tuesday (#tsql2sday), I’ve been absent for a few, figured it was time to jump back in!
SQL and PowerShell, what a great combination. First, let me thank the sponsor, you can find his site from the following link.
One of the first scripting languages I mastered for automation was VBScript. I know. Stop laughing.
However, that background gave me the ability to learn PowerShell and use it in ways to automate the more mundane aspects of SQL Server administration. Two examples of how PowerShell has proved to be an awesome “force multiplier” follow.
Using PowerShell to Automate a SQL Server database restore
The challenge was taking a backup from one SQL Server, written to a Server UNC, and restoring it to another SQL Server on a daily basis. This process is documented here. There were two challenging parts of this process; how to identify the latest backup file, since it was written out with a unique name. The other to restore it to a different location on the target server. Review the code to locate these solutions.
The bonus was not only automation, but a huge savings in time to complete the restore! Using the UI to restore the database ~650GB took over 2.5 hours, using PowerShell and the ADO methods, less than 1 hour!
Using PowerShell to Audit SQL Server Instances
Tasked with investigating many different SQL Server instances for configuration, possible performance issues and documenting settings; I investigated using PowerShell to gather this information via queries. Then, in turn, place these results in an Excel workbook. In this manner, it provides guidance in evaluating and making recommendations on improving settings such as configuration. Plus, you’ll have a baseline of current instance and database settings. This process is documented here.
This PowerShell script is extensible, I’ve re-written it once to take advantage of a re-usable connection object for enhanced performance. It likely needs to be enhanced again in the near future.
If you’ve been thinking of automating your SQL Server instance or database, check out PowerShell. The time invested in learning and creating solutions can not only save a LOT of time, it will free up your time to work on other things. Do share your creative solutions with the community.