dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Connect-DbaSqlServer

Creates an efficient SMO SQL Server object. This command is efficient because it initializes properties that do not cause enumeration by default. It also supports both Windows and SQL Server credentials and detects which alternative credentials.

By default, this command also sets the client to “dbatools PowerShell module – dbatools.io – custom connection” if you’re doing anything that requires profiling, you can look for this client name.

Alternatively, you can pass in whichever client name you’d like using the -ClientName parameter. There are a ton of other parameters for you to explore as well.

To execute SQL commands, you can use $server.ConnectionContext.ExecuteReader($sql) or $server.Databases[‘master’].ExecuteNonQuery($sql)

Screenshots

connect-dbaservername2

Examples

To create an SMO Server object that connects using Windows Authentication

Connect-DbaSqlServer -SqlServer sql2014

To create an SMO Server object that connects using alternative Windows credentials

$wincred = Get-Credential ad\sqladmin
Connect-DbaSqlServer -SqlServer sql2014 -Credential $wincred

To login to sql2014 as SQL login sqladmin.

$sqlcred = Get-Credential sqladmin
$server = Connect-DbaSqlServer -SqlServer sql2014 -Credential $sqlcred

To create an SMO Server object that connects using Windows Authentication and use the client name “mah connection”. So when you open up profiler or use extended events, you can search for “mah connection”.

$server = Connect-DbaSqlServer -SqlServer sql2014 -ClientName “mah connection”

To create an SMO Server object that connects to sql2014 using Windows Authentication, then set the packet size (this can also be done via -PacketSize) and other connection attributes.

$server = Connect-DbaSqlServer -SqlServer sql2014 -AppendConnectionString “Packet Size=4096;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;”

Get-Help

From PowerShell, execute Get-Help Connect-DbaSqlServer -Detailed for more information on this function.

Source Code

Want to see the source code? View Connect-DbaSqlServer.ps1 on GitHub
 

Related commands