Não é possível listar bancos de dados SQL como diretórios com o PowerShell

Question

Estou tentando listar bancos de dados com o PowerShell. Eu sou capaz de usar o cmdlet Inkoke-Sqcmd, mas não consigo listar bancos de dados com este comando:

dir SQLSERVER:\SQL\myserver\sqlinstance\Databases

Get-ChildItem : No se encuentra la ruta de acceso 'SQLSERVER:\SQL\serverr2\serverr2\Databases' porque no existe.
En línea: 1 Carácter: 4
+ dir <<<<  SQLSERVER:\SQL\serverr2\serverr2\Databases
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\serverr2\serverr2\Databases:String) [Get-ChildItem], ItemNo
   tFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Eu já testei isso no meu computador local, mas falhei no meu servidor. Há algo faltando na minha instalação? Eu tenho o Windows Server 2008 R2, o SQL Server 2008 R2, o PowerShell 2.0

Editar: O cmdlet Get-Host mostra essas informações:

Name             : ConsoleHost
Version          : 2.0
InstanceId       : c1976472-19c0-439e-a4f6-debe59a18616
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : es-MX
CurrentUICulture : es-ES
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

Também descobri que minha instância do SQL Server é o Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) e não o SQL Server 2008 R2 como eu pensava

    

Answer 1

Can't list SQL databases like directories with PowerShell

Certifique-se de carregar os snap-ins do SQL Server (Add-PSSnapin) para suportar os comandos apropriados, etc. Veja abaixo exemplos da sintaxe com instância padrão ou instância nomeada do SQL Server e onde inserir esse detalhe nos comandos do PS.

Nota importante: A serverr2\serverr2 na sua lógica do PS pode estar incorreta, pode ser a instância padrão em que você usa DIR SQLSERVER:\SQL\Serverr2\Databases apenas. Espelhe essa parte para corresponder ao tipo de conexão com o mecanismo de banco de dados do SSMS, talvez da sua instância do SQL Server.

Então, duas possibilidades para tentar com base no seu erro ( veja abaixo exemplos de como Add-PSSnapin *sql* , se necessário, em primeiro lugar ):

• Instância padrão: DIR SQLSERVER:\SQL\Serverr2\Databases | Select Name
• Instância nomeada: DIR SQLSERVER:\SQL\Serverr2\Serverr2\Databases | Select Name

Exemplo de instância nomeada do SQL Server

Add-PSSnapin *sql*
DIR SQLSERVER:\SQL\<SQLServerName>\<InstanceName>\Databases | Select Name

Exemplo de instância padrão do SQL Server

Add-PSSnapin *sql*
DIR SQLSERVER:\SQL\<SQLServerName>\Databases | Select Name

Leitura adicional e recursos

• Adicionar-PSSnapin
• Selecionar objeto

Diferença entre a instância padrão e a instância nomeada no SQL Server

What are difference between Default instance and Named instance in SQL Server?

A SQL Server installation is referred to as an instance. Up to and including SQL Server 7.0, only one installation of SQL Server was possible on a server, but that restriction didn’t suit a number of deployment scenarios that customers required, including high-availability and consolidation.

With the release of SQL Server 2000, multiple installations of SQL Server were possible on a single server and were known as SQL Server instances. SQL Server 2008 continues with this model and with very few changes. A default instance has much the same profile that SQL Server installations have had in past; you install SQL Server and then connect using the computer name of the server. Your Windows Server can only have one computer name, so you can only use it to connect to one SQL Server instance. This is called the default instance.

If you install additional instances of SQL Server, these are referred to as named instances. You connect to them using the format. For example, if you have a server named PLUTO and you install a named instance named SQL1, you would connect to that instance using PLUTOSQL1. If you installed another named instance called SQL2, you would connect using PLUTO SQL2. If you had a default instance installed, you would connect using PLUTO.

Each instance is completely independent of any other instance and has its own set of services, databases, and configuration settings. All the components of a single instance are managed together, and service packs and patches are applied to all components within an instance.

During the installation process you have to specify a unique instance ID, which is used to define the directory structure, registry structure, and service names for that instance. This is new in SQL Server 2008. By default, the instance name that you specified is used as the instance ID. For the default instance, MSSQLSERVER is used as the instance ID.

Conforme Mr_LinDowsMac . . .

I workaround like this: $databases = (DIR SQLSERVER:\SQL\serverr2 | SELECT Databases -ExpandProperty Databases | SELECT Name,Owner,Urn). Since I can't use WHERE, I just use $databases = $databases -match "SERVERR2\SERVERR2" to filter to that specific instance. – Mr_LinDowsMac