Tag: powershell

Windows Power Shell Script to run SQL Files in Folder against SQL Server Database

Problem Statement:-

At times, we may need to run SQL files present in a folder against SQL Server Database. Since there are no built in feature available as of now, here is our try with a powershell script.

Pre-requisites:-

  1. Save the (.bat) Batch script & (.ps1) PS Script in same folder in which all the SQL Files are stored.
  2. The Machine should have the SQL Server instance and Power shell tool.
  3. Ensure the parameters values are verified in (.bat) Batch file

Scenarios Covered:-

i) All key values are handled with parameters
ii) If database not exist it will throw error and comes out of PS
iii) If any script has issues/failed, it will throw error and comes out of PS
iv) If we want to run the sql scripts in subfolder as well, then “Includesubfolders” parameter set to 1
v) Batch file automatically retrieve the root path

PS Script:-

$Scriptpath  = $args[0]
$Server =  $args[1]
$database = $args[2]
$user= $args[3]
$pwd= $args[4]
$Includesubfolders=$args[5]

Function IsDBInstalled([string]$Server, [string]$database)
{

 $t=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database "master" -Query "select 1 from sys.databases where name='$database'" -OutputSqlErrors $true 
  if (!$t) {
            Write-Host "Failed to connect to [$database] database on [$Server]" -BackgroundColor darkred 
            Write-Error "Failed to connect to [$database] database on [$Server]" -ErrorAction Stop
  } else {
              
            write-host "[$database] Database exists in SQL Server [$Server]" -BackgroundColor blue -ForegroundColor black
  }
}

IsDBInstalled $Server $database

if($Includesubfolders -eq 1) {
$subscripts = Get-ChildItem $Scriptpath -recurse | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $subscripts)
    {   Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
        $tables=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database  $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
        write-host ($tables | Format-List | Out-String) 
        }
} else {
$scripts = Get-ChildItem $Scriptpath | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $scripts)
    {   Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
        $tables=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database  $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
        write-host ($tables | Format-List | Out-String) 
        }
}

Batch Script:-

@ECHO ON
SET root=%cd%
SET PSScript=%root%\RunSQLFiles.ps1
SET PowerShellDir=C:\Windows\System32\WindowsPowerShell\v1.0
CD /D "%PowerShellDir%"

SET path=%root%
SET "machine=sqlserversample45"
SET "db=sample"
SET "user=username"
SET "pwd=password"
SET "Includesubfolders=0"

Powershell -ExecutionPolicy Bypass -Command "& '%PSScript%' '%path%' '%machine%' '%db%' '%user%' '%pwd%' '%Includesubfolders%'"

Pause
EXIT /B

Output:-

If Database Not Exists:

If Database Exists:

If Any Script has Error:

If “Includesubfolders” parameters set to “1”

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Using Windows Power Shell to get the Server Disk Space Information

To find Drive Names, Total Space, Free Space and Free Space Percentage for the list of Servers specified in computers.txt file using Power Shell Script.

Prerequisites:-

  1. Save the computers.txt file and below PS script in the same folder
  2. User should have access to the servers mentioned in computers.txt file
  3. There should not be any empty space or new line without Servername in computers.txt file

PS Script:-

$path=Split-Path $MyInvocation.MyCommand.path 
$Computers = get-content "$path\computers.txt" 
foreach ($Computer in $Computers)  
{  
$Disks = Get-wmiobject  Win32_LogicalDisk -computername $Computer -ErrorAction SilentlyContinue -filter "DriveType= 3" 
$Servername = (Get-wmiobject  CIM_ComputerSystem -ComputerName $computer).Name
foreach ($objdisk in $Disks)  
{  
        
    $total=“{0:N3}” -f ($objDisk.Size/1GB)  
    $free= “{0:N3}” -f ($objDisk.FreeSpace/1GB)  
    $freePercent="{0:P2}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  
       
       Write-Host "Servername      :" $Servername  
       Write-Host "Drive Folder    :" $objDisk.DeviceID "Drive" 
       Write-Host "Total size (GB) :" $total 
       Write-Host "Free Space (GB) :" $free 
       Write-Host “Free Space (%)  :” $freePercent 
} 
} 

Output:-

To save the Output to Diskspace_Report.csv file, Use below PS script:-

$path=Split-Path $MyInvocation.MyCommand.path 
$Computers = get-content "$path\computers.txt" 
foreach ($Computer in $Computers)  
{  
$Disks = Get-wmiobject  Win32_LogicalDisk -computername $Computer -ErrorAction SilentlyContinue -filter "DriveType= 3" 
$Servername = (Get-wmiobject  CIM_ComputerSystem -ComputerName $computer).Name
foreach ($objdisk in $Disks)  
{  
        
    $total=“{0:N3}” -f ($objDisk.Size/1GB)  
    $free= “{0:N3}” -f ($objDisk.FreeSpace/1GB)  
    $freePercent="{0:P2}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  

        $out=New-Object PSObject 
        $out | Add-Member -MemberType NoteProperty -Name "Servername" -Value $Servername 
        $out | Add-Member -MemberType NoteProperty -Name "Drive" -Value $objDisk.DeviceID  
        $out | Add-Member -MemberType NoteProperty -Name "Total size (GB)" -Value $total 
        $out | Add-Member -MemberType NoteProperty -Name “Free Space (GB)” -Value $free 
        $out | Add-Member -MemberType NoteProperty -Name “Free Space (%)” -Value $freePercent 
        $out | Add-Member -MemberType NoteProperty -Name "Name " -Value $objdisk.volumename 
        $out | Add-Member -MemberType NoteProperty -Name "DriveType" -Value $objdisk.DriveType 
        $out | export-csv $path\Diskspace_Report.csv -NoTypeInformation -Append   
} 
}