In Chapter 10, you learned about the most common maintenance tasks that you'll be called upon to perform as a database administrator. Many of those tasks are routine and also tedious. They are great candidates for automation, and one way to automate is through scripting.
You have two scripting environments at your disposal. One is SQLCMD, a command-line utility that has been around since the introduction of SQL Server 2005. The other is PowerShell, a newer and more expressive scripting environment that addresses the needs of DBAs, system administrators, and even power users.
SQLCMD is a Win32 command prompt utility that allows the interactive execution of Transact-SQL statements or scripts. The SQLCMD utility supersedes osql and isql from older releases of SQL Server.
You can use SQLCMD interactively or to execute scripts and jobs that you create once and run multiple times. Interactive mode allows you to type in Transact-SQL commands and get results. For example, the following process walks you through a brief, interactive session:
Open a command prompt: Start
In the command prompt, enter sqlcmd. Press Enter.
SQLCMDwill begin, and you will receive asqlcmdprompt.Note
If you are running SQL Server in Vista, Windows 7, or Windows Server 2008, you may need to run the command prompt as an administrator (right-click the program icon and select "Run as administrator") to ensure the proper access rights to run
SQLCMD.Connect to a database. For example, enter the following to connect to the AdventureWorks database:
use Adventureworks;goThe
gocommand is important. Use it to tellSQLCMDto execute the command or batch of commands that you have just entered. Be sure to end each individual Transact-SQL command with a semicolon (;), as shown in the example.You will see the "Changed database context to 'AdventureWorks'" message.
Then enter another Transact-SQL command. For example:
sp_who2;go
If you've entered the sp_who2 command as shown here, you will see the result in Figure 11-1.
Users can execute SQLCMD scripts/jobs either by specifying a single Transact-SQL statement to execute or by pointing the utility to a text file that contains Transact-SQL statements to execute. (Figure 11-2 shows an example of such a text file.) You would normally direct the output to a text file, but you can also display the output at the command prompt, as in Figure 11-3.
To execute a script in batch mode, invoke SQLCMD, and pass in the script name using the -i command-line option. For example, to execute a script named sample_solcmd.txt, you would invoke SQLCMD as follows:
sqlcmd -iC:some_directorysample_sqlcmd.txtFigure 11-3 shows such an invocation. The -v option in the figure restricts the output to only the first ten rows.
For editing and testing your SQLCMD scripts, you can use SQL Server Management Studio's Query Editor and place the Query Editor in SQLCMD mode. Once you open a new query in Management Studio, select Query
Once in SQLCMD mode, you can write and test your script. In the following script, we show how to perform some simple operations such as setting a database and executing a simple select statement. We also demonstrate how to use variables (using the :setvar keyword). Figure 11-5 shows the script's execution in Management Studio.
-- set the server:setvar myservername localhostPrint '$(myservername)'-- Connect to ServerDefault:connect $(myservername)-- set the database:setvar mydatabase AdventureworksPrint '$(mydatabase)'
use $(mydatabase);go-- Set Local scripting variable MyTable:setvar TestTable Person.Contact-- set the topcount variable:setvar RowCount 10-- select top number of records from TestTable based on RowCount variableselect top $(RowCount) firstname, lastname from $(TestTable)go
The SQLCMD utility is a powerful scripting environment allowing you to execute a full range of administrative tasks and create sophisticated flexible scripts. In addition to Transact-SQL support, SQLCMD supports a predefined set of scripting variables (for example, SQLCMDUSER, SQLCMDPASSWORD, and SQLCMDSERVER) and has a set of commands (for example, editing commands and output commands) that allow you to easily create powerful scripts without programming.
You can build your script files using a text editor such as Notepad. You can also build them using SQL Server Management Studio. When you get a set of commands in Management Studio that does what you need, you can create a script by saving the contents of the query window to a script file.
Creating scripts as we've shown in this section is a powerful way to leverage your time. Once you script a task, you can perform that task reliably and quickly, at a moment's notice, and without having to rethink everything. Scripting is a huge time-saver for DBAs.
The following example shows how to use SQLCMD for the routine task of creating a full backup of a database:
Create a
.sqlfile to contain the script. In this example, we'll use the namec:userscarmentdesktopSQLCMD_backup.sql.Place the following commands into the file. Notice the use of
SQLCMDvariables. The syntax is$(<variable name>), so for example on the third line, we're using a variable namedmyservernamethat we will pass into the script and then print the value as part of this script.-- sample SQLCMD file to backup a database-- set the serverPrint '$(myservername)'-- Connect to ServerDefault:connect $(myservername)-- set the databasePrint '$(mydatabase)'use master;goPrint '$(backupfile)'BACKUP DATABASE [$(mydatabase)] TO DISK='$(backupfile)'GoSave the file.
Then execute the script by invoking it from
SQLCMD. The following code snippet is one way to do that. Remember that you defined a series of variables in the script file. When you execute the script, you need to pass in the values for the variables. The way to do that is with the-vcommand-line option. So, for each variable that is used in the script (myservername,mydatabase, andbackupfile), you need a corresponding-vcommand-line option:sqlcmd -v myservername="localhost" -v backupfile="c:sqlcmd_advworks.bak" -vmydatabase="Adventureworks" -i c:userscarmentdesktopsqlcmd_backup.sqlYou should see results similar to those in Figure 11-6.
Management Studio provides the ability to generate T-SQL scripts for just about any database operation. As an example, let's look at how to create a backup script for a database that you can later use as a SQLCMD script. A backup is a simple matter of navigating to the database in Management Studio, right-clicking the database, selecting Tasks, and then selecting Back Up, as shown in Figure 11-7.
The next step is to pick the backup options. Having selected the options, you can script the Transact-SQL that represents the backup operation you have selected. Do that by clicking the Script button, as shown in Figure 11-8. The resulting Transact-SQL code is as follows:
BACKUP DATABASE [AdventureWorks] TO DISK = N'c:sqlcmd_advworks.bak'WITH NOFORMAT, NOINIT,NAME = N'AdventureWorks-Full Database Backup',SKIP, NOREWIND, NOUNLOAD, STATS = 10
You can now use this code as part of a script that you invoke from SQLCMD. Generating scripts like this is a great way to produce the T-SQL you want to use as part of your automated database maintenance tasks. Perform a task once in Management Studio, then create a script, and then generalize the script by using the features of SQLCMD such as variables and parameters that we talked about earlier in this chapter.
PowerShell is a new command-line shell and scripting language designed with significant improvements in power and functionality when compared with SQLCMD. The current version of PowerShell is 2.0, and it comes as part of Windows 7 and Windows Server 2008 R2. If you have PowerShell 1.0, you can upgrade to version 2.0 by downloading PowerShell 2.0 from the Microsoft download site. The examples I use in this section will work with PowerShell 1.0 or 2.0, but I recommend upgrading to version 2.0 to take advantage of the advanced features and additional capabilities.
SQL Server 2008 provides a PowerShell utility (called sqlps.exe) that enables you to easily access SQL Server instances and SMO objects and to evaluate policies within the PowerShell environment. These powerful capabilities in PowerShell allow you to create robust scripts for performing a wide array of database activities interactively or as a script.
PowerShell offers a variety of ways to automate tasks:
Cmdlets, which are simple, single-function command-line tools built into the shell
Scripts, which are combinations of cmdlets and logic
Access and invocation of standard .NET classes
PowerShell is built on top of the .NET common language runtime (CLR) and the .NET Framework. To use the SQL Server features of PowerShell, you need to install the Basic management tools. If you have already installed SQL Server, you can see whether the Basic management tools are installed by running the "Installed SQL Server features discovery" report. Go to the SQL Server Installation Center by selecting Start
SQL Server 2008 Setup installs the following PowerShell components when you select either the client software or the Database Services nodes:
Windows PowerShell 1.0, if PowerShell is not already present on your computer. (Note: Windows Server 2008 R2 and Windows 7 come with PowerShell 2.0.)
The SQL Server PowerShell snap-ins. The snap-ins are DLLs that implement two types of PowerShell support for SQL Server:
A set of SQL Server PowerShell cmdlets. Cmdlets are scripts that implement a specific action. For example, Invoke-Sqlcmd runs a Transact-SQL or XQuery script that can also be run by using the
sqlcmdutility, and Invoke-PolicyEvaluation reports whether SQL Server objects comply with policy-based management policies. You will see an example of the Invoke-Sqlcmd cmdlet later in this section.A SQL Server PowerShell provider. The provider lets you navigate the hierarchy of SQL Server objects using a path similar to a file system path. Each object is associated with a class from the SQL Server Management object models. You can use the methods and properties of the class to perform work on the objects. In addition, where you are in the path gives the commands that you issue context. For example, if you
cdto the AdventureWorks database location, then commands issued are in the context of the AdventureWorks database. The example later in this section will demonstrate this point.
The
sqlpsutility that is used to run PowerShell sessions that include the SQL Server snap-ins:It interactively runs Windows and SQL Server PowerShell commands.
It runs Windows Server and SQL Server PowerShell script files and cmdlets.
It uses the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.
sqlpsis a utility that creates a PowerShell environment and then loads and registers the SQL Server PowerShell snap-ins (mentioned earlier).
You can run PowerShell interactively. Begin by opening a command prompt window. At the command prompt, type powershell. If PowerShell is installed properly, you will see your normal command prompt preceded by PS, meaning that you are in the interactive PowerShell window. See Figure 11-11 for an example of how the command prompt will look (notice that the PowerShell version is V2 or PowerShell 2.0 since our example is in Windows 7).
To run the SQL Server provider in PowerShell, you need to enter the command sqlps in the Windows PowerShell window. Figure 11-12 shows the result of doing that. Notice now the prompt indicates that you are in PS SQLSERVER, or the SQL Server PowerShell.
You can start also start PowerShell sessions from SQL Server Management Studio by right-clicking objects in Object Explorer and selecting Start PowerShell. SQL Server Management Studio then starts a PowerShell session in which the SQL Server PowerShell snap-ins have been loaded and registered. The path for the session is preset to the location of the object you right-clicked in Object Explorer. For example, if you right-click the AdventureWorks database object in Object Explorer and select Start PowerShell, the PowerShell path is set to the following:
SQLSERVER:SQLComputerInstanceDatabasesAdventureWorks>
See Figure 11-13 for an illustration of the SQL Server PowerShell window started from Management Studio.
Before you start running commands and scripts in PowerShell, it is important to understand the execution policy that is set. The execution policy determines the conditions under which scripts are run, even the ones you write yourself. By default, PowerShell uses a default policy of Restricted, which means that no scripts will run.
The ability to run scripts is obviously desirable. You'll therefore want to change the execution policy to something more useful. A good choice is the RemoteSigned policy, which allows you to run local scripts without restriction but requires that scripts executed from the Internet be signed by a trusted publisher. Use the set-executionpolicy command to specify the RemoteSigned policy. For example:
set-executionpolicy remotesignedDo be aware that in order to change the execution policy, the user executing the set-executionpolicy command must have the ability to write to the registry key KEY_LOCAL_MACHINESOFTWAREMicrosoftPowerShell1ShellIdsMicrosoft.PowerShell (likely the administrator).
Once you have changed the policy to the desired state, you can check it at any time with this command:
get-executionpolicyFigure 11-14 shows some output from this command (in this example, ours is set to RemoteSigned).
With the SQL Server snap-ins, you can create PowerShell scripts that use and manipulate SQL Server objects. As a simple example, we have created a file called PS_sample.ps1:
## A simple example to demonstrate running a PS script## set context in our SQL server treecd SQLSERVER:sqlcarment8710-pcdefaultdatabasesadventureworks# see the objects at this level - CONTEXT IS CRITICALDir# Issue a simple query using one of the SQL Server cmdletsInvoke-Sqlcmd "select top(10) firstname, lastname from Person.Contact"# message....Write-Host "Sample Script Finished!"
Now you can execute your script in the PowerShell environment by simply typing in the script filename. You get the result in Figure 11-15, which is a directory listing of the SQL Server objects in the AdventureWorks database followed by the SQL select statement that will return the first ten rows from the Contact table.
Notice that in order to execute the script, you needed to use the explicit path, which is fine. However, as an alternative, you could navigate to the directory in which the .ps1 script lives. Figure 11-16 shows how to do that by using the cd command to navigate to the directory where the script lives and then executing the PowerShell script from there.
There are a few things to point out here in the execution of this script:
Just like you would refer to a disk drive as
C:, you refer to the SQL Server object structure asSQLSERVER:. You can see this use ofSQLSERVER:as the root of an object structure path in the first bit of output from the script.Without an absolute or relative path, PowerShell will not execute a script. In this case, we used the relative path of
./, which refers to the current directory.Context is critical when writing a script. Make sure to be aware of it. For example, within the test script, we used the
Dircommand, which gets all the child items (it is shorthand for Get-ChildItem). Without an explicit path, theDircommand will start from the current path, which will be wherever you are when you execute the script.
Since a lot of the tasks that you perform as a DBA are repetitive and do not require human interaction, PowerShell can be run in batch mode (from the command line). As you discover additional tasks that you would like to automate, PowerShell is the perfect environment for creating scripts and cmdlets to allow you to build a rich environment to manage your SQL Server (and other systems). The key to automation is to be able to run PowerShell scripts from the command line. Once you are able to do that, you can execute PowerShell scripts from the following:
The SQL Server Agent
The Windows Scheduler
Maintenance plans (covered later in this chapter)
The syntax for running PowerShell scripts in batch mode from the command line is pretty simple. Here is a sample of the basic syntax:
C:> powershell -NoExit -Command "C:UserscarmentDesktopPS_Sample.ps1"There are three parts to this command:
The command invokes
powershell.exe, the Windows PowerShell executable.The
-NoExitparameter tells the PowerShell console to remain open after the script finishes. If you don't use this option, the console window will close the moment the script finishes, meaning you won't have the chance to view any data that gets displayed to the screen.It contains the path to the script file, in this case
C:UserscarmentDesktopPS_Sample.ps1.
It is possible to run more than just script files; you can run PowerShell commands from the command line. For example, typing the following in the Run dialog box not only starts SQL Server PowerShell (SQLPS) but also causes it to run the Invoke-Sqlcmd as follows (notice that without context, we used the AdventureWorks database qualifier in the table name definition):
C:> sqlps Invoke-Sqlcmd 'select top(10) firstname,lastname from Adventureworks.Person.Contact'
See Figure 11-17 for an example. Also notice that we did not specify -noexit. Results were thus returned to the command window, and then we exited out of PowerShell.
You can run PowerShell commands from SQL Server Agent. There is a special job type named PowerShell that expects a PowerShell command. Figure 11-18 shows the job step definition in SQL Server Agent where we have selected the Type of PowerShell and then entered the PowerShell script in the Command section. This job step will now execute the PowerShell script as part of the overall SQL Server Agent job.
Maintenance plans (which we will talk about in more detail in the next chapter) do not directly support PowerShell commands. However, there is a SQL Server Agent task in the Maintenance Plan toolbox that can be used to call a SQL Server Agent task that has a job step or steps that are PowerShell commands. In Figure 11-19, we have created an Execute SQL Server Agent Job Task as part of our maintenance plan (that refers to the Run PowerShell Script SQL Server Agent Job we created earlier in Figure 11-18). We'll talk more on how to create a maintenance plan in the "Creating a Database Maintenance Plan" section in a moment. In this way, you can incorporate PowerShell commands into your maintenance plans.
Version 2 of PowerShell (which comes with Windows 7 and Windows Server 2008 R2 or can be downloaded) introduces the PowerShell Integrated Scripting Environment (ISE). The Windows PowerShell ISE is a host application for Windows PowerShell. Windows PowerShell ISE is a development and debugging environment that allows you to run commands and write, test, and debug scripts in a single Windows graphic user interface. It features multiline editing, tab completion, syntax coloring, selective execution, context-sensitive help, and support for right-to-left languages.
To start the Windows PowerShell ISE, follow these steps:
In Windows, click Start
In the Windows PowerShell console, called
powershell.exe, or in the Run box, type powershell_ise.exe.
Figure 11-20 shows the ISE. Notice the language-sensitive coloring, the three panes (script pane, output pane, and command pane), and the ability to interactively create script and run commands all in the same environment. The ISE is a great tool for creating, debugging, and testing PowerShell scripts and a very good reason for upgrading to PowerShell 2.0.
You can get much more sophisticated with PowerShell for SQL Server. For more information, you can review SQL Server 2008 Books Online. Another area to explore is the CodePlex open source community. There is a project dedicated to SQL server PowerShell extensions that you can download and use for a wide array of functions. The primary goal of these extensions is to provide a set of intuitive functions based on the SMO objects (if you recall, this is one of the big benefits of SQL Server PowerShell). Refer to http://www.codeplex.com/SQLPSX for more information.
SQLCMD and PowerShell give you excellent and expressive scripting environments from which to automate maintenance tasks. There's no reason for your DBA job to be a drudge. Take anything that you do on a routine basis, and automate it. Automate the drudgery, and then you're free to focus on the more interesting aspects of the job. (Or you can just take long lunches!)
Chapter 12 covers another key facet of automation: maintenance plans. A maintenance plan is something that you define for a given SQL Server instance that allows you to specify actions to take on a routine basis. The maintenance plan feature was introduced in SQL Server 2008. It's an important feature to know about, and we believe it should be the foundation of any approach to automating your routine work.