Practical PowerShell For SQL Server …
[Pages:1]Practical PowerShell For SQL Server Developers and DBAs
Download the latest version of this PowerShellTM wallchart and read the accompanying in-depth article from Simple-Talk at
Module Repositories
Store your modules--including sqlps--in one of these.
system-level $env:windir \System32\WindowsPowerShell\v1.0\Modules
user-level
$HOME \Documents\WindowsPowerShell\Modules
Profile Locations
Store aliases and commands that you always want available.
all users and all shells
$env:windir\System32\WindowsPowerShell\v1.0\profile.ps1
all users and
$env:windir\System32\WindowsPowerShell\v1.0\ Microsoft.PowerShell_profile.ps1
Microsoft.PowerShell shell
Modules vs. Snap-ins
Loading snap-ins for SQL support are deprecated, though available.
current user and all shells $HOME\Documents\WindowsPowerShell\profile.ps1
current user and
$HOME\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1
Microsoft.PowerShell shell
SQL Server 2008 & 2008 R2 SQL Server 2012
Cmdlet support SQLServerCmdletSnapin100 Provider support SQLServerProviderSnapin100
Rather, use the sqlps module instead.
Modules Snap-ins
Load Import-Module Add-PSSnapin
View items loaded Get-Module Get-PSSnapin
SQLServerCmdletSnapin110 SQLServerProviderSnapin110
View items available to load Get-Module -ListAvailable Get-PSSnapin -Registered
Determining Default Properties
Nodes reveal only a portion of their properties by default; see Key Commands for more.
Type specified in modulespecific formatting file?
Type specified in system formatting file?
$env:windir\system32\ WindowsPowerShell \v1.0\Modules\*. format.ps1xml $HOME\Documents\WindowsPowerShell\Modules\*.format.ps1xml $env:windir\system32\ WindowsPowerShell \v1.0\*.format.ps1xml
Load sqlps module:
DefaultDisplayPropertySet Use $object.PSStandardMembers.DefaultDisplayPropertySet;
defined?
otherwise, display all properties
Import-Module sqlps ?DisableNameChecking
Determining List or Table Output
Executing Queries
Invoke-Sqlcmd
[-ServerInstance ] [-Database ] [-EncryptConnection] [-Username ] [-Password ] [[-Query] ]
Copyright ? 2012 Michael Sorens 2012.07.02, Version 1.0.1
Published on Simple-
Nodes display either in PowerShell's list format or table format automatically, unless you
explicitly specify otherwise.
Format-verb cmdlet specified in pipeline? Type specified in modulespecific formatting file? Type specified in system formatting file? More than four properties?
... | Format-List ... ... | Format-Table ... $env:windir\system32\ WindowsPowerShell \v1.0\Modules\*. format.ps1xml $HOME\Documents\WindowsPowerShell\Modules\*.format.ps1xml $env:windir\system32\ WindowsPowerShell \v1.0\*.format.ps1xml
Use Format-List; otherwise, use Format-Table
[-QueryTimeout ] [-ConnectionTimeout ] [-ErrorLevel ] [-SeverityLevel ] [-MaxCharLength ] [-MaxBinaryLength ] [-AbortOnError] [-DedicatedAdministratorConnection]
Nodes in SQL Server Space
Description
Node
SQL Server data store root \
Network root
\SQL
Instances on selected machine \SQL\machine
Top-level instance objects
\SQL\machine\instance
Databases in selected instance \SQL\machine\instance\Databases
Default Properties Name, Root, Description MachineName InstanceName list of object names Name, Status, RecoveryModel, CompatLvl, Collation, Owner
Object Type (... prefix = Microsoft.SqlServer.Management.) ....PowerShell.Extensions.SqlServerProviderExtension ....PowerShell.Extensions.Machine ....Smo.Server
System.String ....Smo.Database
[-DisableVariables]
Top-level DB objects
\SQL\machine\instance\Databases\database
list of object names
System.String
[-DisableCommands]
- - - - Selected database nodes - - - -
[-HostName ]
Tables in selected database \SQL\machine\instance\Databases\database\Tables Schema, Name, Created
....Smo.Table
[-NewPassword ] [-Variable ] [-InputFile ]
Views in selected database Roles in selected database Triggers in selected database
\SQL\machine\instance\Databases\database\Views Schema, Name, Created \SQL\machine\instance\Databases\database\Roles Name \SQL\machine\instance\Databases\database\Triggers Name, Created
....Smo.View ....Smo.DatabaseRole ....Smo.Trigger
[-OutputSqlErrors] [-SuppressProviderConte xtWarning] [-IgnoreProviderContext] []
Top-level nodes: \SQL, \SQLPolicy, \SQLRegistration, \Utility, \DAC, \DataCollection, \IntegrationServices, \SQLAS
Database nodes: ApplicationRoles, Assemblies, AsymmetricKeys, Certificates, DatabaseAuditSpecifications, Defaults, ExtendedProperties, ExtendedStoredProcedures, FileGroups,
FullTextCatalogs, FullTextStopLists, LogFiles, PartitionFunctions, PartitionSchemes, PlanGuides, Roles, Rules, Schemas, ServiceBroker, StoredProcedures, SymmetricKeys, Synonyms, Tables, Triggers, UserDefinedAggregates, UserDefinedDataTypes, UserDefinedFunctions, UserDefinedTableTypes, UserDefinedTypes, Users, Views, XmlSchemaCollections
Executing Queries
Cmdlets implemented by SQL Server Provider
For brevity, sql aliases Invoke-Sqlcmd: New-Alias sql Invoke-Sqlcmd
Cmdlet
Canonical Other aliases
Description
Description Query with default context Query specifying server Query to interactive grid Output in table format Output in list format
Command sequence
sql -Query "select db_name()" sql -Query "select db_name()"-Server .\sqlexpress sql -Query "select * from clents"| Out-GridView sql "select * from clents" | Format-Table ?AutoSize sql "select * from clents" | Format-List
alias
Get-Location gl
Set-Location sl
Get-ChildItem gci
Get-Item
gi
Rename-Item rni
Remove-Item ri
pwd cd, chdir dir, ls
ren del, erase, rd, rm, rmdir
Gets current node Changes current node Lists the objects at current node Properties of current node Renames an object Removes an object
Key Commands
Source: Navigate SQL Server PowerShell Paths on MSDN
For brevity sandboxDB aliases SQLSERVER:\SQL\machine\instance\databases\sandbox and gci aliases Get-ChildItem.
SQL Server drives
Use New-PSDrive to create drive shortcuts.
Description
Go to root of SQL Server data store Go to root of DB objects
Command sequence Set-Location SQLSERVER:\ Set-Location SQLSERVER:\SQL
New-PSDrive ?Name DB ?PSProvider SQLSERVER ?Root SQLSERVER:\sql\localhost\SQLEXPRESS\Databases
List instance names on local machine List databases on selected instance List tables in selected database List tables with shortcut, default properties List subset of tables using SMO property
Get-ChildItem SQLSERVER:\SQL\machine Get-ChildItem SQLSERVER:\SQL\machine\instance\Databases gci SQLSERVER:\SQL\machine\instance\Databases\database\Tables gci sandboxDB:\Tables gci sandboxDB:\Tables | where {$_.Schema -eq "dbo"}
Get-PSDrive | ? { $_.Provider.Name -eq "SqlServer" } | select name,root
Name
Root
----
----
DB
SQLSERVER:\sql\localhost\SQLEXPRESS\Databases
SQLSERVER SQLSERVER:\
List tables,all properties
gci sandboxDB:\Tables | Format-Table ?Force *
References
Scripting Tables or Complete Database
For brevity sandboxDB aliases SQLSERVER:\SQL\ machine\instance\databases\sandbox,
Learning PowerShell, PowerShell in SQL Server 2012 / 2008, SQL Server PowerShell Help, Using SQL Server Cmdlets
% aliases ForEach-Object, and ? aliases Where-Object.
Description
Command sequence
Key Terms
All tables (output to console) All tables (output to file) All tables (separate batches) Selected tables Single table All tables with their indexes
Full database
gci sandboxDB:\Tables | % { $_.Script() }
gci sandboxDB:\Tables | % { $_.Script() } | Set-Content C:\create.sql
gci sandboxDB:\Tables | % { $_.Script() + "GO" }
gci sandboxDB:\Tables | ? { $_.name -match "big.*" } | % { $_.Script() }
(gci sandboxDB:\Tables | ? { $_.name -eq "xyz_table" }).Script()
gci sandboxDB:\Tables | % { $_.Script() + "GO" $_.Indexes | % { $_.Script() + "GO" }
} $myScriptFile = filepath $myDbInstance = Get-Item nodepath $mydb = $myDbInstance.Databases["dbName"] $mydb.Script() | Out-File $myScriptFile $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($myDbInstance) $scrp.Options.AppendToFile = $True $scrp.Options.FileName = $myScriptFile # Other options here... $scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$mydb.Tables)
SMO
.NET classes to create applications that manage SQL Server.
SQLPS
Module providing SQL Server support in PowerShell in the form of new cmdlets (Invoke-Sqlcmd and others) and a SQL Server provider.
SQL
Allows you to interact with the hierarchy of SQL Server
Server objects just as a native PowerShell file system provider
Provider allows you to interact with files. You can navigate
through SQL Server space using paths to nodes
analogously to paths to files.
SQLPSX CodePlex project introduced prior to sqlps to provide SQL Server support in PowerShell, SQLPSX still provides some functionality not covered by sqlps.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- install sql server powershell cmdlets
- install sql server powershell module
- sql server powershell module
- sql server powershell cmdlet
- install powershell sql server module
- download sql server powershell module
- powershell list sql server instances
- powershell query sql server database
- powershell execute sql server query
- sql server powershell cmdlets
- powershell test sql server connection
- powershell sql server connection examples