A Fine Slice Of SQL Server
Michael Bourgon has a few Powershell snippets he wrote this week:
I needed to parse out a Unix filename from a file. The original “ls” looked like this:
-rw-rw-r– 1 psabcderfg data 646621 Jul 19 16:25 myfile16071901.Z
The unix command to parse it would
cat $fl |awk ‘{print $9}’And the powershell equivalent is:
$newfiles = get-content $fl | foreach {$_.split(” “)[8]}
(the number is 8 instead of 9 because powershell arrays start at 0)
Check all of them out.
Comments closedPhil Factor shows how to use Powershell’s push and pop methods to create an expression evaluator:
I use stacks for writing expression analysers. Generally I like at least two stacks, probably up to four. They tend to be different sizes and may have other differences. If written as objects, the code becomes much cleaner and easier to read. Why do I write expression analysers? You might imagine that you would never need such a thing, but once you have one, reasons for using it just keep appearing. They are handy for parsing document-based hierarchical data structures, for parsing grammars and for creating Domain-Specific Languages (DSLs). A DSL is handy when you’re writing a complex application because it cuts down dependencies, and allows you to develop scripts for complex workflows without recompiling .
What I describe here is a cut-down version of what I use, just to illustrate the way of creating and using stacks. I extend this basic algorithm, originally from Dijstra’s shunting algorithm, into a complete language interpreter. All it needs is a third stack to implement block iterations and ‘while’ loops. Why not use PowerShell itself as your DSL? I’ve tried that, but my experience is that it is pretty-well impossible to eliminate script-injection attacks, and I also find that there isn’t quite enough control.
For a more prosaic usage of the stack in Powershell (as well as bash), you can push your current location, move to a new directory, perform some action in that new directory, and pop your old location off the stack to go back to where you were before. This is particularly useful for those Powershell modules and cmdlets which leave you in a different directory from where you started.
Comments closedPaul Turley discusses work within the community to get Reporting Services cmdlets:
We (along with Aaron Nelson, Data Platform MVP & Chrissy LeMaire, PowerShell MVP) are working with the SQL Server product teams to recommend the first set of CmdLets that we would like to see added to the PowerShell libraries. Please help us by posting comments with your suggestions. What are the most important SSRS-related tasks that you would like to automate using PS? Give us your top five or so.
I’m glad to see Reporting Services get some Powershell love.
Comments closedDerik Hammer notes that there is a bug in 2016 with SQL Agent jobs which have Powershell step types:
When executed through the SQL Agent, the SQLPS.exe mini-shell is called and the current working directory is switched to the SQLSERVER:\ provider. When you call a cmdlet that uses the FILESYSTEM provider under the context of the SQLSERVER provider the cmdlet will fail.
Derik has a Connect ticket open for this bug as well.
Comments closedSander Stad wants to use Powershell workflows to back up databases in parallel:
It’s possible to use a workflow to execute your backups. You have to take into consideration that there is a downside. If you execute all the backups at once you’ll probably get issues with throughput if you’re dependent on a slow network for example.
You could always add another parameter to filter out specific databases to make sure you execute it as a specific set.
Sander does include his script, so check that out.
Comments closedRichie Lee shows how to hide assembly load information in a Powershell script:
Recently I’ve been wondering how I can suppress the output in my PowerShell scripts when loading assemblies into them. I used to find them useful; but now I find them annoying and they are no substitute for error handling ( I used to find them handy as a way of telling me that the script had got this far in the script).
There is more than one way to suppress output to the console, but for assembly loading, I prefer to use [void] because it looks neater than the alternatives:
I’d never used this technique; I always piped to $null.
Comments closedRob Sewell describes his experience creating a new cmdlet:
The journey to Remove-SQLDatabaseSafely started with William Durkin b | t who presented to the SQL South West User Group (You can get his slides here)
Following that session I wrote a Powershell Script to gather information about the last used date for databases which I blogged about here and then a T-SQL script to take a final backup and create a SQL Agent Job to restore from that back up which I blogged about here The team have used this solution (updated to load the DBA Database and a report instead of using Excel) ever since and it proved invaluable when a read-only database was dropped and could quickly and easily be restored with no fuss.
This is a combination of describing what the cmdlet does as well as the circumstances behind its creation. It’s a good read.
Comments closedMax Trinidad continues his series on top-down ETL using Powershell:
After all the previous functions has been loaded, just type the following one-liner:
Process-PSObjectToSQL -SQLDataObj $SQLData;
This sample script code can serve as a startup Template to load data into SQL Server.
This sample SQL data load will fail. Here’s when the Try/Catch/Finally will work for you in trapping what went wrong. Adding the necessary code to provide that additional information to troubleshoot and fix the problem.
Parts one and two available as well.
Comments closedIt’s a classic one-liner, but if you’re not used to reading it I’ll break it down for you. First, we useGet-ChildItem to return a list of registered servers in our central management server (named PRO-CM-SQL in my example). This returns a series of objects that lists all the registered names on the central management server in each directory, so we need to filter out the directory names with a Where-Object (objects that don’t have a “mode” value of “d” for directory). Once we have our list, we just select the names ( Select-Object ). Then we pipe the list of names over to a ForEach-Object and execute the script each time. Finally, we tack on a Export-CSV cmdlet to output the results to an easy to read file we can open in Excel (or notepad, or whatever).
Our script also doesn’t control output, so you leave that up to the user. They can put it on the screen or pipe it to a file. And that’s an important style point: never put your users on rails. You may like output one way, but someone else may not. Just think of the next person.Because some day you might be that next person.
This is a good post if you need to figure out how to find your servers’ current power settings, but a great post if you want to think about how to write helpful Powershell scripts.
Comments closedShane O’Neill learned a bit of Powershell in the process of documenting replication:
Now, due to an unfortunate incident when I was a Software Support Engineer that involved a 3 week old backup and a production database, I prefer to not to use the GUI if I can help it.
I’m not joking about that as well, if there is ANY way that I can accomplish something with scripts instead of the GUI, I will take it!
Especially when the need was to document the properties of over 100 articles, I was particularly not looking forward to opening the article properties window for each of the articles and copying them out individually.
Check it out.
Comments closed