-2

I'm trying to connect to my database using PowerShell but it's not find it even though I can see it using SSMS.

enter image description here

I'm trying to follow the example MS provides here

closed as off-topic by Tom V - Team Monica, John aka hot2use, mustaccio, Erik Darling, Mr.Brownstone Dec 19 '17 at 15:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Tom V - Team Monica, John aka hot2use, mustaccio, Erik Darling, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Hi, Welcome to the site. Please post your code as text instead of screenshots. Also, you're just not following the examples are you? The examples you linked to create a database and nowhere do they ask you to type localhost at the prompt. – Tom V - Team Monica Dec 19 '17 at 9:12
  • I was just testing what would happen if I typed localhost. The other lines are pretty much what the example is -_- – Batman Dec 20 '17 at 15:17
4

If you are on the SQL Server where the database resides, then you can follow these steps to connect to a database in PowerShell

First you have to load the SQL Server PowerShell Module. This is achieved with the following simple command:

PS C:\> sqlps
Microsoft (R) SQL Server (R) PowerShell
Version 12.0.5557.0
Copyright (c) 2014 Microsoft. All rights reserved.

PS SQLSERVER:\> 

You have now loaded the SQL Powershell Module and have been switched to the SQLSERVER drive.

Let's see what we can retrieve from the system:

PS SQLSERVER:\> get-childitem

Name            Root                           Description
----            ----                           -----------
DAC             SQLSERVER:\DAC                 SQL Server Data-Tier Application
                                               Component
DataCollection  SQLSERVER:\DataCollection      SQL Server Data Collection
SQLPolicy       SQLSERVER:\SQLPolicy           SQL Server Policy Management
Utility         SQLSERVER:\Utility             SQL Server Utility
SQLRegistration SQLSERVER:\SQLRegistration     SQL Server Registrations
SQL             SQLSERVER:\SQL                 SQL Server Database Engine
SSIS            SQLSERVER:\SSIS                SQL Server Integration Services
XEvent          SQLSERVER:\XEvent              SQL Server Extended Events
DatabaseXEvent  SQLSERVER:\DatabaseXEvent      SQL Server Extended Events
SQLAS           SQLSERVER:\SQLAS               SQL Server Analysis Services


PS SQLSERVER:\>

That looks promising.

We'll switch into the SQL Server Database Engine (SQL) and see what we have:

PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> get-childitem

MachineName
-----------
SQLSERVER-NAME

Ok. Let's connect to the SQLSERVER-NAME instance and carry on researching (I used localhost in my example, because I am on the server itself):

PS SQLSERVER:\SQL> cd localhost
PS SQLSERVER:\SQL\localhost> get-childitem

Instance Name
-------------
DEFAULT

Switch to the DEFAULT instance:

PS SQLSERVER:\SQL\localhost> cd DEFAULT
PS SQLSERVER:\SQL\localhost\DEFAULT>

And retrieve a list of instance objects:

PS SQLSERVER:\SQL\localhost\DEFAULT> get-childitem
Audits
AvailabilityGroups
BackupDevices
Credentials
CryptographicProviders
Databases
Endpoints
JobServer
Languages
LinkedServers
Logins
Mail
ResourceGovernor
Roles
ServerAuditSpecifications
SystemDataTypes
SystemMessages
Triggers
UserDefinedMessages
PS SQLSERVER:\SQL\localhost\DEFAULT>

Ok, switch into the databases...

PS SQLSERVER:\SQL\localhost\DEFAULT> cd Databases
PS SQLSERVER:\SQL\localhost\DEFAULT\Databases> get-childitem

