Chapter 13. Windows PowerShell and SMO
In the previous chapters, you learned how to connect and query SQL Server 2008 instances using the new features SQLPS and the SQL and SQLPolicy folder under the SQLSERVER: drive. In this chapter, you will use Windows PowerShell in conjunction with .NET class libraries to connect to SQL Server. You will also use SQL Server Management Objects (SMO) to connect to SQL Server and access the SQL Server–related objects, features, and functionalities. This chapter covers the alternative ways to connect to SQL Server.
The PowerShell and SMO portions of this chapter cover the following:
PowerShell and the
Working with SQL Server using SMO
Working with databases using SMO
Working with tables using SMO
Backup and Restore with SMO
PowerShell and the SQLConnection .NET Class
The .NET class
System.Data.SqlClient.SqlConnection represents an open connection to a SQL Server database. You can leverage this class via PowerShell to connect to SQL Server, retrieve data, and execute procedures.
The following example retrieves the SQL Server version number from the default instance:
#Let's set the location to the script folder C:\DBAScripts Set-Location C:\DBAScripts $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=PowerServer3;Database=master;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "Select @@version as SQLServerVersion" $SqlCmd.Connection ...