SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PowerShell Tool Time: Controlling Our Tools


PowerShell Tool Time: Controlling Our Tools

Author
Message
sake.god
sake.god
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 170
Comments posted to this topic are about the item PowerShell Tool Time: Controlling Our Tools
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872490 Visits: 47483
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gaganlamba059
gaganlamba059
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 73
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
MiguelSQL
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2619 Visits: 1241
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
sake.god
sake.god
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 170
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. BigGrin



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.
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65406 Visits: 886
Thanks for the good article.
Misha_SQL
Misha_SQL
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4593 Visits: 1122
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
corey lawson
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3162 Visits: 661
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
alen teplitsky
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27115 Visits: 4910
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
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72538 Visits: 14916
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search