A Fine Slice Of SQL Server
What I have done here is hard-code three parameters ( database edition, start IP address and end IP address) which for my situation won’t change but I have given the ability to pass in the environment name, SQL Server name and database name.
So a prompt will be presented to the user – here you should enter the relevant details and click enter.
It’s not that difficult to do, and the scripts themselves are probably faster than fumbling around the UI.
Comments closedSlava Murygin has ten Powershell scripts to help administer a SQL Server instance:
Script #5. Read SQL Server Error Log file.
That is extremely important troubleshooting script. When you start/restart the SQL Server service and it does not come up, you can run this script to see what was going on during the SQL Server startup and what was the problem (just note that value of “$SQLInstancePath” must be pre-set by previous script):
Click through for all of the scripts.
Comments closedRob Sewell shows us how to convert the SQL Agent job duration into a .NET TimeSpan using Powershell:
The first job took 15 hours 41 minutes 53 seconds, the second 1 minute 25 seconds, the third 21 seconds. This makes it quite tricky to calculate the duration in a suitable datatype. In T-SQL people use scripts like the following from MSSQLTips.com
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) as ‘RunDurationMinutes’
I wish that some version of SQL Server would fix this “clever” duration. We’ve had the time datatype since 2008; at least add a new column with run duration as a time value if you’re that concerned with backwards compatibility.
Comments closedThomas Rushton shows how to split delimited text using Powershell:
Not very readable. At least, I can’t read it easily, so I need to split that into something a bit friendlier, like an actual list, one line per item. Fortunately, PowerShell has a split command that should do the job, as per this post ScriptingGuy’s Split Method in PowerShell.
Split() is just a .NET string method; you can use any overloads of that method in Powershell just like you could in C# or F#.
Comments closedRichie Lee reproduces a Powershell script to get the license key used for installing SQL Server:
Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!
Click through for the code.
Comments closedHowever, when run via SQL Agent, it succeeds. GAH!
I tried 50 different variations; modifying the script, various TRY..CATCH blocks found on the internet. Nothing. Every single one of them succeeded.Then I remembered that by default, even though it had an error, by default errors always continue. ($ErrorActionPreference=”Continue”. So I added this line at the top:
Read on for the answer.
Comments closedSlava Murygin shows how to use Powershell to read the Registry:
Do you use third party tools to document state of your SQL Server?
If not, that script is for you!At first, you will know what is your SQL Server is up to.
At second, that might be your baseline document, to which you can compare a current SQL Server state over the time.
At third, that is a priceless piece of documentation!!! (I mean FREE!!!) which you can put in a folder and report to your boss.
Registry settings are a good part of a baseline, particularly a security baseline.
Comments closedWayne Sheffield has a Powershell cmdlet to find sessions matching SQL Saturday topics:
The output to the screen is the Event #, Speaker, Session Title and URL for the presentation.
The generated export file will also include the event name and session abstract. Additionally, the URL will be encased with the Excel HYPERLINK() function. When the export file is opened up with Excel, clicking the URL will open your browser to the session information, where the presentation material can be downloaded.
I hope that you get a lot of use out of this script.
Click through for the script and more details.
Comments closedThomas Rushton wants to customize his Powershell prompt:
By default, your PowerShell prompt is “PS <<path>> >”. So normal; so dull. However, that can be easily changed. In my case, I want to add:
- the current time (well, the time at which the prompt was generated)
- a reminder which account I’m using (I have two – a “normal user” one, and a “DBA/SU” one
- a countdown of seconds remaining until my next holiday
Oh, and I still need to see the current path.
Very interesting. By contrast, here’s how to change a Bash prompt.
Comments closedCody Konior has a beef with Powershell workflows:
That’s inexplicable.
One thing which does make it all work is setting $PSRunInProcessPreference which, “If this variable is specified, all activities in the enclosing scope are run in the workflow process.” Unfortunately that doesn’t explain what’s really going on and what the impacts are, so I won’t use it. But here it is turning the original failing script into a working one.
I’ve never used Powershell workflows. It sounds like potentially an exasperating experience.
Comments closed