PowerShell SMO (SQL Server Management Objects) exposes SQL Server’s object model to PowerShell – databases, tables, files, jobs, users, permissions, backups, essentially everything accessible through SSMS. The practical value for a DBA is that operations written once against SMO can run unchanged across every database on every registered server, rather than being copy-pasted into SSMS sessions or script files per instance. <\/b>This article demonstrates the pattern through several progressively more complex examples.<\/b><\/p>\n\n\n\n
These examples include:<\/strong><\/p>\n\n\n\n (1) listing file groups and files across all databases on all registered servers; Each example is a few lines of PowerShell plus a filter for which servers or databases to target. The DRY principle (Don’t Repeat Yourself) applies to DBA automation as strongly as to application code – writing the operation once against SMO means the logic lives in one place, can be tested in one place, and runs consistently across the estate.<\/b><\/p>\n\n\n\n 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:<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n This does most routine tasks, but we need to add a few things, just as we had to with the poem. The 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.<\/p>\n 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.<\/p>\n You may have already categorised your databases with special prefix such a ‘Test’. In this case, you can use a different Or if you want to avoid them then<\/p>\n 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.<\/p>\n 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.<\/p>\n So, we’ll create the pipeline as an ‘advanced’ function<\/a>, so that we can provide help and allow ‘debug-printing’ and ‘voluble’ messages for debugging via So, without actually doing anything harmful to any database, lets\u00a0 test it out.<\/p>\n 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!)<\/p>\n This gives you a report that gives you the space available to each ‘Adventureworks’ database (or whatever you specify \u00a0on the servers you specify, broken down by data and index usage.<\/p>\n Here we drill down on detail to find out (for all our databases on all our registered servers this time)\u00a0 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.<\/p>\n We can also check on log files on all our databases for all our registered servers (or whatever you wish, of course).\u00a0 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.<\/p>\n We can also very simply look at the Virtual Log files for all of the databases’ transaction logs, using a DBCC LogInfo directly.<\/p>\n 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\u00a0 SMO: Database Properties<\/a> or even (I’ve just shown you how!) from the SMO: Server Properties<\/a>.<\/p>\n \u00a0<\/p>\n Well, that gets a lot of standard database reports out of the way, and with\u00a0 How about generating a build script for all the databases of your registered servers, that is saved to a directory corresponding to the server?<\/p>\n In this case, we’ll use That was pretty easy. \u00a0What about executing a SQL Script in a number of databases in a number of servers?\u00a0 Right away, sir. \u00a0<\/p>\n 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 \u00a0indexes. (here, we’ve just restricted it to Adventureworks)<\/p>\n 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.<\/p>\n 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.<\/p>\n 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..<\/p>\n
(2) checking log file sizes and last-autogrow events across the estate;
(3) verifying backup dates (finding databases that haven’t been backed up recently);
(4) running arbitrary T-SQL against multiple databases and aggregating results into a single report or per-database files;
(5) generating full source-control-ready script output for all objects in all databases. <\/b><\/p>\n\n\n\nIntroduction to PowerShell SMO for SQL Server<\/h2>\n\n\n\n
\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\n
<\/li>\nDRY (Don’t repeat yourself)<\/h2>\n
Ten green bottles<\/h3>\n
\t#The basic sequence\n\t$Sequence=\"ten\",\"nine\",\"eight\",\"seven\",\"six\",\"five\",\"four\",\"three\",\"two\",\"one\"\n\t\n\t$StartAt='Ten' #The number we want to start at. Try it out with other values.\n\t\n\t$action={param ($x,$Isfirst); #the scriptblock called for every item in the list\n\tif (-not $Isfirst) {\"there would be $x green bottles hanging on the wall.\"}\n\t@\"\n\t\n\tThere were $x green bottles hanging on the wall, \n\t$x green bottles hanging on the wall\n\tand if one green bottle should accidentally fall\n\t\"@\n\t}\n\t\n\t\n\t$Start={ \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $StartAt Green Bottles.`n\"} #Scriptblock called at start \n\t#and the one called after all items are processed\n\t$Finish={ \"there would be no green bottles hanging on the wall`n`n\u00a0\u00a0\u00a0 The End`n\" } \n\t#the scriptblock that decides whether the current item should be processed\n\t$Filter={ param ($x, $status); if ($x -eq $startAt) {$status = $true}; $status }\n\t\n\t#the actual pipeline. We will save the scriptblock literal in a variable which we can execute\n\t$pipeline={\n\t$Sequence|\n\t& { BEGIN{$ShouldOutput = $false} PROCESS{$ShouldOutput = $filter.invoke( $_, $ShouldOutput); if ($shouldOutput) {$_}} } |\n\t\u00a0\u00a0\u00a0 & {BEGIN{$first=$true; $Start.invoke($_) } PROCESS{$action.invoke($_, $first); $first=$false} END{$Finish.invoke($_) } }\n\t}\n\t$pipeline.invoke() #and we just invoke the pipeline\n\t\n\t<# 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 #>\n\t\n\t$action={param ($x,$Isfirst); #the scriptblock called for every item in the list\n\tif (-not $Isfirst) { \"Single beds were only made for $x\"}\n\t\"`nThere were $x in the bed and the little one said,\"\n\tif ($x -ne 'one') { @\" \n\t'Roll over, roll over!'.\n\tSo they all rolled over and one fell out\n\tAnd he gave a little scream and he gave a little shout\n\t'Please remember to tie a knot in your pajamas'\n\t\"@\n\t}\n\telse {for($ii = 1;$ii -le 3; $ii++) {\"I've got the whole mattress to myself\"}\n\t\"I've got the mattress to myself.\"}\n\t}\n\t\n\t$Start={ \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 There were $StartAt in the Bed.`n\"} #Scriptblock called at start \n\t#and the one called after all items are processed\n\t$Finish={ \"`n`n\u00a0\u00a0\u00a0 The End\" }\n\t#and we then just call the pipeline again!\n\t$pipeline.invoke() \n\u00a0\n<\/pre>\n\u00a0<\/h2>\n
\t$DataSources | # our list of servers\n\t& {PROCESS{$ServerFilter.invoke($_)}} | # choose which servers from the list\n\t\u00a0\u00a0 Foreach-object {new-object (\"$My.Server\") $_ } | # create an SMO server object\n\t\u00a0\u00a0\u00a0\u00a0 Where-Object {$_.ServerType -ne $null} | # did you positively get the server?\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 Foreach-object {$_.Databases } | #for every server successfully reached \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Where-Object {$_.IsSystemObject -ne $true} | #not the system objects\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 & {PROCESS{$Databasefilter.invoke($_)}} | # do all,avoid blacklist or do a whitelist etc\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 & {PROCESS{$JobToDo.invoke($_)}} #and do whatever you want for the database\n<\/pre>\n$Serverfilter<\/code> and $DatabaseFilter<\/code> are variables containing a scriptblock. They have a parameter, which is $_<\/code>, meaning the current object in the pipeline. This parameter isn’t strictly necessary, since it would work by using the $_<\/code> 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.<\/p>\n\t$DatabaseFilter=$TheDatabaseFilter\n\tif ($blacklist.count -gt 0) {$DatabaseFilter= { param($x); if ($blacklist -notcontains $x.name) {$x} }} \n\t# followed by the ones you don't want, listed in your blacklist\n\tif ($whitelist.count -gt 0) {$DatabaseFilter= { param($x); if ($whitelist -contains $x.name) {$x} }} \n\t# and one that just selects the files you specify in your whitelist\n\t<\/pre>\n$Databasefilter<\/code> filter to get only these,<\/p>\n\t{ param($x); if ($x.name -like 'Adv*'){$x}} <\/pre>\n\t{ param($x); if ($x.name -notlike 'Adv*'){$x}}<\/pre>\nWrite-Debug<\/code> and Write-Verbose<\/code>. This means that even your anonymous functions will\u00a0 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.<\/p>\n<\/div>\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t$DataSources=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance\n\tForeach-DatabaseInServers $Datasources | foreach-object{\"Found database '$($_.name)' on Server $($_.parent.name) \"} #test that you can cannect to your datasources \n\t<\/pre>\n\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\t\n\tForeach-DatabaseInServers $Servers\u00a0 `\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} |\n\t\u00a0\u00a0 Select-object @{Name=\"Server\"; Expression={$_.parent.name}}, Name, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage\u00a0 |ConvertTo-HTML \n\t<\/pre>\n\n\n
\n Server<\/th>\n Name<\/th>\n DataSpaceUsage<\/th>\n SpaceAvailable<\/th>\n Index SpaceUsage<\/th>\n<\/tr>\n \n Phil.Factor.com\\sql2005<\/td>\n AdventureWorks<\/td>\n 84160<\/td>\n 15280<\/td>\n 78008<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2005<\/td>\n AdventureworksClone<\/td>\n 66 824<\/td>\n 43320<\/td>\n 70696<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2005<\/td>\n AdventureWorksDW<\/td>\n 43632<\/td>\n 15992<\/td>\n 23096<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorks<\/td>\n 104744<\/td>\n 1040<\/td>\n 78992<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorksCopy<\/td>\n 869 36<\/td>\n 4992<\/td>\n 77464<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2012<\/td>\n AdventureWorksDW<\/td>\n 43744<\/td>\n 16200<\/td>\n 22536<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2012<\/td>\n AdventureWorksDW2008R2<\/td>\n 45544<\/td>\n 16168<\/td>\n 26944<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2014<\/td>\n AdventureWorksLT<\/td>\n 3112<\/td>\n 168<\/td>\n 1632<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2014<\/td>\n AdventureWorksLT2008R2<\/td>\n 3192<\/td>\n 16488<\/td>\n 1632<\/td>\n<\/tr>\n \n Robyn.Page.com\\sql2005<\/td>\n AdventureWorks<\/td>\n 86032<\/td>\n 2468856<\/td>\n 80656<\/td>\n<\/tr>\n \n Robyn.Page.com\\sql2005<\/td>\n AdventureWorksDW<\/td>\n 43616<\/td>\n 0<\/td>\n 23088<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n the file groups and their files<\/h3>\n
\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\tForeach-DatabaseInServers $Servers\u00a0 | \n\t\u00a0\u00a0 foreach-object{$_.FileGroups} | foreach-object{$_.files} |\n\t\u00a0\u00a0\u00a0\u00a0 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 |\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 format-table \n<\/pre>\nChecking on the log files<\/h3>\n
AutoGrowth<\/h4>\n
\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\tForeach-DatabaseInServers $Servers\u00a0 | \n\t\u00a0\u00a0 foreach-object{$_.Logfiles} |\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 Select-object @{Name=\"Server\"; Expression={$_.parent.parent.name}}, @{Name=\"Database\"; Expression={$_.parent.name}},\u00a0 Name, Filename, Growth, GrowthType, size, UsedSpace, MaxSize |\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 format-table \n<\/pre>\nVLFs<\/h4>\n
\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\tForeach-DatabaseInServers $Servers\u00a0 | \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Foreach-object {\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $Db=$_\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $_.ExecuteWithResults('dbcc loginfo').Tables[0] |\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Measure-Object -Minimum -Maximum -Average FileSize |\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select-object @{Name=\"Server\"; Expression={$Db.parent.name}}, \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Name=\"Database\"; Expression={$Db.name}},\u00a0 \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Name=\"No.VLFs\"; Expression={$_.Count}}, \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Name=\"MaxVLFSize(mb)\"; Expression={\"{0:n2}\" -f ($_.Maximum\/1MB)}}, \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Name=\"MinVLFSize(mb)\"; Expression={\"{0:n2}\" -f ($_.Minimum\/1MB)}}, \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Name=\"AverageVLF(mb)\"; Expression={\"{0:n2}\" -f ($_.Average\/1MB)}}\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } | Format-Table * -AutoSize\u00a0 \n<\/pre>\nChecking when your databases were last backed up<\/h3>\n
\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\tForeach-DatabaseInServers $Servers\u00a0 `\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} |\n\t\u00a0\u00a0 Select-object @{Name=\"Server\"; Expression={$_.parent.name}}, Name, RecoveryModel, LastBackupDate, LastLogBackupDate, LastDifferentialBackupDate\u00a0 |ConvertTo-HTML \n\t<\/pre>\n\n\n
\n Server<\/th>\n Name<\/th>\n Recovery<\/th>\n LastBackupDate<\/th>\n LastLogBackupDate<\/th>\n<\/tr>\n \n Phil.Factor.com\\sql2005<\/td>\n AdventureWorks<\/td>\n Full<\/td>\n 15\/11\/2012 14:26:00<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2005<\/td>\n AdventureworksClone<\/td>\n Full<\/td>\n 01\/01\/0001 00:00:00<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2005<\/td>\n AdventureWorksDW<\/td>\n Simple<\/td>\n 01\/01\/0001 00:00:00<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorks<\/td>\n Simple<\/td>\n 01\/12\/2012 09:14:59<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorksCopy<\/td>\n Full<\/td>\n 01\/12\/2012 09:15:02<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorksDW<\/td>\n Simple<\/td>\n 01\/12\/2012 09:15:04<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorksDW2008R2<\/td>\n Simple<\/td>\n 01\/12\/2012 09:15:06<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorksLT<\/td>\n Simple<\/td>\n 01\/12\/2012 09:15:06<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Phil.Factor.com\\sql2008<\/td>\n AdventureWorksLT2008R2<\/td>\n Simple<\/td>\n 01\/12\/2012 09:15:07<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Robyn.Page.com\\sql2005<\/td>\n AdventureWorks<\/td>\n Full<\/td>\n 20\/09\/2012 13:12:25<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n \n Robyn.Page.com\\sql2005<\/td>\n AdventureWorksDW<\/td>\n Simple<\/td>\n 01\/01\/0001 00:00:00<\/td>\n 01\/01\/0001 00:00:00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Select-Object,<\/code> you can do a lot with calculated fields. \u00a0<\/p>\n\u00a0<\/h3>\n
\t\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t\t#now fetch the list of all our registered servers\n\t\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\t\t\n\t\t<# here we\u00a0 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 #>\n\t\tForeach-DatabaseInServers $servers\u00a0 -whitelist @('AdventureWorks')\u00a0 -jobToDo {\n\t\t \u00a0\u00a0 param($database)\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 write-verbose \"Accessing the database '$($database.name)' on server $($database.parent.name)\"\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $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)}}) \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } | format-table \n\t<\/pre>\n\u00a0<\/h2>\n
$JobToDo<\/code>, which is a parameter that comes in handy if you like using such things as Write-Debug<\/code> and Write-Verbose<\/code> 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.\u00a0 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)<\/p>\n\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\t#and a list of the databases we don't want to be scripted\n\t$blacklist='Pubs','NorthWind','AdventureWorks','AdventureWorksDW','ReportServer','ReportServerTempDB' #the databases I don't want to do\n\t#where we want to store the scripts (each server\/instance a separate directory\n\t$Filepath='E:\\MyScriptsDirectory' # local directory to save build-scripts to\n\t#and do it\n\tForeach-DatabaseInServers -verbose $servers -blacklist $blacklist -jobToDo {param($database)\n\t\u00a0\u00a0 $directory=\"$($FilePath)\\$( $database.Parent.URN.GetAttribute('Name','Server') -replace '[\\\\\\\/\\:\\.]','-' )\";\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $transfer = new-object (\"$My.Transfer\") $database\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions = new-object (\"$My.ScriptingOptions\") \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.ExtendedProperties= $true # yes, we want these\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.DRIAll= $true # and all the constraints \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.Indexes= $true # Yup, these would be nice\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.Triggers= $true # This should be included when scripting a database\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.IncludeHeaders = $true; # of course\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.ToFileOnly = $true #no need of string output as well\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $CreationScriptOptions.Filename = \"$directory\\$($Database.name)_Build.sql\"; \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $transfer.options=$CreationScriptOptions # tell the transfer object of our preferences\n\t\u00a0\u00a0 write-verbose \"scripting '$($database.name)' in server '$($database.parent.name)' to $($CreationScriptOptions.Filename)\"\n\t\u00a0\u00a0 if (!(Test-Path -path \"$directory\"))\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Try { New-Item \"$directory\" -type directory | out-null } \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch [system.exception]{\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Error \"error while creating '$directory' \"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } \n\t\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\t\u00a0\u00a0 Try {$transfer.ScriptTransfer()}\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch [system.exception]{\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Error \"couldn't script to '$directory\\$($Database.name)_Build.sql' because of error (possibly an encrypted stored procedure\"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } \n\t\u00a0\u00a0 }\n\t'did that go well?'\n\t<\/pre>\nRunning a SQL Script on rather a lot of databases<\/h2>\n
Generating a single report from running the query<\/h3>\n
\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t#now fetch the list of all our registered servers\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\t$SQL=@'\n\t--Which of my tables don't have any indexes at all?\n\tSELECT @@Servername as [Server],DB_NAME() as [Database], \n\tDB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without any index]\n\tFROM sys.tables t WHERE OBJECTPROPERTY(object_id, 'TableHasIndex')=0\n\torder by [Tables without any index]\u00a0 \n\t'@\n\t\n\t<#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.#>\n\tForeach-DatabaseInServers $servers -TheDatabasefilter { param($x); if ($x.name -like 'Adv*' ){$x}}\u00a0 -jobToDo {\n\t\u00a0\u00a0 param($database)\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result=$database.ExecuteWithResults(\"$SQL\") #execute the SQL\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result.Tables[0]\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } | select-object ('Server','Database' ,'Tables without any index') | convertTo-html \n\t<\/pre>\nGenerating a report for each database<\/h3>\n
\t\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\t\t#now fetch the list of all our registered servers\n\t\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\t\t$Filepath='E:\\MyScriptsDirectory' # local directory to save the reports to\n\t\t$SQLTitle='All_Heaps_In_'\n\t\t$SQL=@'\n\t\t--Which of my tables don't have primary keys?\n\t\tSELECT @@Servername as [Server],DB_NAME() as [Database], --we'll do it via information_Schema\n\t\tTheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +TheTables.Table_Name AS [tables without primary keys]\n\t\tFROM\n\t\tINFORMATION_SCHEMA.TABLES TheTables\n\t\tLEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TheConstraints\n\t\t\u00a0\u00a0\u00a0 ON TheTables.table_Schema=TheConstraints.table_schema\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND TheTables.table_name=TheConstraints.table_name\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND constraint_type='PRIMARY KEY'\n\t\tWHERE table_Type='BASE TABLE'\n\t\tAND constraint_name IS NULL\n\t\tORDER BY [tables without primary keys]\n\t\t'@\n\t\t\n\t\t<#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.#>\n\t\tForeach-DatabaseInServers $servers -TheDatabasefilter { param($x); if ($x.name -like 'Adv*'){$x}} -jobToDo {\n\t\t \u00a0\u00a0 param($database)\n\t\t\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0 $databaseName=$database.name\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerName=$database.Parent.Name\n\t\t \u00a0\u00a0 $directory=\"$($FilePath)\\$($database.parent.name -replace '[\\\\\\\/\\:\\.]','-' )\";#create a directory\n\t\t \u00a0\u00a0 #and a handler for warnings and PRINT messages\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $database.parent.ConnectionContext.add_InfoMessage($handler);\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result=$database.ExecuteWithResults(\"$SQL\") #execute the SQL\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $database.parent.ConnectionContext.remove_InfoMessage($handler);\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (!(Test-Path -path \"$directory\")) #create the directory if necessary\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Try { New-Item \"$directory\" -type directory | out-null } \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch [system.exception]{\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Error \"error while creating '$directory' \"\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\t\t<# 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 #>\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $result.Tables[0]| convertto-csv -NoTypeInformation >\"$directory\\$SQLTitle$databasename.csv\"\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\t<\/pre>\nPopping plenty of databases into source control<\/h2>\n
\t\n\t$DirectoryToSaveTo='E:\\MyScriptsDirectory' # the directory where you want to store them\n\t$TheDatabasefilter = { param($x); if ($x.name -like 'Test*'){$x}} # just those starting with 'Phil'.\n\t\n\t. \".\\ForAllDatabaseServers.ps1\" # pull in our pipeline function 'Foreach-DatabaseInServers'\n\tImport-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\n\t#get a list of the servers we want to scan\n\t$servers= dir 'SQLSERVER:\\sqlregistration\\Database Engine Server Group' | foreach-object{$_.name}\n\t\n\t$ServiceBrokerTypes=@( 'MessageType','ServiceBroker','ServiceContract','ServiceQueue','ServiceRoute','RemoteServiceBinding')\n\t\n\t$JobToDo= {\n\t\u00a0\u00a0 $database=$_ \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $databaseName=$_.name\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerName=$_.Parent.URN.GetAttribute('Name','Server')\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 write-verbose \"scripting $databasename in $serverName\"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions = new-object (\"Microsoft.SqlServer.Management.Smo.ScriptingOptions\")\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.ExtendedProperties= $true # yes, we want these\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.DRIAll= $true # and all the constraints\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.Indexes= $true # Yup, these would be nice\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.ScriptBatchTerminator = $true # this only goes to the file\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.IncludeHeaders = $true; # of course\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.ToFileOnly = $true # no need of string output as well\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ScriptOptions.IncludeIfNotExists = $true # not necessary but makes script more versatile\n\t\u00a0\u00a0 $scrp=new-object (\"$My.Scripter\") $Database.parent\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $scrp.options=$ScriptOptions\n\t\u00a0\u00a0 $database.EnumObjects([long]0x1FFFFFFF -band [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all) | `\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 Where-Object {('sys','information_schema') -notcontains $_.Schema} | Foreach-Object { \n\t\u00a0\u00a0 $urn=\u00a0\u00a0\u00a0\u00a0\u00a0 [Microsoft.SqlServer.Management.Sdk.Sfc.Urn] $_.URN\u00a0\u00a0\u00a0 \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (('ExtendedStoredProcedure','ServiceBroker') -notcontains $urn.type)\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $currentPath=\"$DirectoryToSaveTo\\$($ServerName -replace '[\\\\\\\/\\:\\.]','-' )\\$($urn.GetAttribute('Name','Database') -replace '[\\\\\\\/\\:\\.]','-')\"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ( $ServiceBrokerTypes -contains $urn.type)\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {$fullPath=\"$currentPath\\ServiceBroker\\$($urn.type)\"}\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {$fullPath=\"$currentPath\\$($urn.type)\"}\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (!(Test-Path -path $fullPath ))\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Try { New-Item $fullPath -type directory | out-null } \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch [system.exception]{\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Write-Error \"error while creating '$fullPath' \"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 } \n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $scrp.options.FileName = \"$fullPath\\$($urn.GetAttribute('Schema')-replace '[\\\\\\\/\\:\\.]','-')-$($urn.GetAttribute('Name') -replace '[\\\\\\\/\\:\\.]','-').sql\"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $URNCollection.add($urn)\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 write-verbose \"writing script to $($scrp.options.FileName)\"\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $scrp.Script($URNCollection)\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\t\u00a0\u00a0 }\n\t\u00a0\u00a0\u00a0\u00a0 \n\t}\n\t\n\t$params = @{DataSources=$Servers;TheDatabaseFilter=$TheDatabasefilter;JobToDo=$JobToDo;verbose=$true}\n\tForeach-DatabaseInServers @Params\n\t\"done them, Master.\" \n<\/pre>\n\u00a0<\/h2>\n
\t\t\n\t\tFunction Foreach-DatabaseInServers {\n\t\t \u00a0\u00a0\u00a0 <#\n\t\t\u00a0\u00a0\u00a0 .SYNOPSIS\n\t\t\u00a0\u00a0\u00a0 Does the whatever scriptblock you wish for the databases in list of SQL server instances\n\t\t\u00a0\u00a0\u00a0 .DESCRIPTION\n\t\t\u00a0\u00a0\u00a0 This takes the scriptblock you define\u00a0 and executes it against every database that you specify, either by using a whitelist or a blacklist, or just does them all.\n\t\t\u00a0\u00a0\u00a0 .EXAMPLE\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $DataSources='Dave', 'Dee', 'Dozy', 'Beaky', 'Mick', 'Titch'\u00a0 # server name and instance\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $JobToDo ={param($database)\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $databaseName=$database.name\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerName=$database.Parent.Name\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"Do stuff to $databasename on $Servername\"}\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $whitelist=''#the only databases I want to do, if they're there(Leave empty otherwise)\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $blacklist='Pubs','NorthWind','AdventureWorks','AdventureWorksDW','ReportServer','ReportServerTempDB' #the databases I don't want to do\n\t\t\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Foreach-DatabaseInServers $Datasources\u00a0 $jobToDo $whitelist $blacklist\n\t\t\u00a0\u00a0\u00a0 .PARAMETER DataSources\n\t\t\u00a0\u00a0\u00a0 The list of servers that you want the files from \n\t\t\u00a0\u00a0\u00a0 .PARAMETER JobToDo\n\t\t\u00a0\u00a0\u00a0 The script of the job you want done\n\t\t\u00a0\u00a0\u00a0 .PARAMETER Whitelist\n\t\t\u00a0\u00a0\u00a0 The list of databases you want the action performed on, leaving out all others\n\t\t\u00a0\u00a0\u00a0 .PARAMETER Blacklist\n\t\t\u00a0\u00a0\u00a0 The list of databases you don't want the action performed on\n\t\t\u00a0\u00a0\u00a0 .PARAMETER TheServerFilter\n\t\t\u00a0\u00a0\u00a0 Any filter you specify for servers\n\t\t\u00a0\u00a0 .PARAMETER TheDatabaseFilter\n\t\t\u00a0\u00a0\u00a0 Any filter you specify for databases\n\t\t\u00a0\u00a0\u00a0 .PARAMETER Initialisation\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0any action that needs to be done first before the pipeline\n\t\t\u00a0\u00a0 #>\n\t\t \u00a0\u00a0 param([CmdletBinding()]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The list of databases\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$True,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=0,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='the list of one or more SQL Server instances you would like to target')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [array] $DataSources,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The Job To Do \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$false,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=1,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='The actual job you want to do in each database')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [scriptblock] $JobToDo ={ param($x); $x},\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The WhiteList\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$false,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=2,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='the databases in each instance that you want to select')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [array] $Whitelist=$null,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The BlackList\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$false,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=3,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='the databases in each instance that you dont want to select')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [array] $Blacklist=$null,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The custom database filter you want to use \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$false,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=4,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='if the whitelist or blacklist is no good for what you want')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [scriptblock] $TheServerFilter={ param($x); $x }, # define a filter that does nothing by default\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The custom server filter you want to use \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$false,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=5,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='if you wish to select from all of the databases')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [scriptblock] $TheDatabaseFilter={ param($x); $x },# define a filter that does nothing by default\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # The custom server filter you want to use \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Mandatory=$false,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Position=6,\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HelpMessage='any initialisation you want (with SMO loaded)')]\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [scriptblock] $Initialisation={}# define an initialisation routine that does nothing by default\n\t\t \u00a0 \n\t\t\n\t\t \u00a0\u00a0\u00a0\u00a0 )\n\t\t# set \"Option Explicit\" to catch subtle errors\n\t\tset-psdebug -strict\n\t\t\n\t\t\u00a0\n\t\t# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries\n\t\t$ms='Microsoft.SqlServer'\n\t\t$v = [System.Reflection.Assembly]::LoadWithPartialName( \"$ms.SMO\")\n\t\tif ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {\n\t\t[System.Reflection.Assembly]::LoadWithPartialName(\"$ms.SMOExtended\") | out-null\n\t\t \u00a0\u00a0 }\n\t\t[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null\n\t\t\n\t\t$My=\"$ms.Management.Smo\" #\n\t\t$DatabaseFilter=$TheDatabaseFilter\n\t\tif ($blacklist.count -gt 0) {$DatabaseFilter= { param($x); if ($blacklist -notcontains $x.name) {$x} }} \n\t\t# followed by the ones you don't want, listed in your blacklist\n\t\tif ($whitelist.count -gt 0) {$DatabaseFilter= { param($x); if ($whitelist -contains $x.name) {$x} }} \n\t\t# and one that just selects the files you specify in your whitelist\n\t\t\n\t\twrite-verbose \"The database filter is '$DatabaseFilter'\"\n\t\t\n\t\t$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.) #>\n\t\twrite-verbose \"Using server list $DataSources\";\n\t\t$DataSources | # our list of servers\n\t\t \u00a0 & {PROCESS{$TheServerFilter.invoke($_)}}\u00a0 | # choose which servers from the list\n\t\t \u00a0\u00a0 Foreach-object { write-verbose \"Accessing $($_.ToString())\"; new-object (\"$My.Server\") $_ } | # create an SMO server object\n\t\t \u00a0\u00a0\u00a0\u00a0 Where-Object {$_.ServerType -ne $null} | # did you positively get the server?\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0 Foreach-object { write-verbose \"reached $($_.ToString())\";$_.Databases } | #for every server successfully reached \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Foreach-object { write-verbose \"found database $($_.name)\"; $_} | #display each database \n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Where-Object {$_.IsSystemObject -ne $true} | #not the system objects\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 & {PROCESS{$DatabaseFilter.invoke($_)}}\u00a0 | # do all,avoid blacklist or do a whitelist etc\n\t\t \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 & {PROCESS{$JobToDo.invoke($_)}}\u00a0 #and do whatever you want for the database\n\t\t}<\/pre>\nIn case things don’t work…<\/h2>\n
\n
$DataSources=@('Dave','Dee','Dosy','Beaky','Mick','Tich') # server name and instance<\/code>
into a list of your servers.<\/li>\n\u00a0e.g. @('Adventureworks')<\/code><\/li>\n<\/ul>\n<\/div>\n\n\n\n