19 July 2012

27924 views

27 0

PowerShell SMO: Just Writing Things Once

Sometimes, you can tire of writing the same PowerShell code once again. After this happened to Phil whilst keying in an SMO pipeline to access databases, it occurred to him that he should have only one pipeline to access databases in PowerShell, one reusable pipeline

In this article, I’ll be showing you how to perform a variety of jobs on databases in a number of servers via PowerShell. Some involve simple things like getting database properties, but others involve executing SQL in all databases, or getting entire database-build-scripts. Generally, they create a single report for all your servers and databases that can then be imported into Excel or Word. You should be able to build on these examples to create a variety of other tasks. These sample jobs and reports include

Checking on database sizes
Checking on the file groups and their files
Checking on database sizes
Checking on Transaction Log autogrowth
Checking the number and size of Virtual Log Files (VLF)
Finding out when each database was last backed up (if ever!)
Examining table sizes
Creating Build scripts
Putting databases into source control
using SQL to Find all the tables in all your databases that don’t have any indexes
Using SQL to Find all the tables without primary keys

DRY (Don’t repeat yourself)

When a database developer uses PowerShell, it is often to perform a routine operation via SMO on a number of databases on several servers or instances. This applies to a whole range of tasks. Just speaking for myself, I’ll be creating scripts for individual tables and routines, and I’d be doing entire build scripts. I’ll probably also be doing some sort of documentation, or cataloguing, of what there is in each database, or maybe the individual database settings. I’ll also be setting up a test run, or checking a status with a DMV. I might be doing checks to make sure that indexes weren’t duplicated, or that there was a primary key. I might just be searching for a string, or checking to see which users had rights of access. All these tasks require a PowerShell SMO script that accesses all or some of the databases on all or some of my instances.

After a while, the scripts will seem horribly familiar. It will probably consist of a pipeline that takes a list of servers, often the registered servers in my case, and finds all the non-system databases. It will either pick databases from a list (a whitelist), find those with a pattern in the name, or do every database that is not in a list (a blacklist). I might want to just do databases with a particular prefix or suffix. Then it will do things with each database. All that really changes is the action you want to perform with each database.

It always irritates me to have the same code in more than one place. You’ll really only want to write this once, and then reuse it. The only problem is how.

If you are doing a long background job serially in PowerShell, it makes sense to do this in a pipeline, rather than creating potentially huge collections of large objects, because this is kinder to the memory management of .NET. Each server object is passed down the pipeline, each to have its collection of databases passed on, one at a time to be filtered and worked on and tidied out of the way.

All you need to do is to inject the action you specify into the right part of the pipeline. To demonstrate how to do this with anonymous functions that we assign to delegates, we’ll step back slightly from the problem of accessing servers and their databases via SMO, and we’ll forget any fancy stuff like functions. We’ll demonstrate a pipeline rather differently by creating a PowerShell script to generate the lyrics for ‘Ten Green Bottles’. Then, we’ll change it slightly without altering the pipeline itself in order to generate ‘There were ten in the bed’. Believe me, this will help you understand the meaty stuff later on.

Ten green bottles

We’ll introduce a filter that decides the number less than, or equal to, ten at which to start the great poem. Then we’ll make the whole pipeline do something else without changing the code in the pipeline itself.

#The basic sequence

$Sequence="ten","nine","eight","seven","six","five","four","three","two","one"

$StartAt='Ten' #The number we want to start at. Try it out with other values.

$action={param ($x,$Isfirst); #the scriptblock called for every item in the list

if (-not $Isfirst) {"there would be $x green bottles hanging on the wall."}

There were $x green bottles hanging on the wall,

$x green bottles hanging on the wall

and if one green bottle should accidentally fall

}

$Start={ " $StartAt Green Bottles.`n"} #Scriptblock called at start

#and the one called after all items are processed

$Finish={ "there would be no green bottles hanging on the wall`n`n The End`n" }

#the scriptblock that decides whether the current item should be processed

