Using the SQL SQLSERVER: drive has four primary folders. 2008 R2. You must specify a computer name. You can specify either localhost or `(local`) for the local computer. You must always specify the instance name, even for default instances. For default instances, specify DEFAULT. The nodes after the instance name alternate between object classes (such as Database or View) and object names (such as AdventureWorks2008R2).2008R2 database in a default instance of the Database Engine on the local computer:
For more information about the SMO object model hierarchy, see SMO Object Model Diagram.
Object class nodes in a path are associated with a collection class in the associated object model. Object name nodes are associated with an object class in the associated object model, as in the following table.
Whenever you reference an instance of the Database Engine in a path, the SQL Server provider uses SMO to open a Windows Authentication connection to the instance. The connection is made using the credentials of the Windows account running the Windows PowerShell session. The SQL Server provider does not use SQL Server Authentication.
Windows PowerShell implements cmdlets to navigate provider hierarchies..
By default, Get-ChildItem does not list any system objects. Use the Force parameter to see system objects, such as the objects in the sys schema.
This example lists the local computer and any computer to which you have made an SMO or WMI connection:
This example lists the instances of the Database Engine on the local computer:.
The SQL Server provider is used to manage the objects in an instance of the Database Engine. It is not used to work with the data in databases. If you have navigated to a table or view, you cannot use the provider to select, insert, update, or delete data. Use the Invoke-Sqlcmd cmdlet to query or change data in tables and views from the Windows PowerShell environment. For more information, see Using the Invoke-Sqlcmd cmdlet.:. For example, if you are doing a lot of work in the AdventureWorks2008R2 database, you can create an AWDB: drive:
You can then use the AWDB: drive to shorten the path to AdventureWorks2008R2 objects, such as the Purchasing.Vendor table:
By default, the SQL Server provider uses the Windows account under which it is running to make a Windows Authentication connection to the Database Engine. To make a SQL Server Authentication connection, you must associate the SQL Server login credentials with a virtual drive, and then use the change directory command (cd) to connect to that drive. In Windows PowerShell, security credentials can only be associated with virtual drives.
This script creates a function named sqldrive that you can use to create a virtual drive that is associated with the specified SQL Server Authentication login and instance. }
You can then create a virtual drive named SQLAuth: by running this command:
The sqldrive function prompts you to enter the password for your login, masking the password as you type it in. Then, whenever you use the change directory command (cd) to connect to a path by using the SQLAuth: drive, all operations are performed by using the SQL Server Authentication login credentials that you supplied when you created the drive.
In addition to the SQL Server Management Object Model namespaces that are associated with the \SQL, \SQLPolicy, and \SQLRegistration folders, you can also use classes in the Microsoft.SqlServer.Management.Smo.Wmi namespace. The most common use of this namespace is to query and manage the state of the services that implement each instance of the Database Engine or a policy store.
This example shows how to use the ManagedComputer class to stop and start the service running a default instance of the Database Engine.
# Get a reference to the ManagedComputer class. cd SQLSERVER:\SQL\localhost $Wmi = (get-item .).ManagedComputer # Display the object properties. $Wmi # Get a reference to the default instance of the Database Engine. $DfltInstance = $Wmi.Services["MSSQLSERVER"] # Display the state of the service. $DfltInstance # Stop the service. $DfltInstance.Stop(); write-host "Stopped" # Refresh the cache and look at the state. $DfltInstance.Refresh(); $DfltInstance # Start the service again. $DfltInstance.Start(); write-host "Started"
Windows PowerShell tab-completion reduces the amount of typing you must do. introduce three system variables that you can use to control the amount of information presented by tab-completion and Get-ChildItem.
The following example sets all three variables and lists their settings: