PowerShell Archives » SQLGeeksPro

Tag: PowerShell

Powershell Script to Create Folders

by Dev on

This article will help you with Powershell Script to Create Folders. Being DBA you may get into many situations when you need to create many folders.

Let me give a scenario. You have a SQL Instance of around 100 databases and need to create directory with the database names.

Powershell Script to Create Folders

Go to Start > Search and type “Powershell”. You can use any of the highlighted option.

Powershell Script to Create Folders

Here in this example I’ll be showing the example from first option. i.e. PowerShell ISE. Right Click > Run As Administrator

Powershell Script to Create Folders

The one liner code is as follows. Here I am creating a new folder named “folder” in the path ‘C:\BackupData\’
New-item -Path 'C:\BackupData\folder' -ItemType Directory

The following screen shot shows how it looks in the console.

Powershell Script to Create Folders

Now lets see how you can use this to create multiple folders.
First create a folder list in a notepad and save it in a notepad. In this example the file is “FolderNames.txt”
Powershell Script to Create Folders

$foldername= Get-Content -Path 'C:\Temp\Test\FolderNames.txt' #Provide the Path where the list is kept
$dirpath='C:\Temp\Test' #The Path where you need to create the folders
foreach ($folder in $foldername)
{
New-Item -ItemType Directory -Path $dirpath\$folder
}

Copy the code in the PowerShell ISE and click on the Play button to execute it. The folder names will be displayed in the bottom section as you can see in the below screen shot.
Powershell Script to Create Folders

Validate the path:
Powershell Script to Create Folders

Tagged as: PowerShell

{ Add a Comment }

Powershell Script to Validate SQL Servers

by Dev on

This article is about a Powershell script to Check SQL Server Database Status and Services remotely. This will help in validating SQL Server after Windows patching.

To put in another way, DBAs are responsible for validating SQL Servers after server maintenance. Usually DBAs need to do a sanity check of the SQL Server after planned or unplanned maintenance.

The most common checks while doing a SQL Server validation:

Validate SQL Services are up and running
SQL Databases are online
There is no error in the error log
Authentication protocol after the restart (Kerberos Or NTLM)

Now imagine if you have a large number of servers, how much time you need to spend on such activity. The below simple yet effective powershell script will save a lot of time.

Powershell script to Check SQL Server Database Status and Services:

###########################################################################################################
#Objective: To Check SQL Service status, Database status, Errors in Errorlog and Authentication protocol  #
#Author: Tridib Dev                                                                                       #
#Source:https://sqlgeekspro.com/                                                                          #
#Parameter: SQL Instance Name                                                                             #
###########################################################################################################
param ([parameter(Mandatory = $true)] $SQLServer)
        
Write-host 'SQL Service Status for: ' $SQLServer
Write-host '---------------------------------------------'
Write-host " "
Get-WmiObject -Class Win32_Service -ComputerName $SQLServer | Where-Object {$_.Name -like "*SQL*"} | Format-List -Property Displayname, state, startName
Import-Module SQLPS -DisableNameChecking
$q1 = "select name as DatabaseName, state_desc as Status from sys.databases where state_desc <> 'online'"
$q2 = @"
CREATE TABLE #ErrorLog(
   LogDate DATETIME,
   ErrorSource NVARCHAR(MAX),
   ErrorMessage NVARCHAR(MAX)
)

INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC xp_readerrorlog 0,1, "Error"
select LogDate, ErrorMessage from #ErrorLog 
drop table #ErrorLog
"@
$q3 = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid"

$r1 = Invoke-Sqlcmd -ServerInstance $SQLServer $q1
$r2 = Invoke-Sqlcmd -ServerInstance $SQLServer $q2
$r3 = Invoke-Sqlcmd -ServerInstance $SQLServer $q3

write-host 'Database status'
Write-host '-----------------'
Write-host " "
Write-host $r1.itemArray
Write-host '-----------------'
write-host 'ErrorLog:-'
Write-host '-----------------'
Write-host " "
Write-host $r2.itemArray
Write-host '-----------------'
Write-host " "
Write-host 'Authentication Type'
Write-host '-----------------'
Write-Host $r3.itemArray

Sample Output:

Powershell to Check SQL Database Status — SQL Service Status

Note: You can run the script in PowerShell ISE or download it from ValidateSQLServerThe output will be in console. I have written this script keeping it simple and have not used advanced modules so that you don’t have to install any modules. You just need SQLPS module.
Hope this will help in saving lot of your time in validating SQL Servers after any maintenance. You may also like Powershell Script to find SQL Instance Name for a Remote Server

Tagged as: PowerShell, Validate SQL Server

{ Add a Comment }

PowerShell Script to find SQL Instances on remote server

by Dev on

This article is about a PowerShell script to find sql instances on remote server. Not to mention the remote server can be standalone or Cluster.
When I got the requirement to develop the script, I thought it is not a big deal. Generally speaking, PowerShell will have some direct commands to do so and my script is few searches away.
But when I put my feet in, I got to know it was not as easy as I thought.
Especially the real challenge comes in when you have to list the SQL Instances for a SQL Cluster. The script should be intelligent enough to find out if the server is cluster or standalone and accordingly list the SQL Instances.

Note:The script should run from a server where FailOver Clustering Feature is installed.

PowerShell script to find sql instance name on remote server

Param(
    [Parameter(Mandatory=$true)]
     $ComputerName
)

if ((Get-WMIObject -Class MSCluster_ResourceGroup -ComputerName $ComputerName -Namespace root\mscluster -ErrorAction SilentlyContinue) -ne $null)
 {  
    Import-Module FailoverClusters
    get-clusterresource -Cluster $ComputerName  -ErrorAction SilentlyContinue|
    where-object {$_.ResourceType -like “SQL Server”} | 
    get-clusterparameter VirtualServerName,InstanceName | group-object ClusterObject | 
    select-object @{Name = “SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}} 
    } 
else {
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}
    foreach ($sql in $SQLInstances) {
       [PSCustomObject]@{
           ServerName = $sql.PSComputerName
           InstanceName = $sql
       }
   }  
   }

Not to mention, you can execute it from PowerShell ISE or from PowerShell Command prompt. Let’s see both examples.

Execute The Script Through Windows PowerShell ISE:

PowerShell Script to find SQL Instances on remote server Once you execute the script you’ll be prompted to provide the Server Name as you can see in the last line in above screen shot.
Sample OutPut:

Execute the Script from Windows PowerShell Command Prompt:

Only difference is to provide the server name while running the script as shown in the screen shot. Also the SQL Instance “PC01” is a cluster. Hence you don’t have to bother if the server is standalone or cluster. Hope this helps.
Let me know if you have any questions or comments.

Tagged as: PowerShell, SQL Instance

{ 2 Comments }