PowerShell Tool Time: Controlling Our Tools

  • Mike Fal

    SSC Veteran

    Points: 265

    Comments posted to this topic are about the item PowerShell Tool Time: Controlling Our Tools

  • Jeff Moden

    SSC Guru

    Points: 994677

    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-Item

    Seems 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • gaganlamba059

    SSC Journeyman

    Points: 95

    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.

  • MiguelSQL

    Hall of Fame

    Points: 3079

    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

  • Mike Fal

    SSC Veteran

    Points: 265

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the good article.

  • Misha_SQL

    SSCertifiable

    Points: 5385

    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!

  • corey lawson

    Hall of Fame

    Points: 3705

    Misha_SQL - Friday, September 18, 2015 8:59 AM

    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!

    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.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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

  • Sue_H

    SSC Guru

    Points: 90260

    alen teplitsky - Wednesday, January 3, 2018 11:37 AM

    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

    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 PowerShell

    Sue

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    thx

    got this working and adding more servers daily

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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

  • corey lawson

    Hall of Fame

    Points: 3705

    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

  • corey lawson

    Hall of Fame

    Points: 3705

    ...or here: http://www.rlmueller.net/PowerShellEscape.htm

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply