tag:blogger.com,1999:blog-43385139044067697672024-03-21T20:47:22.647-07:00Power Shell DiariesMy Life with PSRD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-4338513904406769767.post-41901491300318516472016-09-27T14:59:00.000-07:002016-09-27T14:59:23.870-07:00Try Power Shell commands on a web-page!<span style="font-family: "verdana" , sans-serif;">There's a #PowerShell container available for which I saw a tweet about. Time to try in my blog :</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"><iframe frameborder="0" height="300" src="https://containerdemo.learnondemandsystems.com/Lab/LaunchLab?containerImageId=sha256:02cb7f65d61b073a8b6b62ebdec43727bf2b14d5d5ce11e9cccfe583ba3bff92" width="600"></iframe>
</span>
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Go-Ahead. Play it, let connect and load and then type the commands in "<b>Input</b>" and press enter.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Example : gps, dir env:, whoami</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Source : http://www.learnondemandsystems.com/powershell-containers-blogs/ </span>RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-22393161489068828862015-10-12T02:06:00.000-07:002015-10-12T02:29:40.594-07:00What'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.<br />
<a href="http://blogs.technet.com/b/heyscriptingguy/archive/2014/05/21/what-39-s-in-your-powershell-profile-users-39-favorites-part-2.aspx">http://blogs.technet.com/b/heyscriptingguy/archive/2014/05/21/what-39-s-in-your-powershell-profile-users-39-favorites-part-2.aspx</a><br />
<br />
Today, I want to share what's in my profile. I've customized it a bit more since then.<br />
Here' what it has :<br />
<ul>
<li>Change Error color to Gray from default red. It stresses me to see RED RED on the prompt.</li>
<li>Changing the location to my scripts folder to : Easily call my common scripts, Prevent accidental changes to system32 folder.</li>
<li>Checking if I've launched Power Shell as an Admin or not.</li>
<li>Customizing in-built PROMPT function to show ADMIN/DBG mode and increment command number after each command run.</li>
</ul>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0Ds2aVDisG1Z_JRjSxg3UtO4Ba81w_tzjvFDUfjG_oHGkBWd5LcSQb_VSuZJR-g_jPLLceXKUcbhvMfNotLMs_VbI-Usjob51bDihxBeEj79wjmY8gFuUyQxToq7R9dZNxC87YMwu3eA/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0Ds2aVDisG1Z_JRjSxg3UtO4Ba81w_tzjvFDUfjG_oHGkBWd5LcSQb_VSuZJR-g_jPLLceXKUcbhvMfNotLMs_VbI-Usjob51bDihxBeEj79wjmY8gFuUyQxToq7R9dZNxC87YMwu3eA/s1600/Capture.JPG" /></a></div>
<script src="https://gist.github.com/anonymous/fd545d9e44c1fb54fcef.js"></script><br />
Want to know what Power Shell MVP's have ? See <a href="https://www.blogger.com/%3Cscript%20src=%22https://gist.github.com/anonymous/fd545d9e44c1fb54fcef.js%22%3E%3C/script%3E" target="_blank">here</a>.<br />
<br />
If you don't know about profile, in simple terms I'll try to explain.<br />
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.<br />
<br />
To bypass loading profile, powershell.exe has a parameter called -<b>NoProfile</b>. 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.RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-61699141884017702442015-08-19T03:54:00.000-07:002015-08-19T03:54:44.139-07:00Backup-SQLDatabase, Restore-SQLDatabase and other SQLPS cmdlets issue after an upgrade of SQL ServerIf 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 :<br />
<br />
<span style="font-size: large;"><b>ERROR</b></span><br />
"<span style="color: #cc0000;">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".</span>"<br />
<br />
This one comes in Restore-SQLDatabase cmdlet but there can be issues in other cmdlets also which some in SQLPS module.<br />
You're most likely to reach this question after search.<br />
<a href="http://stackoverflow.com/questions/26377356/problems-with-relocatefile-property-in-the-restore-sqldatabase-cmdlet">http://stackoverflow.com/questions/26377356/problems-with-relocatefile-property-in-the-restore-sqldatabase-cmdlet</a><br />
<br />
The useful answer given there is to use correct assembly version.<br />
<br />
<pre style="background-color: #eeeeee; border: 0px; color: #222222; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;">$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"</pre>
<br />
I did do that initially but then thought of a better version which I'm sharing here.<br />
<br />
<span style="font-size: large;"><b>PROBLEM</b></span><br />
So, let's understand the problem here first :<br />
* 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.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik8P-yQg0XfffIx8-BZ6FMqdkyQE17B3zPmasm10EE_b34wjpWtSMXXXZ7PAHF7yBsQXH6kd7OwTx2y6L5e30eKYRihkesOPDk_4Wlib9vTaW7KgVxMtYWJT5RAYbZKOaa5uK5aE7rPqs/s1600/MultipleSQLPS+modules.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik8P-yQg0XfffIx8-BZ6FMqdkyQE17B3zPmasm10EE_b34wjpWtSMXXXZ7PAHF7yBsQXH6kd7OwTx2y6L5e30eKYRihkesOPDk_4Wlib9vTaW7KgVxMtYWJT5RAYbZKOaa5uK5aE7rPqs/s640/MultipleSQLPS+modules.JPG" width="640" /></a></div>
<br />
<br />
* When we run the command "Import-Module SQLPS", it loads both of them. Check using command :<br />
<br />
<pre style="background-color: #eeeeee; border: 0px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;"><span style="color: #222222; font-family: Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, sans-serif;"><span style="font-size: 13px;"># Get loaded assemblies
([appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*smo*"}).Location</span></span></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisuccXn37MzXIJjqqTm3VYImxTCpHQ8nHpKie10UbZI4jGdGUxw-33L4RXaOBMJYt37vsSSf-xXvMu2hdV-COhdw_XFkgOjIyKQQchoEOoOex7eyVlm8ayWZSuinUhaBuFZem-GpnVEGg/s1600/Loaded+Assemblies.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="114" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisuccXn37MzXIJjqqTm3VYImxTCpHQ8nHpKie10UbZI4jGdGUxw-33L4RXaOBMJYt37vsSSf-xXvMu2hdV-COhdw_XFkgOjIyKQQchoEOoOex7eyVlm8ayWZSuinUhaBuFZem-GpnVEGg/s640/Loaded+Assemblies.JPG" width="640" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
So, the cmdlets like Backup-SQLdatabase get bound to SQL 2012 version(110).</div>
<div>
<br /></div>
<div>
* 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.</div>
<div>
<pre style="background-color: #eeeeee; border: 0px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;"><span style="color: #222222; font-family: Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, sans-serif;"><span style="font-size: 13px;">$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogicalMDFName, "$TargetServerMDFFile")</span></span></pre>
<div>
* After that when we run commands like Restore-SQLdatabase and pass -RelocateData parameter, we get the above error.</div>
</div>
<div>
<br /></div>
<b><span style="font-size: large;">SOLUTION</span></b><br />
<br />
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.<br />
<br />
The trick lies in changing the environment variable <a href="https://technet.microsoft.com/en-us/library/dd878326(v=vs.85).aspx" target="_blank">$env:PSModulePath</a>.<br />
We need to remove one version. We can either make these changes in our session or permanently by modifying Profile file.<br />
<br />
* Close existing session if you already have imported both modules.<br />
<br />
<pre style="background-color: #eeeeee; border: 0px; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; width: auto; word-wrap: normal;"><span style="color: #222222; font-family: Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, sans-serif;"><span style="font-size: 13px;">$TempArray = @()
$TempArray = $env:PSModulePath -split ';'
# 110 for SQL 2012, 120 for SQL 2014, 130 for SQL 2016
$env:PSModulePath = ($TempArray -notmatch '110') -join ';'</span> </span> </pre>
Now, check again available modules :<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKwsnU_8pQblV2lQrf1ilwzTrd0yz0nQ6qdJ58t5jJBbnJbKt4PdcwPDDzqpUS3vH6r-hCQgbzRIrCSgtQ1yuW8avOH96qj9qtoKpDHHurLvcn2XMfhBVY2k2Fuwvfe1aYHpEn_UEvMNU/s1600/One+module+loaded.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="144" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKwsnU_8pQblV2lQrf1ilwzTrd0yz0nQ6qdJ58t5jJBbnJbKt4PdcwPDDzqpUS3vH6r-hCQgbzRIrCSgtQ1yuW8avOH96qj9qtoKpDHHurLvcn2XMfhBVY2k2Fuwvfe1aYHpEn_UEvMNU/s640/One+module+loaded.JPG" width="640" /></a></div>
<div>
<br />
Only one should show up.</div>
Now, load SQLPS module and run commands.<br />
They'll run fine. Cheers.RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-76841779031715386012015-03-17T02:23:00.000-07:002015-03-20T01:37:30.946-07:00My 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 <a href="https://www.simple-talk.com/sysadmin/powershell/building-a-daily-systems-report-email-with-powershell/" target="_blank">here </a>and optimised it using remoting features of Windows Power Shell.<br />
<br />
You can download script from Technet.<br />
<a href="https://gallery.technet.microsoft.com/System-Inventory-using-dcdab843">https://gallery.technet.microsoft.com/System-Inventory-using-dcdab843</a><br />
<br />
<div style="font-size: 13.0080003738403px;">
<span style="font-family: Verdana, sans-serif;">The following information is gathered :</span></div>
<ul style="font-size: 13.0080003738403px; margin-left: 40px; padding-left: 0px;">
<li style="padding-bottom: 10px;"><span style="font-family: Verdana, sans-serif;">Computer Information like RAM, OS, Processor (with Pie-Chart)</span></li>
<li style="padding-bottom: 10px;"><span style="font-family: Verdana, sans-serif;">Top 10 Running Processes</span></li>
<li style="padding-bottom: 10px;"><span style="font-family: Verdana, sans-serif;">List of Shared Folders</span></li>
<li style="padding-bottom: 10px;"><span style="font-family: Verdana, sans-serif;">Disk-Info showing disks having Low Disk space (less than 20%,configurable)</span></li>
<li style="padding-bottom: 10px;"><span style="font-family: Verdana, sans-serif;">List of Services that are Automatic but Stopped.</span></li>
</ul>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Please let me know your comments.</span><br />
<div style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13.0080003738403px;">
<br /></div>
RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-36934908262275998442014-11-11T04:07:00.000-08:002014-11-11T04:07:37.503-08:00[Script] RPC Port Allocation using Power Shell<span style="font-size: 13px;"><span style="font-family: Verdana, sans-serif;">Remote Procedure Call (RPC) dynamic port allocation is used by server applications and remote administration applications such as Dynamic Host Configuration Protocol (DHCP) Manager, Windows Internet Name Service (WINS) Manager, and so on.</span></span><br />
<br />
<span style="font-family: Verdana, sans-serif;">Basically, the automation of this article. </span><br />
<a href="https://support.microsoft.com/kb/154596?wa=wsignin1.0"><span style="font-family: Verdana, sans-serif;">https://support.microsoft.com/kb/154596?wa=wsignin1.0</span></a><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">I've added this at Script Center.</span><br />
<span style="font-family: Verdana, sans-serif;">Please download at <a href="https://gallery.technet.microsoft.com/scriptcenter/RPC-Port-Allocation-using-7f1c52c1">https://gallery.technet.microsoft.com/scriptcenter/RPC-Port-Allocation-using-7f1c52c1</a>.</span>RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-48823244658254610872014-10-21T03:02:00.001-07:002014-10-21T03:02:55.827-07:00SQLCMD.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.<br />
I've logged bug here : <a href="https://connect.microsoft.com/SQLServer/feedback/details/1002565">https://connect.microsoft.com/SQLServer/feedback/details/1002565</a><br />
<br />
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.<br />
<br />
In SQL Server 2008's sqlcmd.exe, the output is displayed as-is without any delay.<br />
<br />
Workaround : The behaviour is not there in OSQL.exe.<br />
<br />
Since Microsoft wants us to use sqlcmd.exe in place of osql.exe, I think this thing should be fixed.<br />
Please vote-up.RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-41025359813942344002014-10-21T02:33:00.000-07:002014-10-21T02:33:32.915-07:00[Script] Get Disk Space using PowerShellI posted a simple script at Technet which returns the free space of a specific drive on any computer.<br />
<br />
Take a look here : <a href="https://gallery.technet.microsoft.com/scriptcenter/Get-Disk-Space-using-69c88b59">https://gallery.technet.microsoft.com/scriptcenter/Get-Disk-Space-using-69c88b59</a><br />
<br />
Basic Code :<br />
<br />
<pre class="powershell" style="background-color: white; border-color: rgb(208, 210, 210); border-style: solid; border-width: 2px 1px; color: darkred; font-size: 12px; line-height: 15px; margin-bottom: 10px !important; padding: 10px; white-space: pre-wrap !important; word-wrap: break-word !important;">Write<span class="powerShell__operator" style="color: grey;">-</span>Host <span class="powerShell__string" style="color: blue;">"Getting Information for Drive : $DriveID for Computer : $CompName"</span>
<span class="powerShell__com" style="color: green;"># Get Info about the disk specified</span>
<span class="powerShell__variable" style="color: #aa7700;">$Disk</span> = <span class="powerShell__cmdlets" style="color: #006699; font-weight: bold;">Get-WMIObject</span> Win32_LogicalDisk <span class="powerShell__operator" style="color: grey;">-</span>ComputerName <span class="powerShell__variable" style="color: #aa7700;">$CompName</span> <span class="powerShell__operator" style="color: grey;">-</span><span class="powerShell__keyword" style="color: #006699; font-weight: bold;">Filter</span> <span class="powerShell__string" style="color: blue;">"DeviceID='$DriveID'"</span>
<span class="powerShell__variable" style="color: #aa7700;">$FreeSpace</span> = [System.Math]::Round(((<span class="powerShell__variable" style="color: #aa7700;">$Disk</span>.FreeSpace) <span class="powerShell__operator" style="color: grey;">/</span> 1GB))
Write<span class="powerShell__operator" style="color: grey;">-</span>Host <span class="powerShell__string" style="color: blue;">"Drive $DriveID has freespace : $FreeSpace GB"</span></pre>
<br />
The script can be improved using PS-Remoting for better performance.RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-8908061797081433002014-04-10T06:11:00.002-07:002016-06-18T16:22:42.855-07:00[TOOL] SQL Service Account password change/update (multiple servers)<span style="font-family: "verdana" , sans-serif;">Today, I'm going to share a tool that I've developed for changing password of SQL service account on (n) servers.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"><b>Background </b>: Typically in an organisation as a best-practice account password is changed every 90 days(to prevent brute-force attack's success). Many Windows services are run using these accounts. Hence, there password needs to be updated.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"><b>Manual Way </b>: Take a remote desktop connection to the server, open services.msc, select service and update password in Logon tab.</span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img alt="serviceaccount6" src="http://voiceofthedba.files.wordpress.com/2011/12/serviceaccount6_thumb.jpg?w=383&h=425" style="margin-left: auto; margin-right: auto;" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Src : http://www.sqlservercentral.com/blogs/steve_jones/2011/12/15/how-to-change-the-sql-server-service-cccount/</td></tr>
</tbody></table>
<span style="font-family: "verdana" , sans-serif;">This is fine for 1 or 2 servers but this process does not scale-up in an enterprise where there are many servers and also many services are being run on each server(like sqlserveragent,ssis,ssas,ssrs).</span><br />
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><b>Automation </b>: As always, Power Shell is there to the rescue. There are two methods to achieve that 1. SMO 2. WMI</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">I first tried using SMO but it broke at crucial point.<i> (Avoid SMO as far as you can.)</i></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">So, the second option with me was WMI.</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">Basically, all SQL services are Windows services and can be managed using Win32_Service class.</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<div>
<span style="font-family: "verdana" , sans-serif;"><b>Basic Code </b>:</span><span style="background-color: #eeeeee; font-family: "consolas" , "menlo" , "monaco" , "lucida console" , "liberation mono" , "dejavu sans mono" , "bitstream vera sans mono" , "courier new" , monospace , serif; font-size: 14px; line-height: 17.804800033569336px; white-space: inherit;">$service = gwmi win32_service -computer [computername] -filter "name='whatever'"</span></div>
<pre style="background-color: #eeeeee; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; font-size: 14px; line-height: 17.804800033569336px; margin-bottom: 10px; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;">$service.change($null,$null,$null,$null,$null,$null,$null,"P@ssw0rd")</code></pre>
</div>
<div>
<span style="font-family: "verdana" , sans-serif;"><b>Full Code </b>:</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"></span><br />
<div>
<span style="font-family: "verdana" , sans-serif;"># Script should be run on systems having PowerShell V3 installed.</span></div>
<span style="font-family: "verdana" , sans-serif;">
</span>
<div>
<span style="font-family: "verdana" , sans-serif;"># Run PowerShell as ADMINISTRATOR.</span></div>
<span style="font-family: "verdana" , sans-serif;">
<div>
# Remoting must be enabled on destination servers using "Enable-PSRemoting -Force"</div>
<div>
# In case of errors, please try running this command on local machine : gwmi win32_bios -comp "server1" where "server1" is the machine for which you get error. This should run fine for utility to proceed.</div>
<div>
<br /></div>
<div>
<span style="color: #38761d;"><i><#</i></span></div>
<div>
<span style="color: #38761d;"><i>.SYNOPSIS</i></span></div>
<div>
<span style="color: #38761d;"><i> Service Account Password Change</i></span></div>
<div>
<span style="color: #38761d;"><i>.DESCRIPTION</i></span></div>
<div>
<span style="color: #38761d;"><i> Power Shell utility to change password of SQL service account on (n) servers</i></span></div>
<div>
<span style="color: #38761d;"><i>.PARAMETER Path</i></span></div>
<div>
<span style="color: #38761d;"><i> $servers : List of Server(s) separated by comma</i></span></div>
<div>
<span style="color: #38761d;"><i> $AccountName : Name of the account through which service is running and whose password needs to be changed</i></span></div>
<div>
<span style="color: #38761d;"><i>.EXAMPLE</i></span></div>
<div>
<span style="color: #38761d;"><i> cd "E:\Utilities\ServiceAccount";</i></span></div>
<div>
<span style="color: #38761d;"><i> .\SQLServiceAccount.ps1 -Servers "s1","s2" -accountName "redmond\v-any"</i></span></div>
<div>
<span style="color: #38761d;"><i>.NOTES</i></span></div>
<div>
<span style="color: #38761d;"><i> Author: r1111111r@gmail.com</i></span></div>
<div>
<span style="color: #38761d;"><i> Date : 15/01/2014</i></span></div>
<div>
<span style="color: #38761d;"><i> Version : 1.0</i></span></div>
<div>
<span style="color: #38761d;"><i> You must be having access to the servers.</i></span></div>
<div>
<span style="color: #38761d;"><i> This should be run only from systems having PowerShell V3. Destination servers may have V2/V3.</i></span></div>
<div>
<span style="color: #38761d;"><i>.INPUTS</i></span></div>
<div>
<span style="color: #38761d;"><i> List of servers and account name</i></span></div>
<div>
<span style="color: #38761d;"><i> Then password when prompted</i></span></div>
<div>
<span style="color: #38761d;"><i>.OUTPUTS</i></span></div>
<div>
<span style="color: #38761d;"><i> On Screen and file-logging.</i></span></div>
<div>
<span style="color: #38761d;"><i> It logs into the directory from which it's invoked/called.</i></span></div>
<div>
<span style="color: #38761d;"><i>#></i></span></div>
<div>
<br /></div>
<div>
<i>param(</i></div>
<div>
<i> [cmdletBinding()]</i></div>
<div>
<i> # Seprate list by commas eg. "ser1","ser2"</i></div>
<div>
<i> [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()]</i></div>
<div>
<i> [string[]] $servers,</i></div>
<div>
<i> # eg. "domain\account"</i></div>
<div>
<i> [Parameter(Mandatory=$true)] [ValidatePattern("(\w+)\\(\w+)")]</i></div>
<div>
<i> [string] $accountName</i></div>
<div>
<i> )</i></div>
<div>
<i><br /></i></div>
<div>
<i># Bail-out in case of any error</i></div>
<div>
<i>$ErrorActionPreference = "Stop"</i></div>
<div>
<i><br /></i></div>
<div>
<i># Generic helper function</i></div>
<div>
<i>function ConvertTo-ScriptBlock</i></div>
<div>
<i>(</i></div>
<div>
<i> [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()]</i></div>
<div>
<i> [string] $ScriptString</i></div>
<div>
<i>)</i></div>
<div>
<i>{</i></div>
<div>
<i> $ScriptBlock = $ExecutionContext.InvokeCommand.NewScriptBlock($ScriptString)</i></div>
<div>
<i> Return $ScriptBlock</i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<i>function Get-ScriptDirectory</i></div>
<div>
<i>{</i></div>
<div>
<i> # Look up this script file's path</i></div>
<div>
<i> $Invocation = (Get-Variable MyInvocation -Scope 1).Value</i></div>
<div>
<i> Split-Path $Invocation.MyCommand.Path</i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<i># This function writes the input Msg into the console window</i></div>
<div>
<i>function Log</i></div>
<div>
<i>(</i></div>
<div>
<i> [string] $Msg = $(throw "Msg parameter not specified!")</i></div>
<div>
<i> # Define the foreground color in which to log; if not specified, default provided</i></div>
<div>
<i> ,[string] $Fore = "cyan"</i></div>
<div>
<i>)</i></div>
<div>
<i>{</i></div>
<div>
<i> Write-Host (Get-Date -format "yyyy-MM-dd hh:mm:ss") $Msg `n`r -fore $Fore</i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<i>Log "Account specified is $accountName"</i></div>
<div>
<i># Get password from the user and store it as a "Secure String"</i></div>
<div>
<i># UserName, Message parameters are available only in V3</i></div>
<div>
<i>$pass = (Get-Credential -UserName $accountName -Message "Enter password for account")</i></div>
<div>
<i>If($pass -eq $null) {Log "Password not specified"; throw}</i></div>
<div>
<i><br /></i></div>
<div>
<i># get alias name from domain\alias e.g. domain\alias will return alias</i></div>
<div>
<i>$account = ($accountName.Split("\")[1])</i></div>
<div>
<i><br /></i></div>
<div>
<i>$path = (Get-ScriptDirectory)</i></div>
<div>
<i>if(!(Test-Path $path)) {Log "Invalid log path $path" -Fore Red ; throw}</i></div>
<div>
<i><br /></i></div>
<div>
<i># Checks whether Transcript is in progress</i></div>
<div>
<i>function Test-Transcribing {</i></div>
<div>
<i> $externalHost = $host.gettype().getproperty("ExternalHost",</i></div>
<div>
<i> [reflection.bindingflags]"NonPublic,Instance").getvalue($host, @())</i></div>
<div>
<i><br /></i></div>
<div>
<i> try {</i></div>
<div>
<i> $externalHost.gettype().getproperty("IsTranscribing",</i></div>
<div>
<i> [reflection.bindingflags]"NonPublic,Instance").getvalue($externalHost, @())</i></div>
<div>
<i> } catch {</i></div>
<div>
<i> Write-Warning "This host does not support transcription."</i></div>
<div>
<i> }</i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<b><span style="color: #76a5af;"><i># The function that stops services (including dependent services), changes password & starts them again</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i>function Change-ServiceAccount()</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i>{</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> # This is the code to fetch the services for SQL server including SSIS, OLAP</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> $services = Get-WmiObject -Class win32_service -Filter "DisplayName LIKE '%SQL%'"</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Write-Host "Total SQL services are : " ($services.Count)`n`r</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> </i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> foreach($service in $services)</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> {</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Write-Host "Processing service : " $service.Name`n`r</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> # Need services that are run by the account specified</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> if($service.StartName -match "$account")</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> {</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> $serviceName = $service.Name</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Write-Host "Service is running under : " $service.StartName`n`r -ForegroundColor Yellow</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> </i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Log "Stopping service : $serviceName"</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> # Stop All services so that "Dependent Services" are not running</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> # It's better and simpler than $service.Stop() as the later does not stop dependent services by default</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Get-Service -Name $serviceName | Stop-Service -Force -Verbose</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Log "Changing password for $serviceName"</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> # uint32 Change(DisplayName,PathName,ServiceType,ErrorControl,StartMode,DesktopInteract,StartName,StartPassword,LoadOrderGroup,LoadOrderGroupDependencies,ServiceDependencies);</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> $RetCode = $service.Change($null,$null,$null,$null,$null,$null,$null,$pass)</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> # Get error message from return code</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> $msg = RetCodes -Code ($RetCode.ReturnValue)</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> if($RetCode.ReturnValue -ne 0) { throw "Error : $msg" }</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Start-Sleep 2</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Log "Starting service : $serviceName"</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> $RetCode = $service.StartService()</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i><br /></i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> $msg = RetCodes -Code ($RetCode.ReturnValue)</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> if($RetCode.ReturnValue -ne 0) { throw "Error : $msg" }</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> </i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> }</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> else</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> {</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> Write-Host "Skipping Service : " $service.Name `n`r -ForegroundColor White</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> }</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i> }</i></span></b></div>
<div>
<b><span style="color: #b45f06;"><i>}</i></span></b></div>
<div>
<i><br /></i></div>
<div>
<i># These are the list of return codes of Change() function which changes password</i></div>
<div>
<i>function RetCodes ([int] $Code)</i></div>
<div>
<i>{</i></div>
<div>
<i> switch ($Code)</i></div>
<div>
<i> {</i></div>
<div>
<i> 0 { "Success" }</i></div>
<div>
<i> 1 { "Not Supported" }</i></div>
<div>
<i> 2 { "Access Denied"}</i></div>
<div>
<i> 3 { "Dependent Services Running"}</i></div>
<div>
<i> 4 { "Invalid Service Control"}</i></div>
<div>
<i> 5 { "Service Cannot Accept Control"}</i></div>
<div>
<i> 6 { "Service Not Active"}</i></div>
<div>
<i> 7 { "Service Request Timeout"}</i></div>
<div>
<i> 8 { "Unknown Failure"}</i></div>
<div>
<i> 9 { "Path Not Found"}</i></div>
<div>
<i> 10 { "Service Already Running"}</i></div>
<div>
<i> 11 { "Service Database Locked"}</i></div>
<div>
<i> 12 { "Service Dependency Deleted"}</i></div>
<div>
<i> 13 { "Service Dependency Failure"}</i></div>
<div>
<i> 14 { "Service Disabled"}</i></div>
<div>
<i> 15 { "Service Logon Failure"}</i></div>
<div>
<i> 16 { "Service Marked For Deletion"}</i></div>
<div>
<i> 17 { "Service No Thread"}</i></div>
<div>
<i> 18 { "Status Circular Dependency"}</i></div>
<div>
<i> 19 { "Status Duplicate Name"}</i></div>
<div>
<i> 20 { "Status Invalid Name"}</i></div>
<div>
<i> 21 { "Status Invalid Parameter"}</i></div>
<div>
<i> 22 { "Status Invalid Service Account"}</i></div>
<div>
<i> 23 { "Status Service Exists"}</i></div>
<div>
<i> 24 { "Service Already Paused"}</i></div>
<div>
<i> default {"Invalid return code"}</i></div>
<div>
<i> }</i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<i># Make function calls from here</i></div>
<div>
<i>try</i></div>
<div>
<i>{</i></div>
<div>
<i> # Read local function</i></div>
<div>
<i> $RetFunction = (Get-Content Function:\RetCodes)</i></div>
<div>
<i> $RetFunction = "Function RetCodes {" + $RetFunction + "}"</i></div>
<div>
<i> $RetScriptBlock = (ConvertTo-ScriptBlock -ScriptString $RetFunction)</i></div>
<div>
<i><br /></i></div>
<div>
<i> $LogFunction = (Get-Content Function:\Log)</i></div>
<div>
<i> $LogFunction = "Function Log {" + $LogFunction + "}"</i></div>
<div>
<i> $LogScriptBlock = (ConvertTo-ScriptBlock -ScriptString $LogFunction)</i></div>
<div>
<i><br /></i></div>
<div>
<i> #Create log file to store execution result</i></div>
<div>
<i> $StartTime = Get-Date -Format "yyyyMMdd_hhmm"</i></div>
<div>
<i> $logFile = "$path\ServiceAccount_$StartTime.log"</i></div>
<div>
<i> Start-Transcript $logFile</i></div>
<div>
<i><br /></i></div>
<div>
<i> foreach( $server in $servers)</i></div>
<div>
<i> {</i></div>
<div>
<i> # Check Server is valid and alive</i></div>
<div>
<i> Log "Testing Connection to $server"</i></div>
<div>
<i><br /></i></div>
<div>
<i> If(Test-Connection -ComputerName $server -Quiet)</i></div>
<div>
<i> {</i></div>
<div>
<i> Log "Creating Connection to $server"</i></div>
<div>
<i> $session = New-PSSession -ComputerName $server</i></div>
<div>
<i><br /></i></div>
<div>
<i> If($session.State -eq "Opened") { Log "Successfully connected to : $server" -Fore "Yellow"}</i></div>
<div>
<i> </i></div>
<div>
<i> # Create function RetCodes in remote session</i></div>
<div>
<i> Invoke-Command -Session $session -ScriptBlock $RetScriptBlock</i></div>
<div>
<i><br /></i></div>
<div>
<i> # Create function Log in remote session</i></div>
<div>
<i> Invoke-Command -Session $session -ScriptBlock $LogScriptBlock</i></div>
<div>
<i><br /></i></div>
<div>
<i> # Pass local variable to remote session</i></div>
<div>
<i> Invoke-Command -Session $session -ScriptBlock {param($account)} -ArgumentList $account</i></div>
<div>
<i> Invoke-Command -Session $session -ScriptBlock {param($pass)} -ArgumentList $pass.GetNetworkCredential().Password</i></div>
<div>
<i><br /></i></div>
<div>
<i> # Call local function in remote session</i></div>
<div>
<i> Invoke-Command -Session $session -ScriptBlock ${function:Change-ServiceAccount}</i></div>
<div>
<i> }</i></div>
<div>
<i> else</i></div>
<div>
<i> {</i></div>
<div>
<i> Log "Server : $server is not valid/available, skipping it" -Fore "Red"</i></div>
<div>
<i> }</i></div>
<div>
<i> }</i></div>
<div>
<i><br /></i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<i>catch </i></div>
<div>
<i>{ </i></div>
<div>
<i> Write-Host "oops! " $Error[0] `n`r -ForegroundColor Red</i></div>
<div>
<i> Write-Host "Please check `"http://msdn.microsoft.com/en-us/library/aa384901(v=vs.85).aspx`" for error details"</i></div>
<div>
<i> #Start-Process "http://msdn.microsoft.com/en-us/library/aa384901(v=vs.85).aspx" -ErrorAction SilentlyContinue</i></div>
<div>
<i>}</i></div>
<div>
<i><br /></i></div>
<div>
<i>finally</i></div>
<div>
<i>{</i></div>
<div>
<i> Log "Clearing Sessions" -Fore "Yellow"</i></div>
<div>
<i> Get-PSSession | Remove-PSSession</i></div>
<div>
<i> if(Test-Transcribing) { Stop-Transcript }</i></div>
<div>
<i>}</i></div>
</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><b>Important Points </b>: The above script is optimized using remoting and is amazingly fast. If you need a quick short version, only read and use Change-ServiceAccount function.</span></div>
<div>
<span style="font-family: "verdana" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "verdana" , sans-serif;">At the end, thanks to the all the people whose information I've used in creating this utility. Please let me know your comments on this.</span></div>
RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-32253639431074310462013-10-23T05:11:00.001-07:002013-10-23T05:11:57.507-07:00Timeout problem in Backup-SQLDatabase cmdlet in SQLPS modulePower Shell is for managing all types of technologies be it SQL, EXchange, Lync, Office etc.( <i>just name it</i>)<br />
<br />
These technologies usually create there own cmdlets, and then bundle into a "Module" for distribution.<br />
SQL team initially followed different approach by creating mini-shell "sqlps.exe" but it was not well accepted.<br />
Now, they give there cmdlets in SQLPS module.<br />
<br />
"Import-Module SQLPS" command loads this module.<br />
<br />
There are multiple cmdlets available for multiple tasks like querying, backup/restore, security management, policy management etc. . Two cmdlets for taking backup/restore are :<br />
<br />
<ul>
<li>Backup-SQLDatabase</li>
<li>Restore-SQLDatabase</li>
</ul>
<div>
These two use SMO(<i>Server Management Objects</i>) classes underneath and then run T-SQL commands.</div>
<div>
<div class="MsoNormal">
<span lang="EN-US"><span style="font-family: inherit;">Backup-SqlDatabase is a wrapper over SMO object model (managed
code); SMO constructs T-SQL code and executes query using ADO.NET.</span><span style="color: #1f497d;"><o:p></o:p></span></span></div>
</div>
<div>
SMO classes have been available for a long time, and are widely used in automation.</div>
<div>
There are different ways to perform backup/restore operations as specified <a href="about:blank">here</a>.</div>
<div>
<br /></div>
If we use Power Shell to do automation of this activity ( <i>which we should </i>), we can use the most simple cmdlets to use aka Backup-SQLDatabase and Restore-SQLDatabase.<br />
<br />
However, both these have one serious bug i.e. if a backup or restore takes more than 10 minutes, they time-out and fail.<br />
Usually, backup/restore of large databases take more than 10 minutes to complete (<i>which is why we wanted to automate this</i>).<br />
<br />
Here's the error you will see if you run this command :<br />
<span style="color: blue; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">Backup-SqlDatabase</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-ServerInstance</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: orangered; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">$Server</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-Database</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: orangered; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">$DatabaseName</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-BackupFile</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: orangered; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">$BackUpFile</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-CompressionOption</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: blueviolet; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">On</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-<span style="background: yellow;">ConnectionTimeout</span></span><span style="background-color: yellow; background-position: initial initial; background-repeat: initial initial; font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="background: yellow; color: purple; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">0</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-Initialize</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-Verbose</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: navy; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">-ea</span><span style="font-family: 'Lucida Console'; font-size: 9pt;"> </span><span style="color: blueviolet; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;">Stop</span><br />
<span style="color: blueviolet; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;"><br /></span>
<div class="MsoNormal">
Here’s the error exactly after 600
seconds of execution :<span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: cyan; font-family: "Lucida Console"; font-size: 9.0pt;">VERBOSE: 60
percent processed.</span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: cyan; font-family: "Lucida Console"; font-size: 9.0pt;">VERBOSE: The
backup or restore was aborted.</span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: grey; font-family: "Lucida Console"; font-size: 9.0pt;">The wait
operation timed out</span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: grey; font-family: "Lucida Console"; font-size: 9.0pt;">
+ CategoryInfo :
InvalidOperation: (:) [Backup-SqlDatabase], Win3 </span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: grey; font-family: "Lucida Console"; font-size: 9.0pt;"> 2Exception</span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: grey; font-family: "Lucida Console"; font-size: 9.0pt;">
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.P </span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: grey; font-family: "Lucida Console"; font-size: 9.0pt;"> owerShell.BackupSqlDatabaseCommand</span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal" style="background: black; text-autospace: none;">
<span style="color: grey; font-family: "Lucida Console"; font-size: 9.0pt;">
+ PSComputerName : localhost </span><span lang="EN-US"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: inherit;">This is a very weird issue.</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">You can try </span>re configuring
“remote query timeout” to 0 as given <a href="http://technet.microsoft.com/en-us/library/ms189040.aspx" target="_blank">here</a>,
but the issue persists.</div>
<div class="MsoNormal">
ConnectionTimeout switch is different than StatementTimeout. This switch is not available in these cmdlets.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The <b><span style="color: #38761d;">workaround</span></b> lies in setting this property by connecting to SMO server.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="background: white; text-autospace: none;">
<span style="color: orangered; font-family: "Lucida Console"; font-size: 9.0pt;">$serverConn</span><span style="font-family: "Lucida Console"; font-size: 9.0pt;"> <span style="color: darkgrey;">=</span> N<span style="color: blue;">ew-Object</span> (<span style="color: darkred;">"Microsoft.SqlServer.Management.Smo.Server"</span>)
<span style="color: orangered;">$server</span><o:p></o:p></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal" style="background: white; text-autospace: none;">
<span style="color: orangered; font-family: "Lucida Console"; font-size: 9.0pt;">$serverConn</span><span style="color: darkgrey; font-family: "Lucida Console"; font-size: 9.0pt;">.</span><span style="font-family: "Lucida Console"; font-size: 9.0pt;">ConnectionContext<span style="color: darkgrey;">.</span>StatementTimeout <span style="color: darkgrey;">=</span>
<span style="color: purple;">0 <o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; text-autospace: none;">
<span style="font-family: "Lucida Console"; font-size: 9.0pt;"><span style="color: purple;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">So, I have to pass SMO.Server
object to this cmdlet.</span><span style="color: #1f497d;"><o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; text-autospace: none;">
</div>
<div class="MsoNormal" style="background: white; text-autospace: none;">
<span style="color: blue; font-family: "Lucida Console"; font-size: 9.0pt;">Backup-SqlDatabase</span><span style="font-family: "Lucida Console"; font-size: 9.0pt;"> <span style="background: yellow; color: navy; mso-highlight: yellow;">-InputObject</span><span style="background: yellow; mso-highlight: yellow;"> <span style="color: orangered;">$serverConn</span></span>
<span style="color: navy;">-Database</span> <span style="color: blueviolet;">abc</span>
<span style="color: navy;">-BackupFile</span> <span style="color: darkred;">"L:\123\abc.bak" <o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This will run fine for command that take longer than 600 seconds.( Default value of StatementTimeout is 600, which caused the issue).</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
To tell Microsoft about this, I've logged a bug in their "Connect" program.</div>
<div class="MsoNormal">
<span style="color: #1f497d; font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;"><a href="https://connect.microsoft.com/SQLServer/feedback/details/805087/timeout-problem-in-backup-sqldatabase-cmdlet-in-sqlps-module">https://connect.microsoft.com/SQLServer/feedback/details/805087/timeout-problem-in-backup-sqldatabase-cmdlet-in-sqlps-module</a></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Please vote-up.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>PS</b>: If Power Shell is giving you some issues, don't worry. Invest time in learning and sharing. Overall, your time will be saved.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Tip : </b>I came around this workaround after using the best cmdlets of Power Shell : Get-Help, Get-Member.</div>
<div class="MsoNormal">
These are your base including Get-Command.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Cheers!</div>
<div class="MsoNormal">
<span lang="EN-US"><o:p></o:p></span></div>
<span style="color: blueviolet; font-family: "Lucida Console"; font-size: 9.0pt; mso-ansi-language: EN-IN; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-IN; mso-fareast-theme-font: minor-latin;"><br /></span>RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.comtag:blogger.com,1999:blog-4338513904406769767.post-55096329261005965412013-10-16T21:48:00.000-07:002013-10-16T21:48:56.570-07:00Why this blog ?<h1 class="quoteText" style="background-color: white; color: #181818; font-family: georgia, serif; font-size: 14px; font-weight: normal; line-height: 18px; margin: 0px 0px 15px; padding: 0px;">
“It is every man's obligation to put back into the world at least the equivalent of what he takes out of it.” - Albert Einstein</h1>
<div>
<br /></div>
<div>
There are two purposes of starting this one :</div>
<div>
<ul>
<li>Spread Knowledge</li>
<li>Personal Diary</li>
</ul>
<div>
<br /></div>
</div>
<div>
<b>Spread Knowledge</b></div>
<div>
<br /></div>
<div>
Every day in day out, you Google about something that you need and hopefully find what you need. In thanks to them, I'm also sharing my experiences. I want to share what I know so that it may save someone's time. And, the other benefit of sharing is the more you tell the more you get to know.</div>
<div>
<br /></div>
<div>
<b>Personal Diary</b></div>
<div>
<br /></div>
<div>
I started with PS from Jan 2013 (had some fun admiring it from July 2012), did learn many things, worked and then forgot! Since, I had to work on some other technologies as needed. But, PS lived in my heart. After some gap, I wanted to refresh. I did not have any notes. So, here I am preparing my notes. Maybe later they will prove beneficial to me.</div>
<div>
<br /></div>
<div>
Let's start sharing! </div>
RD18http://www.blogger.com/profile/17000874122823455882noreply@blogger.com