Tag Archives: scripting

PowerShell Script – Detect installed SQL Server Version and switch to appropriate setup directory

Some time ago I wrote a blog post where I explained how to change SQL Server instance collation for installed SQL server instance (see “Changing SQL collation for deployed instance without reinstall (almost)“). That post contained some scripting bits to detect installed SQL Server version and navigate to appropriate setup directory to facilitate collation change process. Recently I had a bit of time to consolidate these bits of PowerShell into one script which detects installed SQL Server version and changes directory to appropriate setup folder. Here you have it:

Please follow and like us:
error0

SQL Server: How to attach all databases from specific folder

I recently had to change SQL Server instance collation using the steps I described in my earlier post. After this operation dozen of databases which were hosted on that instance become detached and I was able to see only set of instance’s system DBs. So I had to look for an option to attach all DB from folder via script as doing this manually for 20 or so databases a bit too much of an effort to me ūüôā

There is “Attach all the databases in one folder” script available in TechNet Script Center which was able to solve this problem for me. All you need it pass¬†DatabaseDir parameter to the function specifying folder with your DBs:

Attach-AllDatabasesInDir -DatabaseDir "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA"

Just in case I provide PS code of this function below in case you don’t want to grab the same from TechNet Script Center. All credit goes to¬†Ivan Josipovic.

#------------------------------------------------------------------------------------------------------

# Name: Attach-AllDatabasesInDir

# Description: This script will attach all the databases in a directory to the local SQL Instance

# Usage: Run the function with the -DatabaseDir parameter

# By: Ivan Josipovic, softlanding.ca

#------------------------------------------------------------------------------------------------------

#Do not modify below here

function Attach-AllDatabasesInDir{

param(

[string]$DatabaseDir = $(throw "-DatabaseDir `"C:\ExampleDir`" is required.")

)

#Load the SQL Assembly

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null

#Connect to the local SQL Server Instance, change the (local) parameter if needed

$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")

if ($server -eq $null){

Write-host -foreground red "Unable to connect to the SQL Server"

return -1

}

$items = get-childitem $DatabaseDir *.mdf

foreach ($item in $items){

[bool]$ErrorExists = $false

$Item.name

try {

$DBName = $server.DetachedDatabaseInfo($($item.fullname)).rows[0].value

}

catch {

Write-host -foregroundcolor red "File was not able to be read. It is most likely already mounted or in use by another application"

$ErrorExists = $true

}

if ($ErrorExists -eq $false){

foreach ($db in $server.databases){

if ($db.name.Equals($DBName)){

write-host -foreground red "This database already exists on the server"

$ErrorExists = $true

}

}

if ($ErrorExists -eq $false){

$DbLocation = new-object System.Collections.Specialized.StringCollection

$DbLocation.Add($item.fullname)

$attach = $server.AttachDatabase($DBName, $DbLocation)

}

}

}

return

}

#------------------------------------------------------------------------------------------------------

Attach-AllDatabasesInDir -DatabaseDir "D:\Data\SQLDat\MOSSData"
Please follow and like us:
error0

How to do a quick check of IIS version

Just a brief note on checking IIS version installed on the server. In case you doing a lot of support or scripting you should appreciate CLI way of doing this which doesn’t imply giving extensive instructions of getting the same information from GUI, which no matter how detailed will always be more error prone.

So to get IIS version you may just execute the following in PS window:

[System.Diagnostics.FileVersionInfo]::GetVersionInfo("$env:SystemRoot\system32\inetsrv\InetMgr.exe").ProductVersion

Sample output from Windows Server 2016 TP5 (it returns 10.0.14300.1000):

Windows Server 2016 TP5 IIS version

Note: If this command doesn’t work but you pretty sure that IIS is installed on your box, then most likely¬†IIS Management Console was not installed, and modern Windows administrator/IT pro should be able to quickly check this by issuing this command (see related question on stackowerflow.com):

Get-WindowsFeature *Web*

And if it is missing just quickly add this via the following command:

Add-WindowsFeature Web-Mgmt-Console

Please follow and like us:
error0

Checking for Windows update presence via command line

Just a quick note on how to check if some specific Windows update is installed on the system via command prompt. I thought I already took a note of this, but was not able to find existing post on this. While investigating an issue or doing troubleshooting something more often than not you may need to make sure if some specific update is present in the system or not. And not always you may want to wade through GUI to check this. Let’s say you may want to automate this process or do some mass discovery by running script on multiple machines.

Option 1. Query WMI (Windows Management Instrumentation) namespace.

1-A. Using wmic.exe (powerful, user-friendly CLI to the WMI namespace):

wmic qfe get hotfixid | find "KB99999"

wmic qfe | find "KB99999"

1-B. Using PowerShell:

Get-WmiObject -query 'select * from win32_quickfixengineering' | foreach {$_.hotfixid}

Option 2. Using PS commandlet get-hotfix which was introduced in PS 2.0:

get-hotfix -id KB974332

So this should be enough for most of the cases.

Please follow and like us:
error0