Hey, Scripting Guy! I have several items I have to check daily on my SQL 2008 servers. I currently have some VBScripts that do many of my tasks, but there are some new things I want to do. I figure that Windows PowerShell would be easier. Do you have any pointers?

– SC

Hi SC,

It is becoming late as Ed continues plowing through almost 200 e-mail messages that piled up during the two weeks leading up to Tech·Ed 2009 and during the week of Tech·Ed 2009. He is about ready to go swimming, expecting that the cool water will cool his overheated brain if nothing else.

Because Ed does not consider himself to be a SQL Server 2008 guru, we decided to pass this question off to Buck Woody, SQL Server Technical Specialist for the Microsoft Corporation. He is a former Program Manager on the SQL Server team and the author of seven books about SQL Server. (Compared to Buck, Ed cannot even spell SQL. Oh, wait, he just did.)

In the last three installments, we explained what Windows PowerShell is and how a database administrator (DBA) can use it.

Last time we showed you the new Windows PowerShell Provider for SQL Server 2008. It’s the “mini-shell” that makes using Windows PowerShell with SQL Server instances even easier. We tooled around a bit inside the structure that this provider has, learned about the objects that it exposes and how you can get and set properties, and even ran several commands that the provider includes. But now it is time to really put this thing to work. We will see how you can use Windows PowerShell to perform some useful tasks, working through some practical applications.

Let us take everything that you have learned to this point and put it together in a single command. We will create a variable, use a standard command, send the results to more commands by using a pipeline operator, and do it all inside the SQL Server 2008 Windows PowerShell provider.

The first thing we have to do is obtain the directory of all the databases on an instance. Substituting the server name in the serverName part of this string and instance name (use default if it is the default instance) in the instanceName part of this string, type this command in the Windows PowerShell for SQL Server provider:

DIR “SQLSERVER:\SQL\serverName\instanceName\Databases” 

Now let us add in a where-Object Windows PowerShell cmdlet to locate only those databases for which the backup is greater than a day:

DIR “SQLSERVER:\SQL\serverName\instanceName\Databases” |
where-Object {((get-Date)-($_.LastBackupDate)).days -gt 1} 

You can see that the Where-Objectcmdlet is followed by a set of braces. This encloses what we want to do with the collection of items to the results of the dir command. We are just asking for the LastBackupDate property on each item (remember that we can see those properties by using the Get-Member cmdlet). The $_part stands in for each database, one at a time. The interesting part is that we are enclosing that whole string with another cmdlet that can read dates—Get-Date. We ask for a property of that cmdlet (the days part), and the whole thing is enclosed by that Where-Object cmdlet. So in plain old English, here is what we are really saying:

“See all those databases? Show me the ones that have a property of LastBackupDate greater than one day.”

Even though this shows us the databases, it does not show the information that we want. No worries. We will just pipe that out to another command that selects the objects and shows certain properties. The name of this mysterious cmdlet is Select-Object:

DIR “SQLSERVER:\SQL\serverName\instanceName\Databases” | 
where-Object {((get-Date)-($_.LastBackupDate)).days -gt 1}  | 
sort-Object -Property LastBackupDate | 
select-Object Name, RecoveryModel, LastBackupDate

Not bad. What else can we do? Do you remember a couple of articles back when we used the Server Management Objects (SMO) Library to script out certain objects? With the Windows PowerShell provider for SQL Server, it is even easier. It does not even need much explanation, although it could do with a warning.

Because you are in the SQL Server PowerShell provider, each SMO library object class is now a directory, exactly like the databases we just used. Now we can type dir to gain access to those objects. One dir command and we can loop through all the objects it contains. But as the Scripting Guys’ grandmother used to say, “Just because you can do something, does not mean that you shoulddo something.” Scripting is one of those times. If you script out all the tables in a database, you could really take some time and processor cycles on your system. You can do it, of course, but we are just saying that perhaps you might want to take a microsecond and reconsider.

With all those warnings and repeating ourselves that you should be on a test system, here is a simple script to script out all the databases. Not all the objects in them, mind you, but the databases themselves:

DIR “SQLSERVER:\SQL\serverName\instanceName\Databases” | 
forEach-object {$_.Script()}

The same command works for other containers such as tables and views. You could even restrict the tables with that useful Select-Object cmdlet to limit the scripting to a certain schema, name, or other property. Oh—you want that to go out to a file? No problem. Just add the Out-File cmdlet with another pipeline operator:

DIR “SQLSERVER:\SQL\serverName\instanceName\Databases” | 
forEach-object {$_.Script()} | 
out-File –FilePath C:\temp\Scripts.sql

Another way to the SQL provider is to send the script to a .sql file, and then use the script file to make a change to the database object. Run the script again, but change the output file to another name. Then just use the Compare-Object cmdlet to see the differences: Get-Help Compare-Object.

No problem, we have several ways to do this. The first way is just to use Invoke-Sqlcmd cmdlet that the SQL Server provider has built in. Here is a sample. As always, replace the serverName and instanceName for your system:

invoke-Sqlcmd -query “select @@VERSION” –ServerInstance serverName\instanceName

You must be able to connect to that instance with different credentials? No worries:

invoke-Sqlcmd -query “select @@VERSION” –ServerInstance serverName\instanceName `
–UserName yourUserName –Password yourPassword

Replace the yourUserNamepart with, you know, you user name and the same for the password. By the way, this works for SQL Server 2005 and even SQL Server 2000 systems, too.

“Hey, wait a minute,” we hear you say. “That is cheating. I want a real drive letter on that other system, exactly as I have it here. I want to browse the objects, do the dir thing and all that. Can I do that?” No problemo. We will just make a completely new drive, by using the Windows PowerShell cmdlet called New-PSDrive. It makes a new Windows PowerShell drive from any installed provider. It requires a string representing the new drive and the kind of provider you want to use. From there, you add the server name that you want to connect to and there you have it:

new-PSDrive “OtherServer” -PSProvider SqlServer -Root “SQLSERVER:\SQL\ serverName\instanceName”

Now just type OtherServer: (or whatever you called it) and press ENTER. Voila! You are in the Instance folder.Now that you have learned enough to be dangerous, you can really get in there and dig around to think of all kinds of useful scripts for working with your databases. And here is the real beauty of working with Windows PowerShell and SQL Server: Windows PowerShell lets you work with all kinds of objects in addition to the database, such as files, Web pages, and more. In fact, you can manage Windows servers, Microsoft Exchange servers, System Center, and most other Microsoft products with Windows PowerShell. And because Windows PowerShell is based on the .NET Framework, you have access to all those items, too. Who says DBAs can’t learn new tricks?

Well, SC, thanks for an excellent question to end this week. Buck Woody, you have our sincere appreciation for an entertaining and educational series of SQL Server articles. We are skipping Quick-Hits Friday this week because Monday was a holiday for us, and sometimes even we need to take a holiday. Next week, we take a look back at some of the best content from Scripting Games history. Just to get you all juiced up for this year’s Summer Scripting Games, June 15–26. Until then, peace.

Ed Wilson and Craig Liebendorfer, Scripting Guys