Name                 Status          Containment Type Recovery Model CompatLvl Collation                      Owner
----                 ------          ---------------- -------------- --------- ---------                      -----
AdminDB2             Normal          None             Full                 120 Latin1_General_CS_AS           sa
AdventureWorks2012   Normal          None             Simple               110 SQL_Latin1_General_CP1_CI_AS   sa
File_Types           Normal          None             Full                 120 Latin1_General_CS_AS           sa
ReportServer         Normal          None             Full                 120 Latin1_General_CI_AS_KS_WS     sa
ReportServerTempDB   Normal          None             Simple               120 Latin1_General_CI_AS_KS_WS     sa
sqlnexus             Normal          None             Full                 120 Latin1_General_CS_AS           sa
StackExchange        Normal          None             Full                 120 Latin1_General_CS_AS           sa
TestDatabase2FS      Normal          None             Full                 120 Latin1_General_CS_AS           sa


PS SQLSERVER:\SQL\localhost\DEFAULT\Databases>

Once you are in the correct database, you can then set of relevant CMD-lets.

PS SQLSERVER:\SQL\localhost\DEFAULT\Databases> cd StackExchange
PS SQLSERVER:\SQL\localhost\DEFAULT\Databases\StackExchange> cd Tables
PS SQLSERVER:\SQL\localhost\DEFAULT\Databases\StackExchange\Tables> Get-ChildItem

Schema                       Name                           Created
------                       ----                           -------
dbo                          Blocking                       05.04.2017 16:17
dbo                          deadlock1                      10.01.2017 16:09
dbo                          deadlock2                      10.01.2017 16:09
dbo                          Issue_776                      29.05.2017 07:54
dbo                          Q184469_Customer               28.08.2017 10:29
dbo                          Q184469_DVD                    28.08.2017 10:29
dbo                          Q184469_DVD_Purchase           28.08.2017 10:29
dbo                          Q190497_Stats_Hash             09.11.2017 09:06
dbo                          Reference_Questions            06.01.2017 15:07
dbo                          Täble_Name                     18.12.2017 11:53
dbo                          tblTable                       30.08.2017 10:45
dbo                          TestJohnNessDBATemp            12.09.2017 11:58
DBT                          Application                    08.02.2017 07:59
DBT                          Database                       08.02.2017 08:02
DBT                          Instance                       08.02.2017 07:57
DBT                          Server                         08.02.2017 07:54
DBT                          User                           08.02.2017 08:00


PS SQLSERVER:\SQL\localhost\DEFAULT\Databases\StackExchange\Tables>

You can then carry on from here.

  • Thank you very much for this. I'm not sure why the examples provided by MS don't explain that you need to be in SQLPS. Very detailed explication. – Batman Dec 20 '17 at 15:39
  • @Batman sqlps.exe (which is not the SQL Server PowerShell module) is a weird, stuck-at-an-old-version partially-implemented version of PowerShell. IMHO no one should be using it. There is a slqps PowerShell module, but it has numerous problems, does not get any updates from Microsoft, and was superseded by the sqlserver module several years ago. Just use regular PowerShell and then import-module sqlserver; – alroc Dec 29 '17 at 2:43
-1

Just follow the simple steps as mention below

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

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"  


#This sets the connection to mixed-mode authentication 

$srv.ConnectionContext.LoginSecure=$false; 



#This sets the login name 
$srv.ConnectionContext.set_Login("userid"); 



#This sets the password 
$srv.ConnectionContext.set_Password("PaSSw0rd") 

we can retrieve the list of databases running on the SQL Server instance by adding the code below.

$srv.Databases | Select name

How to run i am also attaching a screen shot to you

enter image description here

Get-Credentials login through PowerShell

In PowerShell Type

>Get-Credential

enter image description here For further reference please read the article Connect to SQL Server via Windows PowerShell with SQL Server authentication

  • 2
    We normally ask people with questions to post code instead of screen shots, which aren't always available when working in a corporate environment. Could you consider adding code instead of screen shots in your answer? – John aka hot2use Dec 19 '17 at 9:40
  • @hot2use, I agree with you but sometimes it required for better understanding to OP. Because as per OP powershell i saw , he has committed some mistakes. – Md Haidar Ali Khan Dec 19 '17 at 9:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.