Archive

Archive for the ‘PowerShell’ Category

Setting up PowerShell Remoting using winrm quickconfig or Enable-PSRemoting fails

August 1, 2015 Leave a comment

 

PowerShell Remoting configuration will fail because the system is connected to a public network. The advice is to change the network from public to private, unfortunately this is not always possible because of WiFi connections.

 

image

 

Resolution

Enable-PSRemoting -SkipNetworkProfileCheck -Force

 

image

Categories: PowerShell

Import SQL Server Policies

February 15, 2015 Leave a comment

A PowerShell script to easily and quickly import policies into SLQ Server.

 

param ([string] $Sqlserver) Import-Module sqlps -DisableNameChecking $Connectionstring = "server='"+$Sqlserver+"';Trusted_Connection=true" $Conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection($Connectionstring) $PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($Conn) $PolicyFiles=Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\110\_MarkitBuild\Policies\*.*' -Include *.xml foreach ($File in $PolicyFiles.FullName) { $PolicyXmlPath =$File #"C:\Delete_Data_R2\MultipleFiles\Markit-Check-SQLServer2008R2-Build.XML" $xml = [System.Xml.XmlReader]::Create($PolicyXmlPath) $PolicyStore.ImportPolicy($xml,` [Microsoft.SqlServer.Management.Dmf.ImportPolicyEnabledState]::Unchanged, $true, $true)` |Select-Object Name,Enabled }

image

image

 

image

 

Invoking PowerShell script from T-SQL

 

declare @servername varchar(500) declare @vsql nvarchar(4000) declare @versionfolder varchar(4) set @servername=@@servername select @versionfolder=max(compatibility_level) from sys.databases set @vsql='"powershell.exe C:\PowershellScript\Apply_Policy.ps1 -Sqlserver ' +@servername +' -version '+@versionfolder+'"' exec master..xp_cmdshell @vsql

Categories: PowerShell

Search and Delete Files (Extension Search)

November 11, 2014 Leave a comment

I wrote this script to find file’s with a specific extension and take action such as delete.

$_SearchExtension=Read-Host "search file (extension *.trn,*.BAK etc ):" $_SourcePath=Read-Host "Folder location:" $_Action=Read-Host "Action (0=Print,1=Print and Delete):" $_fileex=$_SearchExtension Write-Host "" $_Fpath=$_SourcePath $_files = get-childitem -Path $_Fpath -filter $_fileex

-Recurse -ErrorAction SilentlyContinue

-Verbose |? {$_.PsIscontainer -eq $false } foreach($_f in $_files) { if($_Action -eq 0) { Write-Host "FileName : " $_f.fullname} elseif($_Action -eq 1) { Write-Host "FileName : " $_f.fullname remove-item $_f.fullname } }

image

Find large files on disk

November 11, 2014 Leave a comment

How to find large files on a disk ?

The script will search the large files as per defined file size. The script has 2 input parameters

param([string]$SearchPath,[String] $FileSize)

[string]$SearchPath : Pass the drive path , drive letter or leave it blank (example : C:\xyz\… , C:\,” “)

[String] $FileSize : Search file of size greater than (example : 1024GB or 1024MB)


param([string]$SearchPath,[String] $FileSize)
$CurrentDate=Get-Date
Write-Host `n "Search Start Time : "  $CurrentDate -ForegroundColor Yellow
    if($SearchPath -eq " ")
        {
            foreach ($drive in (Get-PSDrive -PSProvider FileSystem))
            {                
                $DriveName=$drive.Name+":\"
                if ((Test-Path -Path $DriveName) -eq "True")
                {Get-ChildItem $DriveName -Recurse -Force -ErrorAction Ignore | Where-Object {$_.Length -gt $FileSize}| `
                 SELECT Name,@{Name="FileSize[GB]";Expression={[math]::Round($_.Length / 1GB)}},LastWriteTIme `
                 -ErrorAction SilentlyContinue | Format-Table -AutoSize
                }
            }
        }
    else
        {        
            if ((Test-Path -Path $SearchPath) -eq "True")
                {Get-ChildItem $SearchPath -Recurse -Force -ErrorAction Ignore | Where-Object {$_.Length -gt $FileSize}| `
                 SELECT Name,@{Name="FileSize[GB]";Expression={[math]::Round($_.Length / 1GB)}},LastWriteTIme ` 
                 -ErrorAction SilentlyContinue | Format-Table -AutoSize
                }
        }
$CurrentDate=Get-Date
Write-Host `n "Search End Time : "  $CurrentDate -ForegroundColor Yellow

image

Start Powershell Application with “RunAs” Admin

December 24, 2010 1 comment

Check out how to start Windows Powershell Application with “RUNAS” administrator

PS C:\Users\sufian> start-process powershell_ise -verb runAs

Start-Stop SQL Server Services Using PowerShell

December 23, 2010 Leave a comment

Check status of  Running SQL Server Process using PowerShell

Stop SQL Server Process

Get-Service *SQL*
Get-Process SQL*
Stop-process 5096(Process id) -force

 



Start SQL Server Service