$Filter={ param ($x, $status); if ($x -eq $startAt) {$status = $true}; $status }

#the actual pipeline. We will save the scriptblock literal in a variable which we can execute

$pipeline={

$Sequence|

& { BEGIN{$ShouldOutput = $false} PROCESS{$ShouldOutput = $filter.invoke( $_, $ShouldOutput); if ($shouldOutput) {$_}} } |

& {BEGIN{$first=$true; $Start.invoke($_) } PROCESS{$action.invoke($_, $first); $first=$false} END{$Finish.invoke($_) } }

}

$pipeline.invoke() #and we just invoke the pipeline

<# Now, we can change the poem to be 'There were ten in the bed' without touching the pipeline at all, but just changing the contents of the variables holding three of the four scriptblocks #>

$action={param ($x,$Isfirst); #the scriptblock called for every item in the list

if (-not $Isfirst) { "Single beds were only made for $x"}

"`nThere were $x in the bed and the little one said,"

if ($x -ne 'one') { @"

'Roll over, roll over!'.

So they all rolled over and one fell out

And he gave a little scream and he gave a little shout

'Please remember to tie a knot in your pajamas'

}

else {for($ii = 1;$ii -le 3; $ii++) {"I've got the whole mattress to myself"}

"I've got the mattress to myself."}

}

$Start={ " There were $StartAt in the Bed.`n"} #Scriptblock called at start

#and the one called after all items are processed

$Finish={ "`n`n The End" }

#and we then just call the pipeline again!

$pipeline.invoke()

What we’re actually doing is using delegates. We are using those scriptblocks that we assign to variables as anonymous functions. We’re injecting functionality into the pipeline. We haven’t changed the code of the pipeline itself.

PowerShell can be adapted very well to the sort of programming that works by injecting functionality into a pre-existing structure. It is, after all, a dynamic language. In our case, this structure might be a pipeline that selects all your databases, one after another you and allows you to do what you need to each database.

$DataSources | # our list of servers

& {PROCESS{$ServerFilter.invoke($_)}} | # choose which servers from the list

Foreach-object {new-object ("$My.Server") $_ } | # create an SMO server object

Where-Object {$_.ServerType -ne $null} | # did you positively get the server?

Foreach-object {$_.Databases } | #for every server successfully reached

Where-Object {$_.IsSystemObject -ne $true} | #not the system objects

& {PROCESS{$Databasefilter.invoke($_)}} | # do all,avoid blacklist or do a whitelist etc

& {PROCESS{$JobToDo.invoke($_)}} #and do whatever you want for the database

This does most routine tasks, but we need to add a few things, just as we had to with the poem. The $Serverfilter and $DatabaseFilter are variables containing a scriptblock. They have a parameter, which is $_, meaning the current object in the pipeline. This parameter isn’t strictly necessary, since it would work by using the $_ parameter within the scriptblock, but it makes things easier for debugging, or if you have a complex script. This is equivalent to a C# delegate, but without the rigorous type-checking for the parameter you pass. With the poems, the filter didn’t change. It decided how many bottles we start with, or how many children in the bed. In this case having the filter code in a variable now allows us to place the actual filter to let us choose the database when we are running the script. We might want to specify a ‘white’ list of databases that we want to operate on, or we might want to operate on all but a ‘black’ list of databases. We might want to select only those that conform to a wildcard or regex string.

In our case, we’ll just insert a whitelist or a blacklist if one has been specified. Logically, you can’t specify a whitelist if you have a blacklist: it is one or the other, or neither.

$DatabaseFilter=$TheDatabaseFilter

if ($blacklist.count -gt 0) {$DatabaseFilter= { param($x); if ($blacklist -notcontains $x.name) {$x} }}

# followed by the ones you don't want, listed in your blacklist

if ($whitelist.count -gt 0) {$DatabaseFilter= { param($x); if ($whitelist -contains $x.name) {$x} }}

# and one that just selects the files you specify in your whitelist

