Using Powershell to loop through a list of SQL Server Databases

I use Powershell quite a bit to automate routine tasks with SQL Server – mainly because Powershell makes it so simple to perform the same task on a given list of servers. With Powershell, we can execute a .sql script on many servers and save the output for later use. For example, say you need to script out and save database security for all production databases. We can do this with Powershell easily, and I’ll show you how.

Here is the sql script I will be using to script out database level security.

First of all, there needs to be an inventory or list of the databases that you will be querying. Here is an example of a SQL_DATABASES inventory table, and the query we will be using to get our list of databases. We will need both the server and database names.

CREATE TABLE [dbo].[SQL_DATABASES](
	[INSTANCE] [varchar](50) NOT NULL,
	[DATABASENAME] [varchar](100) NOT NULL,
	[REGION] [varchar](4) NOT NULL)
--------
SELECT INSTANCE
 ,DATABASENAME
FROM dbo.SQL_DATABASES
WHERE REGION = 'PROD' --or 'DEV' / 'TEST'

We will be using invoke-sqlcmd to make the connection to the inventory table. You can hit up that link to see the differences between sqlcmd and invoke-sqlcmd. Basically, we need to specify our -ServerInstance, -Database and -Query we will use to get the list of our production databases. Then, each Instance and Database pair will be put into the $databases variable.

# $databases grabs list of production databases 
# from the SQL_DATABASES table on your Database
# ----------------------------------------------
# NOTE* It is always best to test any process on a single dev or test server
# before trying it on ALL production databases!

