Powershell and JSON – Part 2

We have a couple of standard JSON document which requires a property value to be updated based on the system they are moving to. So we started looking for an option which tool or scripting language can the best. So, I looked to use Powershell. Here is an example of How a JSON document value […]

Powershell and JSON – Part 1

Get the List of JSON PowerShell commandlet from Get-Command Get-Command *JSON* Reading JSON Document $file=”G:\Dummy_Data\world_bank\test1.json” (Get-Content -Path $file -Raw)| ConvertFrom-Json Pass JSON as InputObject to ConvertFrom-JSON commandlet $jdata=Get-Content -Path $file -Raw $data=ConvertFrom-Json -InputObject $jdata $data Now Get the members, the properties, and methods, of objects. $jdata=Get-Content -Path $file -Raw $data=ConvertFrom-Json -InputObject $jdata $data |Get-Member Retrieve Property Value $jdata=Get-Content -Path […]

PowerShell SQL Connection Test

In an Environment where you have 100’s of SQL instances and you need to test the connectivity to each of the instance or you need to deploy a script on each of the instance after verifying the connectivity to the instance.You need to have some sort of script which will connect to each instance and […]

Powershell and SQL Server – 3- Script SQL Server Logins with SMO

The Script is to quickly script out Login from Single or Multiple instance cls $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) Write-Host “—Login Script of :”$server”—” -ForegroundColor Yellow foreach($login in $Logins) { $login.Name $LoginName=$login.Name $server.Logins[$LoginName].Script() } }

Script out Server – Database Level Permission for all users and from all databases

This is always an issue that all the permission are gone when ever databases are refreshed from a backup.The below script will script Server ,Database,Object and Database Role Level Permission for all Databases and all users. Create a  ServerList.txt Download the Script_Server_Database_Level_Permission.sql and Powershell Script and save it to local drive location. Replace the “Path” with correct […]

Powershell and SQL Server – 5- List Login Information from Multiple SQL Server Instance with Invoke-Sqlcmd

The script pull Login information from single or multiple SQL Instance cls if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from text files $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “|- SQL Server : ” $SqlServer -ForegroundColor Yellow try { $result=Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -Query “SELECT * FROM sys.syslogins” -QueryTimeout 0 […]

Powershell and SQL Server – 2- List SQL Server Service Name and Service Account with SMO

The Script pulls out SQL Server Service Name (Instance Name) and Service Account (Log on Account) $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “Server :”$SqlServer -ForegroundColor Yellow $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $server|SELECT ServiceName,ServiceAccount,Status,PhysicalMemory |ft -AutoSize }

Powershell and SQL Server – 2- List Databases and Mirroring Status from Multiple SQL Server Instance with SMO

The Script will list out all database configured for DBMirroring $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “Server :”$SqlServer -ForegroundColor Yellow $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $server.Databases |Where-Object{$_.IsMirroringEnabled -eq ‘True’}| select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable,MirroringStatus,IsMirroringEnabled | ft }

Powershell and SQL Server – 1- List Databases and Size from Multiple SQL Server Instance with SMO

It’s quick to pull Database List with Size,Database Space Usage,Index Space Usage and Free Space with in databases using SMO with Powersehll. $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “Server :”$SqlServer -ForegroundColor Yellow $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $server.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | ft } You can use SSMS Object explorer […]

Powershell and SQL Server – Server-Property – Edition-Version

The script loops through each of the sql instance and pulls SERVERPROPERTY information such as Version,Productversion,ProductLevel,Edition cls if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from text files $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { $result=Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -Query “SELECT SERVERPROPERTY(‘ServerName’) ServerName,substring(@@version,0,charindex(‘-‘,@@version)) Version, SERVERPROPERTY(‘productversion’) Productversion, SERVERPROPERTY(‘productlevel’) Productlevel, SERVERPROPERTY(‘edition’)EngineEdition” -QueryTimeout 0 $result […]

Powershell and SQL Server – 4- List Databases from Multiple SQL Server Instance with Invoke-Sqlcmd

Get the database list from Multiple SQL Instances.Create a Server.txt and populate it with SQL Instance Name and access the content of file in an array. To access the Server.txt content in an array and loop it for each server $SQLserverDB=@() #Array variable $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from […]

Powershell and SQL Server – 3- Access SQL Server Instance – List Databases and Properties with Invoke-Sqlcmd

List out database name from a SQL Instance if (-not (Get-Module SQLPS)) { Import-Module SQLPS } $SqlServerName=”LENOVO-PC\SQL2016″ $result=Invoke-Sqlcmd -ServerInstance $SqlServerName -Database master -Query “SELECT * FROM sys.sysdatabases” -QueryTimeout 0 foreach ($dbname in $result.name) { $dbname } or foreach ($dbname in $result) { $dbname.name } List Database properties for each database if (-not (Get-Module SQLPS)) {Import-Module […]

Powershell and SQL Server – 2- Connect to SQL Server Instance

There are different connection options for powershell scripts to connect to SQL Server Invoke-sqlcmd Windows Authentication Invoke-Sqlcmd SQL Authentication Invoke-Sqlcmd with User Login Authentication Prompt SQLConnection.connectionstring property SMO Connection Invoke-sqlcmd Windows Authentication if (-not (Get-Module SQLPS)) { Import-Module SQLPS } $result=Invoke-Sqlcmd -ServerInstance LENOVO-PC\SQL2016 -Database master -Query “select @@servername as ServerName” -QueryTimeout 0 $result Invoke-Sqlcmd SQL […]

Powershell and SQL Server – 1- Import SQLPS Module

Ways to connect to SQL Server using Powershell Microsoft recommend to use SQLPS Module to manage SQL Server.Import the SQLPS Module into Windows Powershell environment Check if SQLPS module is registered and available Get-Module Import SQLPS Module to Powershell console Import-Module SQLPS Ignore Warnings Ignore the Module Load if already loaded if (-not (Get-Module SQLPS)) […]