If you then invoke this in the process part of a scriptblock, it will filter out all the unwanted database objects as they appear, and only pass on the ones you want.

You may have already categorised your databases with special prefix such a ‘Test’. In this case, you can use a different $Databasefilter filter to get only these,

1	{ param($x); if ($x.name -like 'Adv*'){$x}}

Or if you want to avoid them then

1	{ param($x); if ($x.name -notlike 'Adv*'){$x}}

You might want to just do part of a list of databases, specifying the start and end database, just like the poems. Who knows? You can also use blacklists or whitelists for the list of servers in your care, or your registered servers, if you prefer.

Why do it this way? It is because you do not need to alter the code for the pipeline, you are ‘injecting’ your filter into it. You have your code in just one place, without duplication. With a delegate we can specify the functionality, and therefore what actually happens, at script execution-time. You can have a different filter depending on the specific parameters . To do it using conventional conditional logic would end up with a rats nest of ‘if’ statements. One can, if necessary, be even cleverer and add as many tasks as you wish to perform once the database is selected, but we won’t overdo things at this point.

So, we’ll create the pipeline as an ‘advanced’ function, so that we can provide help and allow ‘debug-printing’ and ‘voluble’ messages for debugging via Write-Debug and Write-Verbose. This means that even your anonymous functions will be able to use these features as they are used within the same overall function. We’ll save this function in the same directory as all our code that actually does things.

So, without actually doing anything harmful to any database, lets test it out.

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

$DataSources=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance

Foreach-DatabaseInServers $Datasources | foreach-object{"Found database '$($_.name)' on Server $($_.parent.name) "} #test that you can cannect to your datasources

Once you’ve got this working to your satisfaction, and you’ve added any extra filters or other functionality you need and tested it, then you can start doing something useful. In the following examples, I’m just checking for AdventureWorks (I’ve put in a filter to select only those databases starting with ‘ADV’, but your requirements will be different!)

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

Foreach-DatabaseInServers $Servers `

-TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} |

Select-object @{Name="Server"; Expression={$_.parent.name}}, Name, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage |ConvertTo-HTML

This gives you a report that gives you the space available to each ‘Adventureworks’ database (or whatever you specify on the servers you specify, broken down by data and index usage.

Server	Name	DataSpaceUsage	SpaceAvailable	Index SpaceUsage
Phil.Factor.com\sql2005	AdventureWorks	84160	15280	78008
Phil.Factor.com\sql2005	AdventureworksClone	66 824	43320	70696
Phil.Factor.com\sql2005	AdventureWorksDW	43632	15992	23096
Phil.Factor.com\sql2008	AdventureWorks	104744	1040	78992
Phil.Factor.com\sql2008	AdventureWorksCopy	869 36	4992	77464
Phil.Factor.com\sql2012	AdventureWorksDW	43744	16200	22536
Phil.Factor.com\sql2012	AdventureWorksDW2008R2	45544	16168	26944
Phil.Factor.com\sql2014	AdventureWorksLT	3112	168	1632
Phil.Factor.com\sql2014	AdventureWorksLT2008R2	3192	16488	1632
Robyn.Page.com\sql2005	AdventureWorks	86032	2468856	80656
Robyn.Page.com\sql2005	AdventureWorksDW	43616	0	23088

the file groups and their files

Here we drill down on detail to find out (for all our databases on all our registered servers this time) how large each MDF file is in each group for each database. We see what group they are in, whether they are allowed to auto-grow – and if so the amount of growth, the type of growth, size of file. amount of space used and so on. This should help you to spot those MDF files where things are getting tight.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

Foreach-DatabaseInServers $Servers |

foreach-object{$_.FileGroups} | foreach-object{$_.files} |

Select-object @{Name="Server"; Expression={$_.parent.parent.parent.name}}, @{Name="Database"; Expression={$_.parent.parent.name}}, @{Name="Group"; Expression={$_.parent.name}}, Name, AvailableSpace, Filename, Growth, GrowthType, size, UsedSpace, MaxSize |

format-table

Checking on the log files

AutoGrowth

We can also check on log files on all our databases for all our registered servers (or whatever you wish, of course). We see whether they are allowed to auto-grow, and if so the amount of growth, the type of growth, size of file. amount of space used and so on. This should alert you to such problems as long-running transactions.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

Foreach-DatabaseInServers $Servers |

foreach-object{$_.Logfiles} |

Select-object @{Name="Server"; Expression={$_.parent.parent.name}}, @{Name="Database"; Expression={$_.parent.name}}, Name, Filename, Growth, GrowthType, size, UsedSpace, MaxSize |

format-table

VLFs

We can also very simply look at the Virtual Log files for all of the databases’ transaction logs, using a DBCC LogInfo directly.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

Foreach-DatabaseInServers $Servers |

Foreach-object {

$Db=$_

$_.ExecuteWithResults('dbcc loginfo').Tables[0] |

Measure-Object -Minimum -Maximum -Average FileSize |

Select-object @{Name="Server"; Expression={$Db.parent.name}},

@{Name="Database"; Expression={$Db.name}},

@{Name="No.VLFs"; Expression={$_.Count}},

@{Name="MaxVLFSize(mb)"; Expression={"{0:n2}" -f ($_.Maximum/1MB)}},

@{Name="MinVLFSize(mb)"; Expression={"{0:n2}" -f ($_.Minimum/1MB)}},

@{Name="AverageVLF(mb)"; Expression={"{0:n2}" -f ($_.Average/1MB)}}

} | Format-Table * -AutoSize

Checking when your databases were last backed up

With a few alterations it becomes a check on when they were last backed up. After all, the function is returning the databases as SMO objects and so you can pick anything from SMO: Database Properties or even (I’ve just shown you how!) from the SMO: Server Properties.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

Foreach-DatabaseInServers $Servers `

-TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} |

Select-object @{Name="Server"; Expression={$_.parent.name}}, Name, RecoveryModel, LastBackupDate, LastLogBackupDate, LastDifferentialBackupDate |ConvertTo-HTML

Server	Name	Recovery	LastBackupDate	LastLogBackupDate
Phil.Factor.com\sql2005	AdventureWorks	Full	15/11/2012 14:26:00	01/01/0001 00:00:00
Phil.Factor.com\sql2005	AdventureworksClone	Full	01/01/0001 00:00:00	01/01/0001 00:00:00
Phil.Factor.com\sql2005	AdventureWorksDW	Simple	01/01/0001 00:00:00	01/01/0001 00:00:00
Phil.Factor.com\sql2008	AdventureWorks	Simple	01/12/2012 09:14:59	01/01/0001 00:00:00
Phil.Factor.com\sql2008	AdventureWorksCopy	Full	01/12/2012 09:15:02	01/01/0001 00:00:00
Phil.Factor.com\sql2008	AdventureWorksDW	Simple	01/12/2012 09:15:04	01/01/0001 00:00:00
Phil.Factor.com\sql2008	AdventureWorksDW2008R2	Simple	01/12/2012 09:15:06	01/01/0001 00:00:00
Phil.Factor.com\sql2008	AdventureWorksLT	Simple	01/12/2012 09:15:06	01/01/0001 00:00:00
Phil.Factor.com\sql2008	AdventureWorksLT2008R2	Simple	01/12/2012 09:15:07	01/01/0001 00:00:00
Robyn.Page.com\sql2005	AdventureWorks	Full	20/09/2012 13:12:25	01/01/0001 00:00:00
Robyn.Page.com\sql2005	AdventureWorksDW	Simple	01/01/0001 00:00:00	01/01/0001 00:00:00

Well, that gets a lot of standard database reports out of the way, and with Select-Object, you can do a lot with calculated fields.

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

<# here we want to find out the data space used for each row, along with the rowcount. We then can calculate the average size of each row #>

