Connect to SQL Server 2012 using PowerShell

Connect to SQL Server 2012 using PowerShell

Step by Step Process to query a table using SQL Server PowerShell

1)  Open SQLPS.exe located in

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn

                    it will display PS SQLSERVER:\>

2)  Now goto SQL 

   by typing    PS SQLSERVER:\>set-location SQL   enter

               it will become

                PS SQLSERVER:\SQL>

3) Get Machine Name:

       
PS SQLSERVER:\SQL>dir

       Machine-Name

4)    Now goto MachineName (got in step 3)

          
PS SQLSERVER:\SQL>set-location Machine-Name

5)  Now get list of sql server instances  running in that machine

PS SQLSERVER:\SQL\machine-name>dir

        

Instance Name
-------------
SQLEXPRESS2012

6)  now get list of databases 

connect to SQL Server Instance
PS SQLSERVER:\SQL\machine-name>set-location SQLEXPRESS2012

Step 7) Get List of Objects in SQL-Server Instance

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012>dir

Audits
AvailabilityGroups
BackupDevices
Credentials
CryptographicProviders
Databases
Endpoints
JobServer
Languages
LinkedServers
Logins
Mail
ResourceGovernor
Roles
ServerAuditSpecifications
SystemDataTypes
SystemMessages
Triggers
UserDefinedMessages

Step 8) Get List of Databases;

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012>set-location databases;

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases>dir

Name                 Status          Containment Type Recovery Model CompatLvl Collation                      Owner
----                 ------          ---------------- -------------- --------- ---------                      -----
AdventureWorks   Normal, AutoClo None             Simple               110 SQL_Latin1_General_CP1_CI_AS   sa
                     sed                                                                                      i

BooksList            Normal, AutoClo None             Simple               110 Latin1_General_CI_AI           sa
                                                                                                                 i
EntLibQuickStarts    Normal, AutoClo None             Simple               110 SQL_Latin1_General_CP1_CI_AS   sa
                     sed
Northwind            Normal, AutoClo None             Simple               110 Latin1_General_CI_AI           sa

Step 9)  Connect to Specific database;

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases>set-location Adventureworks


PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases\Adventureworks>

step 10)   Get Connected DB Name

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases\Adventureworks> invoke-sqlcmd -query "select db_name()"
 
Column1
-------
AdventureWorks


Step 11) Issue a select statement to a Table/view

PS SQLSERVER:\sql\machine-name\sqlexpress2012\databases\AdventureWorks> invoke-sqlcmd -query "select count(*) from sales.store"
WARNING: Using provider context. Server = MACHINE-NAME\SQLEXPRESS2012, Database = AdventureWorks2012.

                                                                                                                                Column1
                                                                                                                                -------
                                                                                                                                    701


Note:  you can do all database operations using SQL SERVER POWERSHELL



Step 12)   One Step Process to connect to Database

If you already know your machine name,sql server instance and database name. Here is the Procedure

PS SQLSERVER:\>set-location sql\Machine-Name\SQL Instance Name\databases\adventureworks

PS SQLSERVER:\sql\machine-name\sqlexpress\databases\adventureworks>

Then issue SQL command here


PS SQLSERVER:\sql\machine-name\sqlexpress\databases\adventureworks>invoke-sqlcmd -query "select count(*) from sales.store"

Output:
WARNING: Using provider context. Server = machine-name\SQLEXPRESS2012, Database = AdventureWorks2012.

                                                                                                                                Column1
                                                                                                                                -------
                                                                                                                                    701


Happy coding ....

Tags:Step by Step process to connect to Database using PowerShell in SQLSERVER,Set-Location in PowerShell,Invoke-sqlcmd in Powershell,SQL Server Powershell command Invoke-sqlcmd,
SQL Server Powershell command Set-Location,Query Database using SQL SERVER PowerShell,
Query Table using SQL-Server PowerShell,using SQL Server Power Shell.