Tag Archives | PowerShell

My PowerShell command to backup SQL server

Below is my PowerShell code to backup SQL servers. This will create a folder in the destination with the ServerName, then a subfolder with the date, and then a subfolder with the hour. You can backup a single database or all of them. You must have the PowerShell SQL snap ins installed:

Add-PSSnapin -name SqlServerProviderSnapin110 -ErrorAction SilentlyContinue
Add-PSSnapin -name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

Maybe this code will help someone:

Function JBMURPHY-SQL-BackupDB() {

if ($All){
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases")
elseif($DatabaseName -eq $NULL){
write-host "You must use the -DatabaseName parameter"
$DatabaseName=$(Invoke-Sqlcmd -server $ServerName "SELECT name FROM sys.databases WHERE name = '$DatabaseName'")

foreach ($db in $DatabaseName){
    $folderDate=$(get-date -uformat "%Y-%m-%d")
    $folderHour=$(get-date -uformat "%H")
    new-item "$Destination\$ServerName\$folderDate\$folderHour" -type directory -force
    $sqlcmd="BACKUP DATABASE [$dbName] TO DISK = N'$Destination\$ServerName\$folderDate\$folderHour\$dbName.bak' WITH NOFORMAT, NOINIT,  NAME = N'$($dbName) FullBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
    Write-Host $sqlcmd
    invoke-sqlcmd -query "$sqlcmd" -Server $ServerName -QueryTimeout 1200

PowerShell code to update a CRM 2011 field (using REST/oData)

In this earlier post I showed how to loop through all the contacts in CRM 2011. Next thing I wanted to do was to update a field on each Account. So I needed to figure out how to update data, not just read it. Here is the code to do that:

	$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$webclient.Headers.Add("X-HTTP-Method", "MERGE")

PowerShell, JSON, oData and CRM 2011 (or SharePoint 2010)

I am working on how to consume data from/to SharePoint 2010 and from/to CRM 2011. I decided to try and see if I can get the data to display in PowerShell, figuring if I can get it there, I should be able to get it anywhere?  Here is the code to loop through all the Contacts in a CRM 2011 deployment.

Took me a while to figure this out. Should work with any oData source?

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
while ($url){
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
	foreach ($result in $data.d.results){
		write-host "$($result.FullName) , $($result.EMailAddress1)"
	Write-Host "Press any key to continue ..."
	$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
	if ($data.d.__next){
	else {

To loop through the items of a SharePoint 2010 list, you would change $url to:


Not sure if this would be valuable to anyone, but here it is!

How to tell if your PowerShell session is remote

I wanted to write a conditional to prevent certain things from runing if in a remote PSSession.

If you are in a standard PowerShell session the following is returned:

[Environment]::GetCommandLineArgs()[0] = C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

If you are in a remote PSSession:

[Environment]::GetCommandLineArgs()[0] = C:\Windows\system32\wsmprovhost.exe 

How to tell if your PowerShell function was called by name or by alias

I been wanting to write functions that act differently depending on how they were called. I am not sure if this is good practice or not, but I like the idea. Turns out all you need is “$MyInvocation.InvocationName” Look at they following code:

Function Test-Calling {
if ($MyInvocation.InvocationName -eq "cows"){Write-host "This function was called by an alias ($($MyInvocation.InvocationName))"}
if ($MyInvocation.InvocationName -eq $MyInvocation.MyCommand){Write-host "This function was called by name ($($MyInvocation.MyCommand))"}
set-alias cows Test-Calling

If you call the function by alias you get : This function was called by an alias (cows)
If you call the function by it’s name you get: This function was called by name (Test-Calling)

I hope that helps someone.


PowerShell script to force download and install WindowsUpdates

I have been using the UpdateHF.vbs vbscript for years to patch all my servers. I wrote a simple HTA to wrap it, and it gets executed by psexec. I wanted to take that script, strip it down, and convert it to PowerShell. My intension was to  run it through a PSSession but I get access denied. Maybe I will try launching it with SCCM?

I found these three scripts, that I chopped to together for a script that I wanted:

Function JBMURPHY-Install-WindowsUpdates {
	if($(Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired")){
		if (!($reboot)){
    		write-host "There are pending reboots, please pass the reboot command"

	Write-Host -nonewline " + Searching for Updates: "
	$UpdateSession = New-Object -ComObject Microsoft.Update.Session
	$Updates=$updateSession.CreateupdateSearcher().Search("IsAssigned=1 and IsHidden=0 and IsInstalled=0").Updates
	Write-Host " Found [$($Updates.count)] Updates to Download and install`n`n"

	$UpdatesCollection = New-Object -ComObject Microsoft.Update.UpdateColl
	$UpdatesDownloader = $UpdateSession.CreateUpdateDownloader()
	foreach ($Update in $Updates){

		# Add Update to Collection
		if ( $Update.EulaAccepted -eq 0 ) { $Update.AcceptEula() }
		$UpdatesCollection.Add($Update) | out-null

		# Download
		Write-Host -NoNewline " + Downloading Update $($Update.Title)"
		$UpdatesDownloader.Updates = $UpdatesCollection
		$DownloadResult = $UpdatesDownloader.Download()
		$DownloadResultResultCode = switch -exact ($DownloadResult.ResultCode)
		  0   {"NotStarted"}
		  1   {"InProgress"}
		  2   {"Succeeded"}
		  3   {"SucceededWithErrors"}
		  4   {"Failed"}
		  5   {"Aborted"}
		$Message = " [{0}] " -f ($DownloadResultResultCode)
		Write-Host -ForegroundColor Green $message
	if (($Install) -and ($($Updates.count) -gt 0)) {
	write-host "`n`nInstalling updates"
	$Installer = $UpdateSession.CreateUpdateInstaller()
	$Installer.Updates = $UpdatesCollection
	$InstallerResult = $Installer.Install()
	$InstallerResultCode = switch -exact ($InstallerResult.ResultCode)
		  0   {"NotStarted"}
		  1   {"InProgress"}
		  2   {"Succeeded"}
		  3   {"SucceededWithErrors"}
		  4   {"Failed"}
		  5   {"Aborted"}
		$Message = " Installation [{0}] " -f ($InstallerResultCode)
		Write-Host $message
	if (($reboot) -and ($($Updates.count) -gt 0)) {
	if($(Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired"))
    		write-host "Rebooting"

Thanks to those that put the original scripts together

PowerShell script to “process” scripts/functions

I was not happy with the code I wrote in this post. The goal was to write my functions with a “generic” naming convention, and then “process them” – change the naming convention to match my employer’s company name. For example JBMURPHY-AD-GetCurrentSite would be “processed” to Company-AD-GetCurrentSite. In the previous post, I just looped thought and created an alias for each function that matched a RegEx.

I think figured out a better way. Instead of creating aliases, I read the contents of the script line by line, replacing strings as I go, and then create a new file with the new naming conventions.

First I created a hash table with my search and replace strings:

	$SearchAndReplace = @{
	"server.domain.com" = "realservername.company.com";

Next I loop through the files, get their content line by line, loop through the hash table replacing the matching text and output to a temp file.

      foreach ($file in $(gci $SourcePath)) {
    	$tempFile = [System.IO.Path]::GetTempFileName()
    	get-content $file | %{
    	ForEach ($key in $SearchAndReplace.Keys) {
    	  $OutPut=$OutPut -Replace $key,$SearchAndReplace[$key]
    	$OutPut >> $tempFile
    	move-item $tempFile $destination -force

Much cleaner, and I can keep adding search and replace terms to my hash table.

PowerShell to create Aliases for all your functions

I have a naming convention for all the function that I write, for example: JBMURPHY-AD-GetGroup. I wanted to create aliases for all my functions with my employers’s name, for example CompanyName-AD-GetGroup. I created the following function to “grep” all the function names and create an “Alias” file. This file would have all the set-alias commands that would be sourced on PowerShell start up. This function contains Regular Expressions (my weakness), which I borrowed from here.

$tempFile = [System.IO.Path]::GetTempFileName()

Select-String -Pattern "function\s+(\w+-\w+-\w+)\b" *.ps1 | %{
$FUNCTIONS=$_.Matches | %{$_.groups[0].Value -Replace "Function ",""}
Write-host "set-alias $($FUNCTIONS -Replace $OLDNAME,$NEWNAME) $FUNCTIONS -Option AllScope"
Out-File $tempFile -encoding ascii -append -inputobject "set-alias $($FUNCTIONS -Replace $OLDNAME,$NEWNAME) $FUNCTIONS -Option AllScope"
move-item $tempFile "$($NEWNAME)ALIASES.ps1" -force

PowerShell, Active Setup and running a SCCM package “before log on”

I have been struggling with the following idea for a while: How to run a package before before a person logs on using SCCM. There is a setting in SCCM that runs when no one is logged on, but if a person reboots and logs on before SCCM fires, the package will never run. Basically I wanted the ability to make SCCM work like Software Deployment in AD (AD prevents you from logging on until software is completely installed).

The missing piece, for me, was “Active Setup”. Active Setup is pretty well documented here. Combining Active Setup with PowerShell and SCCM, I believe I can run packages before people log on (or at least before they launch any programs).

Here are my steps

  • Step 1 : I need to create an Active Setup registry entry on everyone’s machine that will run a PowerShell script
    • I created this PowerShell script to create the Active Setup registry entries. This will put a StubPath that launches the PowerShell script.
  • Step 2 : Pop up an info message telling the user we are doing some work before the finish logging in.
  • Step 3 : Launch the SCCM Advertised program that you want. Since Active Setup is running as the user, you have to use SCCM to run anything that needs “administrative privileges”. The following code is used inside a PowerShell script to launch an advertisement on the local machine (you need to know the ProgramID and the PackageID):
Function JBMURPHY-SCCM-UIExecuteProgram {
 Param([parameter(Mandatory = $true)]$ProgramID,
       [parameter(Mandatory = $true)]$PackageID)
 $UIResource = New-Object -ComObject UIResource.UIResourceMgr
 $UIResource.ExecuteProgram($ProgramID, $PackageID,$true)

Kinda complicated, but I think it will work. Have you used these methods?

PowerShell Query to find a users computer name in SCCM

We rely on Remote Assistance. Usually, I just type “msra /offerra” in to my PowerShell session and lookup a the user’s computer name in the SCCM report named “Computers for a specific user name”. I wanted to make that process quicker. I wrote the following script to query SCCM for the “list of computer’s who’s last logged on user” is the person I am looking for.

FUNCTION JBMURPHY-SCCM-GetComputerByLastLoggedOnUser {
Param([parameter(Mandatory = $true)]$SamAccountName,
	Get-WmiObject -namespace $SCCMNameSpace -computer $SCCMServer -query "select Name from sms_r_system where LastLogonUserName='$SamAccountName'" | select Name

Let me know if this is useful to you!

Powered by WordPress. Designed by WooThemes