Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

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 SQLConnection .NET class

• 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 ...