Search code examples
powershellsqlps

Enumerate Databases Using SQL PowerShell

I'm using PowerShell 4.0 and the SQLPS plugin for working with SQL Server.

ForEach($Database in (Get-ChildItem -FORCE SQLSERVER:\SQL\localhost\DEFAULT\Databases))
{
    Write-Host $Database
}

This outputs a list of database I have locally such as:

[Test_1] [DBA] [Test_2]

However if I try to do something with the $Database like

ForEach($Database in (Get-ChildItem -FORCE SQLSERVER:\SQL\localhost\DEFAULT\Databases))
{
    Invoke-Sqlcmd -Database $Database -HostName WIN-MAINV98PDFA -Query "select db_name()" -ServerInstance LocalHost
}

I get an error:

WARNING: Using provider context. Server = localhost. Invoke-Sqlcmd : Cannot open database "[Test_1]" requested by the login. The login failed. Login failed for user 'WIN-MAINV98PDFA\admin'. At line:3 char:5 + Invoke-Sqlcmd -Database $Database -HostName WIN-MAINV98PDFA -Query "select d ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I think problem boils down to the difference between--the brackets.

Works:

Invoke-Sqlcmd -Database Test_1 -HostName WIN-MAINV98PDFA -Query "select db_name()" -ServerInstance LocalHost

and

Fails:

Invoke-Sqlcmd -Database [Test_1] -HostName WIN-MAINV98PDFA -Query "select db_name()" -ServerInstance LocalHost

This seems like something that should just work. What am I missing?

Solution

  • The object stored in the $Database variable is probably a complex type, and contains more information than just the database's name. Run this command, to see if there is a property that contains the database name without the square brackets around it:

    $Database | Select-Object -Property *;

    Assuming that there is something like a Name property, then pass in just the database's name by referencing that property.

    ForEach($Database in (Get-ChildItem -FORCE SQLSERVER:\SQL\localhost\DEFAULT\Databases))
{
    Invoke-Sqlcmd -Database $Database.Name -HostName WIN-MAINV98PDFA -Query "select db_name()" -ServerInstance LocalHost
}

    Here is an example, using a Windows Service instead of a Database object.

    Screenshot