I'm not a DBA, but this was a really good introduction to PowerShell in general. I scribbled down just about everything the speaker offered. I'll slowly go through it now. As I type this up, I'm testing everything. I'm often guilty of phoning in blog postings, but I'm not doing that here. Things worth saying are:
- First of all, please run PowerShell as an administrator. That will save you a lot of heartache as you attempt what follows.
- A lot of old school DOS commands are honored in PowerShell. For example, when I open PowerShell the prompt says PS C:\WINDOWS\system32> and when I type cd.. it changes to PS C:\WINDOWS>
- All of the supported DOS conventions are aliases for real PowerShell commands. You may see what an alias is by, typing, in the case of cd for example, get-alias cd which will write the name of the real command to the console. Set-Location is the name of the real command in this case and when I type set-location .. it changes the command prompt to PS C:\> which I could have also got to with set-location C:\ ...please note that there does not seem to be case sensitivity in using the PowerShell commands as the command is Set-Location and I'm getting away with set-location and also set-location WINDOWS takes me back into the WINDOWS directory and sets the command prompt to PS C:\WINDOWS>
- Other DOS commands include move and just now I was, from the C root, able to move Tom.txt from C:\Tom.txt to C:\foo\Tom.txt with move Tom.txt foo ...the command cls will clear out the history of noise in the shell and something like echo "Hello" will write Hello to the console... move corresponds to Move-Item while cls corresponds to Clear-Host and echo to Write-Output so I may move Tom.txt back to where it was by navigating into the foo folder and typing move-item Tom.txt C:/ ...note that the commands, properly called cmdlets, typically take a verb-noun shape. write-host hi there also puts copy to the console, in this case: hi there
- get-childitem and dir do the same thing (show a list of what is in the directory at hand) but there is no support for dir /W which would have, in DOS, broken up the list into a few columns running side by side (I think). I was able to emulate the /W trick by using get-childitem | format-wide which made two columns of things in the C root when I ran the command after having navigated into the C root. I typed get-help format-wide to learn more about format-wide and saw [-Column <Int32>] in a SYNTAX subsection which led me to type get-childitem | format-wide –column 3 to have three columns instead of just two. This begs the question, what do the pipe symbol and the hyphen do? The hyphen denotes a parameter switch used as a setting for the command before it, so in this example –column is a setting for format-wide. I had at first thought that format-wide's functionality might be a setting for get-childitem, but this is not so. In order to emulate the dir /W behavior one must hand the get-childitem command to the format-wide command. The pipe symbol provides for this. When you "pipe" one thing to another, you hand that which is on the left of the pipe to that which is on the right of the pipe as an inbound value.
- "hi there" | out-file "C:\temp\THUNDERDOME.txt" was and example of creating a text file with "hi there" inside of it at C:\temp\THUNDERDOME.txt. I took the photo I provide here at SQLSaturday and in it Jennifer points out how complicated it is to do the same thing in C#. out-file -inputobject "thunderdome" -filepath "C:\temp\Whatever.txt" is a comparable act which will put thunderdome as a string into a new file at C:\temp\Whatever.txt
- get-psdrive will give you a list of all hierarchies that PowerShell may navigate about. The C drive is a FileSystem for example and HKLM and HKCU are registries. I think Jennifer said that HKLM is Active Directory. One may filter the list to see just the registries with get-psdrive –psprovider registry
- If the command prompt is PS C:\> then typing set-location HKLM:\ would take one into the Active Directory stuff and out of the file tree at C setting the command prompt to PS HKLM:\> ...I had to Google for how to do this and I found the answer here.
- After going into active directory, I typed get-childitem and saw a partial list of what there was to see along with an error in the midst of the list telling me... get-childitem : Requested registry access is not allowed. ...Next, set-location SOFTWARE took me into SOFTWARE and from here get-childitem returns a list of installed programs without the error I saw before. I'm not sure how to manipulate the user accounts yet and that wasn't really a big focus in Jennifer's talk. This has some ideas but it seems to be doing it from the C root.
- Clicking the up arrow at the command prompt will "retype" old commands for you, one at a time, circling backwards in history progressively. F8 seems to do the same thing.
- sqlps is a tool that Jennifer described as flavor of PowerShell with SQL installed. import-module sqlps –disablenamechecking was how Jennifer recommended installing it but I got an error reading: import-module : File C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\sqlps\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170. ...when I tried. I got around this by running, from the C root, powershell.exe –executionpolicy bypass and then turning back around and running import-module sqlps –disablenamechecking again, but this, which touches on execution policies, illuminates that this policy change comes with a little bit of living dangerously. The prompt I now see is PS SQLSERVER:\>
- get-help *SQL* is going to get a list of all commands which have sql in the name. The asterisks are wildcards.
- A list of what is at the root of SQLSERVER looked like this for me...
- SQL
- SQLPolicy
- SQLRegistration
- DataCollection
- XEvent
- Utility
- DAC
- SSIS
- SQLAS
- Audits
- AvailabilityGroups
- BackupDevices
- Credentials
- CryptographicProviders
- Databases
- Endpoints
- JobServer
- Languages
- LinkedServers
- Logins
- ResourceGovernor
- Roles
- ServerAuditSpecifications
- SystemDataTypes
- SystemMessages
- Triggers
- UserDefinedMessages
- ApplicationRoles
- Assemblies
- AsymmetricKeys
- Certificates
- DatabaseAuditSpecifications
- Defaults
- ExtendedProperties
- ExtendedStoredProcedures
- Federations
- FileGroups
- FullTextCatalogs
- FullTextStopLists
- LogFiles
- PartitionFunctions
- PartitionSchemes
- PlanGuides
- Roles
- Rules
- Schemas
- SearchPropertyLists
- Sequences
- ServiceBroker
- StoredProcedures
- SymmetricKeys
- Synonyms
- Tables
- Triggers
- UserDefinedAggregates
- UserDefinedDataTypes
- UserDefinedFunctions
- UserDefinedTableTypes
- UserDefinedTypes
- Users
- Views
- XmlSchemaCollections
- $txt = "hello" followed by write-host $txt will put hello to the console and at this point just $txt by itself will also put hello to the console. $txt is an example of a variable. The dollar sign denotes a variable. $num = 1 followed by write-host $num will put 1 to the console and if we follow this up with $num = $num + 1 and then write-host $num this will put 2 to the console. Combining variables, $txt = $txt + $num followed by write-host $txt will put hello2 to the console.
- $txt.Equals('hello') puts False to the console at this point while $txt.Equals('hello2') will put out True and $txt.Length will give us: 6
- Equals is an example of a Method for a string variable while Length is an example of a Property. To see all of the methods or properties for a type do something like so: $txt | gm or $txt | get-member (gm is an alias for get-member)
- get-service will give a list of all of the services running on your box, and get-service | format-wide might be the better thing to do so that none of the names get truncated, AdobeFlashPlayerUpdateSvc, for example, was just represented as AdobeFlashPlaye... in the list made by the first, simpler syntax. get-service *sql* will give a list of the services which have SQL in their names and get-service *sql* | select-object Name, Status, DisplayName, ServicesDependedOn | format-table –autosize will give us some of the stats on those services, and more so than just select-service *sql* which will not tell us about the ServicesDependedOn, but let's focus on AdobeFlashPlayerUpdateSvc as that seems like a really harmless service to mess with. get-service AdobeFlashPlayerUpdateSvc | select-object Status tells me that the service is stopped. (get-service AdobeFlashPlayerUpdateSvc).Start() should turn it on, but when I run get-service AdobeFlashPlayerUpdateSvc | select-object Status right after it tells me that it's still stopped. SQLWriter is running and I can successfully stop it and start with (get-service SQLWriter).Stop() and (get-service SQLWriter).Start() so why can't I start AdobeFlashPlayerUpdateSvc? get-service AdobeFlashPlayerUpdateSvc | select-object ServicesDependedOn just returns {} which I'm guessing is an empty list, so there isn't a dependency that's just off. Alright, when I just go into my services and try to start it outside of PowerShell a dialog box tells me: The Adobe Flash Player Update Service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.
- get-service AdobeFlashPlayerUpdateSvc | select-object * is going to give us a list of all of the obvious stats for AdobeFlashPlayerUpdateSvc but the Startup Type is not one of them and to get that the command is get-wmiobject –class Win32_Service –property StartMode –filter "Name='AdobeFlashPlayerUpdateSvc'" ...alter the state with set-service AdobeFlashPlayerUpdateSvc –startupType disabled and set-service AdobeFlashPlayerUpdateSvc –startupType automatic and set-service AdobeFlashPlayerUpdateSvc –startupType manual
- Alright, let's write a script! First, we must allow permissions for that with powershell.exe –executionpolicy unrestricted (which I ran from the root of C) and then we should create a script. I created a text file at C:\foo\whatever.txt, renamed it to C:\foo\whatever.ps1, and put this into it...
$state = ""
$state = $state + (get-service SQLWriter | select-object Status)
IF($state -eq "@{Status=Running}")
{
echo "stopping..."
(get-service SQLWriter).Stop()
}
ELSE
{
echo "starting..."
(get-service SQLWriter).Start()
}
get-service SQLWriter | select-object Status
...which I may use to toggle the SQLWriter service on and off by navigating into the foo folder and typing ./whatever.txt - get-childitem "C:\somefolder\" *.html is going to give you a list of just the .html files in the somefolder folder.
- Let's rewrite whatever.ps1 to loop through a collection and write stuff from it to the console:
$state = "whatever yo"
1, 2, "Heya", $state | foreach-object {
write-host $_ -ForegroundColor YELLOW;
write-host $_ -ForegroundColor RED;
} - I tried to figure out how to open a series of files in notepad combining the stuff in the last two bullet points like so...
get-childitem "C:\somefolder\" *.html | % {
notepad $_FullName
}
...but when I do I just get an empty file for each thing I attempt to open. Whatever. Note that % is an alias for foreach-object. - If I go back to sqlps and navigate back to the tables I can run a query with invoke-sqlcmd "SELECT * FROM Whatever" and this may be done from outside of the database like so invoke-sqlcmd "SELECT * FROM Whatever" -database MyDatabase
- write-output "That's all!"
- clear-host
Addendum 4/11/2016: I realized today that I have whatever.txt above in a spot where I should have whatever.ps1. Also I learned of start-process explorer ftp://user:pass@example.com -Windowstyle maximized and also of start-sleep -m 3000 today!
No comments:
Post a Comment