Foreach-DatabaseInServers $servers -whitelist @('AdventureWorks') -jobToDo {

param($database)

write-verbose "Accessing the database '$($database.name)' on server $($database.parent.name)"

$database.tables| select-object (@{Name="Server"; Expression={$database.parent.name}}, @{Name="Database"; Expression={$database.name}}, @{Name="table"; Expression={$_.name}}, @{Name="Data Space Used (Kb)"; e={$_.DataSpaceUsed}}, @{Name="Data Rows"; e={$_.RowCount}}, @{Name="no. columns"; e={$_.columns.count}}, @{Name="Average Row size (Bytes)"; e={"{0:n2}" -f (($_.DataSpaceUsed/$_.RowCount)*1024)}})

} | format-table

How about generating a build script for all the databases of your registered servers, that is saved to a directory corresponding to the server?

In this case, we’ll use $JobToDo, which is a parameter that comes in handy if you like using such things as Write-Debug and Write-Verbose or if we want to do entirely different things depending on some condition. This is because the scriptblock is now executed in the context of the function. We also need a variable to specify where to save the files. In this case, I want a blacklist because I don’t want to script things like Adventureworks (In this example, I’ve taken out a lot of specific databases, ovbiously)

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

#and a list of the databases we don't want to be scripted

$blacklist='Pubs','NorthWind','AdventureWorks','AdventureWorksDW','ReportServer','ReportServerTempDB' #the databases I don't want to do

#where we want to store the scripts (each server/instance a separate directory

$Filepath='E:\MyScriptsDirectory' # local directory to save build-scripts to

#and do it

Foreach-DatabaseInServers -verbose $servers -blacklist $blacklist -jobToDo {param($database)

$directory="$($FilePath)\$( $database.Parent.URN.GetAttribute('Name','Server') -replace '[\\\/\:\.]','-' )";

$transfer = new-object ("$My.Transfer") $database

$CreationScriptOptions = new-object ("$My.ScriptingOptions")

$CreationScriptOptions.ExtendedProperties= $true # yes, we want these

$CreationScriptOptions.DRIAll= $true # and all the constraints

$CreationScriptOptions.Indexes= $true # Yup, these would be nice

$CreationScriptOptions.Triggers= $true # This should be included when scripting a database

$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file

$CreationScriptOptions.IncludeHeaders = $true; # of course

$CreationScriptOptions.ToFileOnly = $true #no need of string output as well

$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile

$CreationScriptOptions.Filename = "$directory\$($Database.name)_Build.sql";

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences

write-verbose "scripting '$($database.name)' in server '$($database.parent.name)' to $($CreationScriptOptions.Filename)"

if (!(Test-Path -path "$directory"))

{

Try { New-Item "$directory" -type directory | out-null }

Catch [system.exception]{

Write-Error "error while creating '$directory' "

return

}

Try {$transfer.ScriptTransfer()}

Catch [system.exception]{

Write-Error "couldn't script to '$directory\$($Database.name)_Build.sql' because of error (possibly an encrypted stored procedure"

}

'did that go well?'

Running a SQL Script on rather a lot of databases

That was pretty easy. What about executing a SQL Script in a number of databases in a number of servers? Right away, sir.

Generating a single report from running the query

Let’s start with a simple example that just gives you a report of all the tables in all your databases that don’t have any indexes. (here, we’ve just restricted it to Adventureworks)

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

$SQL=@'

--Which of my tables don't have any indexes at all?

SELECT @@Servername as [Server],DB_NAME() as [Database],

DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without any index]

FROM sys.tables t WHERE OBJECTPROPERTY(object_id, 'TableHasIndex')=0

order by [Tables without any index]

<#So, we can make it call some sql and get back a result. In this case we are only looking at the various AdventureWorks databases in all the servers, just to illustrate the different filters you can specify.#>

Foreach-DatabaseInServers $servers -TheDatabasefilter { param($x); if ($x.name -like 'Adv*' ){$x}} -jobToDo {

param($database)

$result=$database.ExecuteWithResults("$SQL") #execute the SQL

$result.Tables[0]

} | select-object ('Server','Database' ,'Tables without any index') | convertTo-html

Generating a report for each database

Now, we’ll do a slightly more complicated routine. We’ll write some files, one for each database, listing in CSV all the tables in each database that are heaps, without primary keys. You’ll see that it would be dead easy to use this for a variety of reports, just by changing the SQL, (and SQL Title). I use this method for testing code on a variety of different databases, servers and collations.

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

#now fetch the list of all our registered servers

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

$Filepath='E:\MyScriptsDirectory' # local directory to save the reports to

$SQLTitle='All_Heaps_In_'

$SQL=@'

--Which of my tables don't have primary keys?

SELECT @@Servername as [Server],DB_NAME() as [Database], --we'll do it via information_Schema

TheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'

+TheTables.Table_Name AS [tables without primary keys]

FROM

INFORMATION_SCHEMA.TABLES TheTables

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TheConstraints

ON TheTables.table_Schema=TheConstraints.table_schema

AND TheTables.table_name=TheConstraints.table_name

AND constraint_type='PRIMARY KEY'

WHERE table_Type='BASE TABLE'

AND constraint_name IS NULL

ORDER BY [tables without primary keys]

Foreach-DatabaseInServers $servers -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} -jobToDo {

param($database)

$databaseName=$database.name

$ServerName=$database.Parent.Name

$directory="$($FilePath)\$($database.parent.name -replace '[\\\/\:\.]','-' )";#create a directory

#and a handler for warnings and PRINT messages

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};

