Sql-server – PowerShell Script To Identify Databases

database-designpowershellsql server

I have enough PowerShell skills to get off the ground, but have none with SQL server. Can someone please point me in the right direction of how to use PowerShell to get information on remote SQL servers to identify what databases are running on them.

Thanks for any help.

Edit – Using SMO Code

I have tried to put this code together, but get the error: Cannot find type [Microsoft.SqlServer.Management.Smo.Server] make sure the assembly containing this type is loaded

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

Best Answer

You have two options, both of which will require SQL Server Management Studio to be installed on the host you are running the commands from:

  1. SQLPS
  2. SMO

I lean toward the latter because it includes a bit easier format to learn for basic task such as getting a list of objects in an instance.

With SMO you have to load the appropriate assemblies you want to work with. In most cases Microsoft.sqlserver.management.smo.server will be the most common one used.

With SQLPS you have to know what version of SQL Server you are working with. SQL Server 2008 R2 (and R1) you will add-pssnapin *sql* and SQL Server 2012 and beyond you will import-module SQLPS.

Now to the question at hand of getting a list of databases.

SQLPS method

dir SQLSERVER:\\SQL\ServerName\Default\Databases | select name

The above would be for a default named instance, you would change "default" to your instance name if it was a named instance.

SMO method

$srv = New-Object 'Microsoft.SqlServer.Management.SMO.Server' "myInstance"
$srv.Databases | select name

Now the above code can easily be wrapped into a function or for each loop that would allow you to easily pass in multiple server names.

Edit

Depending on what literature you read on PowerShell you will find that most folks that take time to write out a script, will go ahead and spend the time to make it a repeatable one. This is done either through a function (basic level for me) and then modules (more advanced that I have not touched yet).

So for your example code:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

Your foreach loop is not needed in order to output the list of databases. This is an object within SMO that contains multiple properties, one of which is name and PowerShell will simply output that in the default format, as shown below: enter image description here A difference to note between SMO and SQLPS here is that SQLPS would not output the system databases by default, SMO will.

If you look up blog post on PowerShell Profiles a common thing to do is add the assembly command to that so ever time you open your PowerShell command it is already available. I will then add functions that are basically commands I use often, and happen to have one for databases:

function Get-DBList ($server)
{
    $srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
    $srv.Databases | Select name, RecoveryModel, 
        @{Label="CompatibilityLevel";Expression={($_.CompatibilityLevel).ToString().Replace("Version", "")}}
} #end Get-DBList

You can modify this to your liking but if I call it using my local instance I get the output below. enter image description here

I will go ahead and let you know something that I found out the hard way. Using SMO you can connect to SQL Server 2000 up to SQL Server 2014, however, each version of SQL Server can vary on where particular properties are located. I had a learning conversation with Jonathan Kehayias on Twitter one afternoon learning this but I cannot recall what property I was looking for. In these situations the .NET class library on MSDN for SMO is your friend because it can help guide you to properties you are looking for.

Related Question