Search This Blog and Web

Monday, October 12, 2015

What's in my Power Shell profile ?

Ed Wilson aka "The Scripting Guy" from Microsoft ran a series of posts in which he wrote about what different users have in their profiles. I was also one of those who replied to that email and he kindly did mention me in his blog.

Today, I want to share what's in my profile. I've customized it a bit more since then.
Here' what it has :
  • Change Error color to Gray from default red. It stresses me to see RED RED on the prompt.
  • Changing the location to my scripts folder to :  Easily call my common scripts, Prevent accidental changes to system32 folder.
  • Checking if I've launched Power Shell as an Admin or not.
  • Customizing in-built PROMPT function to show ADMIN/DBG mode and increment command number after each command run.

Want to know what Power Shell MVP's have ? See here.

If you don't know about profile, in simple terms I'll try to explain.
It's an auto-loading PS1 file which runs each time Power Shell starts. Whatever is written in it is executed and then you can see prompt. the environment variable $Profile tells you the path of the file.

To bypass loading profile, powershell.exe has a parameter called -NoProfile. It's a best practice to use this. If an attacker has control of your system, he can change PROFILE and run arbitrary code. Also, your code may behave differently if someone has changed some functions in it.

Wednesday, August 19, 2015

Backup-SQLDatabase, Restore-SQLDatabase and other SQLPS cmdlets issue after an upgrade of SQL Server

If you are using some cmdlets of SQLPS module and you have done an upgrade from SQL Server 2012 to SQL Server 2014, you're likely to face many unfriendly error messages like this one below :

"Cannot bind parameter 'RelocateFile'. Cannot convert the "Microsoft.SqlServer.Management.Smo.RelocateFile" value of type "Microsoft.SqlServer.Management.Smo.RelocateFile" to type "Microsoft.SqlServer.Management.Smo.RelocateFile"."

This one comes in Restore-SQLDatabase cmdlet but there can be issues in other cmdlets also which some in SQLPS module.
You're most likely to reach this question after search.

The useful answer given there is to use correct assembly version.

$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "MyDB_Data", "c:\data\MySQLServerMyDB.mdf"
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "MyDB_Log", "c:\data\MySQLServerMyDB.ldf"

I did do that initially but then thought of a better version which I'm sharing here.

So, let's understand the problem here first :
* After upgrade there are two versions of SQLPS module present in the system. One to them is of SQL Server 2012(110) and other is of SQL Server 2014(120). Use Get-Module -ListAvailable to check.

* When we run the command "Import-Module SQLPS", it loads both of them. Check using command :

# Get loaded assemblies
([appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*smo*"}).Location

So, the cmdlets like Backup-SQLdatabase get bound to SQL 2012 version(110).

* When we create a new object of SMO like one below, it gets bound to SQL Server 2014 version unless we specify Version as given in answer above.
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogicalMDFName, "$TargetServerMDFFile")
* After that when we run commands like Restore-SQLdatabase and pass -RelocateData parameter, we get the above error.


Basically, we need to make sure that only one of the assemblies get used. It can be either of SQL Server 2012 or SQL Server 2014.

The trick lies in changing the environment variable $env:PSModulePath.
We need to remove one version. We can either make these changes in our session or permanently by modifying Profile file.

* Close existing session if you already have imported both modules.

$TempArray = @()
$TempArray = $env:PSModulePath -split ';'
# 110 for SQL 2012, 120 for SQL 2014, 130 for SQL 2016
$env:PSModulePath = ($TempArray -notmatch '110') -join ';'  
Now, check again available modules :

Only one should show up.
Now, load SQLPS module and run commands.
They'll run fine. Cheers.

Tuesday, March 17, 2015

My biggest contribution yet (System Inventory using Power Shell)

I wrote this tool a few years back but forgot to share. I took one script from here and optimised it using remoting features of Windows Power Shell.

You can download script from Technet.

The following information is gathered :
  • Computer Information like RAM, OS, Processor (with Pie-Chart)
  • Top 10 Running Processes
  • List of Shared Folders
  • Disk-Info showing disks having Low Disk space (less than 20%,configurable)
  • List of Services that are Automatic but Stopped.
Please let me know your comments.

Featured Post

Timeout problem in Backup-SQLDatabase cmdlet in SQLPS module

Power Shell is for managing all types of technologies be it SQL, EXchange, Lync, Office etc.( just name it ) These technologies usually cr...