$database.parent.ConnectionContext.add_InfoMessage($handler);

$result=$database.ExecuteWithResults("$SQL") #execute the SQL

$database.parent.ConnectionContext.remove_InfoMessage($handler);

if (!(Test-Path -path "$directory")) #create the directory if necessary

{

Try { New-Item "$directory" -type directory | out-null }

Catch [system.exception]{

Write-Error "error while creating '$directory' "

return

}

<# you might want to save these in a central monitoring server, or put them all in one file, of course, but that is what powershell is all about #>

$result.Tables[0]| convertto-csv -NoTypeInformation >"$directory\$SQLTitle$databasename.csv"

}

Popping plenty of databases into source control

fine. Let’s do something slightly trickier. There is a script that generates the source, one file per object, of the databases of your registered servers. Actually, as it stands, I’ve only included those databases that start with the word ‘Test’. I suspect you’ll want something slightly different. I’ve put each server in its own directory, of which each database is in its own subdirectory. Each object type (e.g. TABLE) is in its own directory within this. From this, it is a mere bagatelle to pop these into source control, but we’re not scripting that for you since I don’t know what Source Control Manager (SCM) you’re using.

$DirectoryToSaveTo='E:\MyScriptsDirectory' # the directory where you want to store them

$TheDatabasefilter = { param($x); if ($x.name -like 'Test*'){$x}} # just those starting with 'Phil'.

. ".\ForAllDatabaseServers.ps1" # pull in our pipeline function 'Foreach-DatabaseInServers'

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

#get a list of the servers we want to scan

$servers= dir 'SQLSERVER:\sqlregistration\Database Engine Server Group' | foreach-object{$_.name}

$ServiceBrokerTypes=@( 'MessageType','ServiceBroker','ServiceContract','ServiceQueue','ServiceRoute','RemoteServiceBinding')