$databases = invoke-sqlcmd -ServerInstance Server -Database Database `
-Query "SELECT INSTANCE ,DATABASENAME FROM Database.dbo.SQL_DATABASES WHERE REGION = 'PROD'"


Now that we’ve placed the list of Servers and databases into $databases, we need a way to loop through each database in that list. This is where the foreach loop comes in handy.

foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate
# through each pair of server / database.
$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.DATABASENAME #databasename from the select query
#...

Next we will generate the file name for the output of the query for each database. The goal is to have a separate .sql file for each database with a file name format of “Instance_DatabaseName_security.sql”. I am using C:\DBA_SCRIPTS\DB_Permissions\ as the filepath for the output files. I’ve also included the syntax that could be used to do a Replace on named instances to remove the “\” for file names.

#...
#generate the output file name for each server/database pair
$filepath = "C:\DBA_SCRIPTS\DB_Permissions\"
$filename = $Inst +"_"+ $DBname +"_security.sql" 

# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.

$outfile = ($filepath + $filename) #create out-file file name
#...

Now that we have the list of databases and have generated the output file name for each pair, we can execute our script on each database. For scripting database permissions, I use a variation of this script from S. Kusen. This next piece of powershell will connect to each individual server($Inst) and database($DBname) using invoke-sqlcmd, execute our .sql file (SQL_ScriptDBPermissions_V2.sql) to script out database security (using -InputFile), and output the results from each database into a separate .sql file at the $outfile path we created above.

#...
#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\DB_Permissions\SQL_ScriptDBPermissions_V2.sql" | out-file -filepath ($outfile)

} #end foreach loop

It’s as simple as that, and this is just one example of how powershell can easily be used to accomplish a task on many SQL Servers at once.

Here is the entire powershell script:

# NOTE* It is always best to test any process on a single dev or test server before trying it on ALL production databases!
# ----------------------------------------------
Import-Module SqlPs -DisableNameChecking #may only need this line for SQL 2012 +

# $databases grabs list of production databases from the SQL_DATABASES table on your Database
$databases = invoke-sqlcmd -ServerInstance Server -Database Database -Query "SELECT INSTANCE ,DATABASENAME FROM Database.dbo.SQL_DATABASES WHERE REGION = 'PROD'"


foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.DATABASENAME #databasename from the select query


#generate the output file name for each server/database pair
$filepath = "C:\DBA_SCRIPTS\DB_Permissions\"
$filename = $Inst +"_"+ $DBname +"_security.sql"
 
# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.
 
$outfile = ($filepath + $filename) #create out-file file name


#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\DB_Permissions\SQL_ScriptDBPermissions_V2.sql" | out-file -filepath ($outfile)
 
} #end foreach loop

Free Tools for the SQL Server DBA

Let me preface this by saying that this is my first ever blog post of any kind. I decided to make this blog to document “interesting” experiences or situations that may arise during my day-to-day work as a DBA. This blog is as much for me as it is for anyone who decides to read it.

I decided to make my very first post about free tools that I use to help solve various problems, whether it be performance tuning or routine maintenance tasks. These tools are completely free – so you have no excuse to not be using them.

I will try to maintain this list on this page, but without the details.

TOOLS

sp_WhoIsActive:

DBA’s need to pinpoint exactly what is running slow on a database and sometimes do it very quickly. Developers love the “Hey why is my app running slow?” or “OMGWTH – Why haven’t you fixed that POS database yet?” questions. sp_WhoIsActive lets you see exactly what queries are running, how long they have been running, and what they are waiting on currently. WhoIsActive also has a lot of extra parameters you can pass to it to find even more info. Some of my favorites are @Get_plans, @find_block_leaders, and @get_avg_time.

I could write an entire post just about using WhoIsActive, so I’ll point you to some much better blogs. Brent Ozar did a very good post on WhoIsActive and Adam Mechanic (the creator of WhoIsActive) did a whole blog series on using the procedure.

Pro Tip: Install WhoIsActive on all servers and create a Keyboard Query Shortcut in SSMS (I use Ctrl+F1) to execute sp_WhoIsActive in any open query window.

SQLQueryStress:

SQLQueryStress is a very handy tool, and coincidentally, it was also created by Adam Mechanic. Let’s say you are trying to tune a particular query, whether it be by re-writing the query or by adding indexes. SQLQueryStress provides a very quick look at how the query is performing by showing seconds per iteration, total logical reads per iteration, and CPU seconds per iteration. This removes the step of having to add up the STATISTICS IO output to figure out the total number of reads the query is doing.

It can also do some very cool things with parameter substitution. It is very important when tuning a query to make sure the query is faster with many different parameters, not just a single set of parameters, and SQLQueryStress will help automate that process for you.

SQLQueryStress

SQL Load Generator:

Every need to generate a query load on your SQL Server? SQL Load Generator provides a quick and easy way of doing this. It’ll let you run many queries simultaneously with different logins and different application names. Enough said.

LoadGenerator

SQL Sentry Plan Explorer:

After using Plan Explorer, it is the only way that I will analyze execution plans anymore. SSMS is clunky and can make it challenging to analyze more complex execution plans. Plan explorer helps to immediately identify the most expensive operators or subtrees. Bottom line: Plan Explorer makes query analysis much more efficient and it looks so much better than viewing a plan in SSMS.

Aaron Bertrand did a great post on using Plan Explorer here.

ClearTrace:

Ever need to quickly analyze a trace file? ClearTrace will import a SQL Server trace file into a database of your choosing, summarize the data, and graphically display performance information in an easy-to-digest format. ClearTrace can sort by whichever column you want, making it really easy to find either the most I/O or CPU intensive queries.

ClearTrace

Redgate SQL Search:

SQL Search is a SSMS add-in that does exactly what it sounds like: it will search through objects (tables, views, stored procedures, functions, etc.) in a database for a snippet of text. This is helpful when trying to find everything that may reference a table or any other given object.

SQLSearch

NimbleText:

NimbleText is great if you write a lot of SQL statements. If you do any type of repetitive typing or text editing, NimbleText can probably help.

WEBSITES

StatisticsIOParser.com:

StatisticsIOParser (developed by Richie Rump) is a great way to deal with the obnoxious output of SET STATISTICS IO. It formats and sums up the totals of the output. Very simple and very helpful.

StatisticsIOParser

PoorSQL.com:

PoorSQL is an online SQL formatter. It makes ugly SQL look pretty. (Very similar affect as that third cocktail you drank at the bar the other night – only you probably weren’t looking at SQL)

Pro Tip: Download the SSMS plugin to format SQL in your own query windows.PoorSQL

Did I forget a free tool or website? Is there a great free tool I’m not using, and definitely should be? Let me know in the comments.