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 :

ERROR
"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.
http://stackoverflow.com/questions/26377356/problems-with-relocatefile-property-in-the-restore-sqldatabase-cmdlet

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

$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "MyDB_Data", "c:\data\MySQLServerMyDB.mdf"
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, 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.

PROBLEM
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.

SOLUTION

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.

SQLCMD.exe in SQL Server 2012 does not give real-time output logging

I hit into an another issue in SQL 2012 related to real-time logging.
I've logged bug here : https://connect.microsoft.com/SQLServer/feedback/details/1002565

The problem is when a long running Stored Procedure is run using sqlcmd.exe of SQL Server 2012, the output is not displayed in real-time. It gets displayed only after the query completes or user stops it.

In SQL Server 2008's sqlcmd.exe, the output is displayed as-is without any delay.

Workaround : The behaviour is not there in OSQL.exe.

Since Microsoft wants us to use sqlcmd.exe in place of osql.exe, I think this thing should be fixed.
Please vote-up.