$JobToDo= {

$database=$_

$databaseName=$_.name

$ServerName=$_.Parent.URN.GetAttribute('Name','Server')

write-verbose "scripting $databasename in $serverName"

$ScriptOptions = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")

$ScriptOptions.ExtendedProperties= $true # yes, we want these

$ScriptOptions.DRIAll= $true # and all the constraints

$ScriptOptions.Indexes= $true # Yup, these would be nice

$ScriptOptions.ScriptBatchTerminator = $true # this only goes to the file

$ScriptOptions.IncludeHeaders = $true; # of course

$ScriptOptions.ToFileOnly = $true # no need of string output as well

$ScriptOptions.IncludeIfNotExists = $true # not necessary but makes script more versatile

$scrp=new-object ("$My.Scripter") $Database.parent

$scrp.options=$ScriptOptions

$database.EnumObjects([long]0x1FFFFFFF -band [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all) | `

Where-Object {('sys','information_schema') -notcontains $_.Schema} | Foreach-Object {

$urn= [Microsoft.SqlServer.Management.Sdk.Sfc.Urn] $_.URN

if (('ExtendedStoredProcedure','ServiceBroker') -notcontains $urn.type)

{

$currentPath="$DirectoryToSaveTo\$($ServerName -replace '[\\\/\:\.]','-' )\$($urn.GetAttribute('Name','Database') -replace '[\\\/\:\.]','-')"

if ( $ServiceBrokerTypes -contains $urn.type)

{$fullPath="$currentPath\ServiceBroker\$($urn.type)"}

else

{$fullPath="$currentPath\$($urn.type)"}

if (!(Test-Path -path $fullPath ))

{

Try { New-Item $fullPath -type directory | out-null }

Catch [system.exception]{

Write-Error "error while creating '$fullPath' "

return

}

$scrp.options.FileName = "$fullPath\$($urn.GetAttribute('Schema')-replace '[\\\/\:\.]','-')-$($urn.GetAttribute('Name') -replace '[\\\/\:\.]','-').sql"

$UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')

$URNCollection.add($urn)

write-verbose "writing script to $($scrp.options.FileName)"

$scrp.Script($URNCollection)

}

$params = @{DataSources=$Servers;TheDatabaseFilter=$TheDatabasefilter;JobToDo=$JobToDo;verbose=$true}

Foreach-DatabaseInServers @Params

"done them, Master."

100

101

102

103

Function Foreach-DatabaseInServers {

.SYNOPSIS

Does the whatever scriptblock you wish for the databases in list of SQL server instances

.DESCRIPTION

This takes the scriptblock you define and executes it against every database that you specify, either by using a whitelist or a blacklist, or just does them all.

.EXAMPLE

$DataSources='Dave', 'Dee', 'Dozy', 'Beaky', 'Mick', 'Titch' # server name and instance

$JobToDo ={param($database)

$databaseName=$database.name

$ServerName=$database.Parent.Name

"Do stuff to $databasename on $Servername"}

$whitelist=''#the only databases I want to do, if they're there(Leave empty otherwise)

$blacklist='Pubs','NorthWind','AdventureWorks','AdventureWorksDW','ReportServer','ReportServerTempDB' #the databases I don't want to do

Foreach-DatabaseInServers $Datasources $jobToDo $whitelist $blacklist

.PARAMETER DataSources

The list of servers that you want the files from

.PARAMETER JobToDo

The script of the job you want done

.PARAMETER Whitelist

The list of databases you want the action performed on, leaving out all others

.PARAMETER Blacklist

The list of databases you don't want the action performed on

.PARAMETER TheServerFilter

Any filter you specify for servers

.PARAMETER TheDatabaseFilter

Any filter you specify for databases

.PARAMETER Initialisation

any action that needs to be done first before the pipeline

param([CmdletBinding()]

# The list of databases

[Parameter(Mandatory=$True,

Position=0,

HelpMessage='the list of one or more SQL Server instances you would like to target')]

[array] $DataSources,

# The Job To Do

[Parameter(Mandatory=$false,

Position=1,

HelpMessage='The actual job you want to do in each database')]

[scriptblock] $JobToDo ={ param($x); $x},

# The WhiteList

[Parameter(Mandatory=$false,

Position=2,

HelpMessage='the databases in each instance that you want to select')]

[array] $Whitelist=$null,

# The BlackList

[Parameter(Mandatory=$false,

Position=3,

HelpMessage='the databases in each instance that you dont want to select')]

[array] $Blacklist=$null,

# The custom database filter you want to use

[Parameter(Mandatory=$false,

Position=4,

HelpMessage='if the whitelist or blacklist is no good for what you want')]

[scriptblock] $TheServerFilter={ param($x); $x }, # define a filter that does nothing by default

# The custom server filter you want to use

[Parameter(Mandatory=$false,

Position=5,

HelpMessage='if you wish to select from all of the databases')]

[scriptblock] $TheDatabaseFilter={ param($x); $x },# define a filter that does nothing by default

# The custom server filter you want to use

[Parameter(Mandatory=$false,

Position=6,

HelpMessage='any initialisation you want (with SMO loaded)')]

[scriptblock] $Initialisation={}# define an initialisation routine that does nothing by default

)

# set "Option Explicit" to catch subtle errors

set-psdebug -strict

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries

$ms='Microsoft.SqlServer'

$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")

if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {

[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null

}

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null

$My="$ms.Management.Smo" #

$DatabaseFilter=$TheDatabaseFilter

if ($blacklist.count -gt 0) {$DatabaseFilter= { param($x); if ($blacklist -notcontains $x.name) {$x} }}

# followed by the ones you don't want, listed in your blacklist

if ($whitelist.count -gt 0) {$DatabaseFilter= { param($x); if ($whitelist -contains $x.name) {$x} }}

# and one that just selects the files you specify in your whitelist

write-verbose "The database filter is '$DatabaseFilter'"

$Initialisation.invoke() <# just in case you have a once-off routine you wish to execute (if you are doing SMO, since the function loads SMO.) #>

write-verbose "Using server list $DataSources";

$DataSources | # our list of servers

& {PROCESS{$TheServerFilter.invoke($_)}} | # choose which servers from the list

Foreach-object { write-verbose "Accessing $($_.ToString())"; new-object ("$My.Server") $_ } | # create an SMO server object

Where-Object {$_.ServerType -ne $null} | # did you positively get the server?

Foreach-object { write-verbose "reached $($_.ToString())";$_.Databases } | #for every server successfully reached

Foreach-object { write-verbose "found database $($_.name)"; $_} | #display each database

Where-Object {$_.IsSystemObject -ne $true} | #not the system objects

& {PROCESS{$DatabaseFilter.invoke($_)}} | # do all,avoid blacklist or do a whitelist etc

& {PROCESS{$JobToDo.invoke($_)}} #and do whatever you want for the database

}

So there you have it; a simple pipeline for serving up whatever databases you specify, into which you can inject whatever functionality you need. Your requirements might be different. Your servers could be outside the windows domain and therefore accessible only by SQL Server credentials. You may need to do slightly different things to different databases. You may need to specify a process to be done at the sever level as well as the database level. In which case, hack it about and come up with something that fits your requirements. That’s the joy of a scripting language like PowerShell..

In case things don’t work.

Unless you have servers called Dave, Dee, Dosy, Beaky, Mick and Tich, you’ll need to change a list like …
$DataSources=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance
into a list of your servers.
If your servers are outside your domain, then you will need to change the foreach-DatabaseInServers code suitably.
If you haven’t got the latest SQLPS module, get it from Chad Miller’s ‘Making A SQLPS Module’ blog
Make sure that you have the SMO library installed!
in some cases I’ve just selected AdventureWorks, just for the demo. You won’t want that when you come to use it.
Use the code in the speechbubble at the top of the page
if you have only one item in the servers, blacklist or whitelist, still use the array syntax e.g. @('Adventureworks')

PowerShell SMO: Just Writing Things Once

DRY (Don’t repeat yourself)

Ten green bottles

the file groups and their files

Checking on the log files

AutoGrowth

VLFs

Checking when your databases were last backed up

Running a SQL Script on rather a lot of databases

Generating a single report from running the query

Generating a report for each database

Popping plenty of databases into source control

In case things don’t work.

Subscribe for more articles

Rate this article

Phil Factor

Related articles

Backing Up MySQL Part 7: MySQL Enterprise Backup

Database Subsetting and Data Extraction

Automating BCP with PowerShell

Tags