PowerShell Tool Time: Controlling Our Tools
-
Comments posted to this topic are about the item PowerShell Tool Time: Controlling Our Tools
-
I haven't done a deep dive on the article but it looks good. The Get-SQLBackups function is nice, clean, and easy to read. It'll be a big help to anyone that want's to learn more about PowerShell.
That, notwithstanding, I'll never understand why people use PowerShell to do something that SQL Server does pretty well on it's own and without the need of extra functions needing to be available...
DECLARE @Yesterday DATETIME = DATEADD(hh,-24,CURRENT_TIMESTAMP);EXECUTE master.dbo.xp_delete_file 0,N'C:\DBFiles\backups',N'trn',@Yesterday,1;
... which is the functional equivalent of the PowerShell command in the article, which also requires a separate function to be constructed and be available.
Get-SQLBackups -Path C:\DBFiles\backups -Type Log -OlderThanHours 24 | Remove-ItemSeems like turn-about on an old saw my be fair play here. "Just because you can do something in PowerShell, doesn't mean you should." 😉
As a sub-note on this, you can get an occasional LSN failure when trying to do log file restores if you only use time of day in trying to decide which log files to restore to a point in time. It's a rare thing but it does happen. It's the reason why the SQL Server restore GUI and most 3rd party software do it by LSN rather than by completion time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Excellent article.
You are validating for 'Full','Differential' and 'Log' as parameters to the function, but you are testing for '.Full','.Differential' and '.Log' in your if statement, with extra dots in the beginning. Is this a typo?
I didn't try running the code myself yet, but this one stood out when I was reading.
Gaganpreet S Lamba
-
I like the article.
and yes yes... you can do it easier with SQL statements like Jeff says... but what I like about the article is that is uses a very simple problem to teach how to solve it with powershell.
In other words... I don't like the article because it shows me how to delete old back files, but I like it because it's a very simple case of how to build a function, and use mandatory field and check the input, something that I didn't know and that I'm trying to learn.
Good work!
Miguel
-
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.
-
Thanks for the good article.
-
Thank you for the article! For those of us powershell-challenged, can you explain this part in the RESTORE stetment: $($tlog.name) ? I understand the part inside the parenthesis, but why do we need enclose it in $() again? Thank you!
- Misha_SQL - Friday, September 18, 2015 8:59 AM
Powershell's double-quoted strings allow one to have a $variable's value get injected into the string. In most "real" programming languages, you're concatenating string literals with variable values to do the same.
"RESTORE LOG [RestoreDemo] FROM DISK=N'$($tlog.name)' WITH NORECOVERY;"
So... "RESTORE...$($tlog.name)..."
Because $tlog is an object reference, and not a string value, we see the special sauce we have to use to pull this off:
$($tlog.name) is the Powershell idiom to properly inject the current value of the $tlog.name property value into that double-quoted string.
If you were to try this instead:
"RESTORE ... $tlog.name...", Powershell will "stringify" the $tlog object to inject that into things, which is probably not what you want to happen. It shouldn't break anything directly there, but it won't be the droid you're looking for. -
Not too experienced with powershell functions, but how do you run it outside of the ISE? I created the function, but can't run it from a regular powershell window with just the function name
- alen teplitsky - Wednesday, January 3, 2018 11:37 AM
Save the function as a ps1 file and put that file in your Powershell profile, save it in a module and import the module, paste the function in the beginning of whatever powershell script you are running which is similar to using the .source method which is just referencing the ps1 file with the function in a script before it is called - along the lines of
. C:\SomePath\ToPSFile\whatever.ps1
This article has a good explanation and examples of using the different approaches:
How To Make Use Of Functions in PowerShellSue
-
thx
got this working and adding more servers daily
-
Is there anyway to use this with a UNC path? I cut and pasted some of your code and trying to write your function to run on a central server with an input of a text file of a few dozen servers and to dynamically build the path. But can't seem to find anything about Get-ChildItem and using it with a UNC path
-
Sure! If you're using double-quoted strings, be sure to double-up the backslashes:
gci "\\\\server\\folder\\to\\file.txt"
or
get-childitem "\\\\server\\folder\\to\\file.txt"
Post reply
Something like this could help clarify all the different ways one can work with strings and their quirks/tricks in Powershell.
http://www.tomsitpro.com/articles/powershell-string-formatting,2-999.html -
...or here: http://www.rlmueller.net/PowerShellEscape.htm
-
I was playing around and figured out you can do filesystem::\\server\f$\ and it works when I run it for one server. Doesn't work when i'm trying to run it against a file of my servers.
my scrap code
if ($servers -notmatch "")
{
$fs = FileSystem::
$unc = "\\"
$unc2 = "\"
$loc = "f$"
$path = $fs + $unc + $box + $unc2 + $loc
$servers = Get-Content -literalpath "C:\temp\sql_servers3.txt"
$files = foreach ($box in $servers) {Get-ChildItem $Path -Filter "*$extension" -Recurse}
return $files
when I run this in a powershell window and assign the variables manually including the server name, it works. when I try reading the server names in, it doesn't.
Changed it a little to see what's being passed and getting positional parameter error and will fix it tomorrow
if ($servers -notmatch "")
{
$fs = FileSystem::
$unc = "\\"
$unc2 = "\"
$loc = "f$"
$path = $fs + $unc + $box + $unc2 + $loc
$servers = Get-Content -literalpath "C:\temp\sql_servers3.txt"
$files = foreach ($box in $servers) {Out-Host $path}
#{Get-ChildItem $Path -Filter "*$extension" -Recurse}
return $path
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply