Learn How to use databases from within Windows PowerShell. This post is the video transcript of our DrillBit™ Video Training. The entire video can be found here: PowerShell DrillBit Use Databases from Within PowerShell. The entire Video Training Library is available for only $25 per month.
Instructor: Don Jones, Microsoft MVP
Video Style: Screencast
Instructor: Don Jones, Microsoft MVP
How to Use Databases from within Windows PowerShell
Windows PowerShell doesn't have any native ways to access a database, but it can access the underlying .NET Framework classes so that it can retrieve information from and update information in a database. One of the first things you'll have to do is figure out what your server name is that has your database, and that's relatively straightforward. But it's possible for a server to run more than one instance, or more than one copy of SQL Server, so you'll need to know the instance name if the one you want to connect to isn't the default one for the server.
One of the easiest ways to get that information is to go to the server and run "get service."
You'll spin back up to the M's, we're looking for Microsoft here. You should eventually find something that says "MS SQL."
The dollar sign $ starts the beginning of the instance name.
This computer has an instance of SQL Server running, and the instance name is SQL Express.
If you've installed the Express Edition of SQL, by default it does install itself to a SQL Express. That will often be the case if that's the version of SQL Server that you're using.
Once you have that information, you'll need to construct what's called a "connection string." This is basically a set of directions that tells the .NET Framework where the server is, what instance you want to connect to, how you want to log on, and which database you want to use.
I can never remember the connection strings, and no one can, which is why there is connectionstrings.com.
Here you can see there are many different types of databases supported. Since I'm using SQL 2008, I’ll be looking for the .NET Framework data provider for our SQL Server,
This is an example of what you'll need.
You can see that there's a few pieces of information that you'll need to change. This is the name or address of your server. That will have a backslash with the instance name if you're not connecting to the default. Initial catalog is the database you want to connect to, and user ID and password are fairly straightforward.
If you decide that you're going to use integrated security, meaning you're going to rely on your Windows credentials to authenticate you, that's the trusted connection version.
Again, you'll provide the server address and the initial database, but then simply tell it that "integrated security equals SSPI."
You won't provide a username or password. Instead, SQL will authenticate you based on the credentials that you used to log onto Windows.
Now, you've got your server name, you've got your instance name, and you have your connection string. I find that the easiest way to use databases is to get some functions that build everything in together. That's what I've done here.
This first function is called Get-DatabaseData.
You can see that it accepts three parameters: "$connectionString," "$query," which is the SQL Server language query you want to execute, and then a switch. You'll specify "$isSQLServer" if you are in fact connecting to SQL Server.
This function is capable of connecting to other types of databases as well. Rather than using the SQL client, it uses an OLE DB client.
If you are connecting to SQL Server, you would specify minus is SQL Server. We'll see an example of this function in use in just a moment.
Also, provide a second function called "Invoke-DatabaseQuery."
The first function is used when all you're doing is retrieving data from SQL Server. This function is used when you want to change something. You're executing an update, a delete or an insert query. The parameters are the same: $connectionString, $query, and if it is a SQL Server, $isSQLServer.
Let's take a look at some of these in action.
I have created a couple of functions here that will talk to a database, retrieve information from them, write information back to them and so forth. Here's the first one. "Get-DJComputersFromSQL."
This is designed to retrieve information from SQL. All you have to do is give it the SQL Server instance name, the name of the database, the name of the table and the name of the column that has a computer name in it.
You'll see here where it actually uses that database function I created.
Get-DatabaseData, it passes along the connection string, sends it the query that I just constructed here in text, and specifies -isSQLServer.
To see an example of updating a database, you just need to scroll down a little bit to SetDJComputersInSQL.
Again, there are a bunch of parameters that pass in data.
It creates a connection string in text, then it creates a query in text.
Here's where it uses the Invoke-DdatabaseQuery function, passing along a -connectionString, specifying that it -isSQLServer and specifying the -query that needs to be executed.
Once you have functions like this that wrap up all the difficulty of talking to a database, it's fairly easy to use. So long as you understand the connection string issues and you understand enough of the SQL language to write the query that you need, these functions make it easier to actually execute those queries and get results back.