#PSTip List all fixed disk drives visible to SQL Server instance

Note: This tip requires PowerShell 2.0 or above.

When automating SQL server database creation in PowerShell, we may want to give the end user an option to select from a list of available fixed disk drives and let the user select the drive that should be used for database file creation, etc. It is preferred here to see what drives can be seen by the SQL server instance. We can do this using the EnumAvailableMedia() method in SQL SMO. A variant of this method takes MediaType as an argument. Within this enumeration, a value ‘2’ represents fixed disk.

Let us see how to use this in PowerShell:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$server.EnumAvailableMedia(2)

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts

%d bloggers like this: