I’ve recently decided I’ve got to get with the times and started dabbling with Powershell. Up until now i’ve not really seen any use (as a DBA) for it from a SQL Server perspective as I’ve been able to achieve pretty much everything in the past using TSQL, VBScript and/or SMO etc but the client stated they wanted Powershell to script out all the objects from all instances in their large estate.

I’ll not bore you too much with what its doing but in a nutshell it’ll loop a list of servers from a Central Management Server (CMS) but this can be amended to loop a textfile or indeed a text list. From this, the script will create a folder for the server, loop all databases (incl system but this can be turned off) creating a folder for each and generate a .sql file for procedures, views and functions each within their own folders within the database. You can add/remove the ability to script other objects such as tables, indexes etc but it wasn’t a requirement at the time so I’ve not included it.

I’m not going to pretend that this script is top notch but for a beginner I’m quite proud of it .

There are a number of similar scripts on the web but none of them did quite what I was after, hopefully It’ll be of some use to some.

Will I be using Powershell again? Definitely! I know I could have knocked this up quicker using other technologies but where’s the fun in that ????

And here it is, there are 2 scripts: One which is a function to get the objects called (funnily enough) getSQLObjects (fn_getSQLObjects.ps1) and the other is a script to get the server list and call the function (Run_getSQLObjects.ps1). These could have been combined and a parameter used to dictate whether to use text file, CMS or other method of listing the SQL Servers but I decided to keep them separate.

One other thing to note is that I used SQLPS to run this but the script could be tweaked to use a relevant connection to the CMS to get the serverlist.

fn_getSQLObjects.ps1:

##########################################################
# PowerShell Script of DB Objects
#
# Uses PS and SMO to script the drop and create a type of
# SQL Server object (views, tables, etc.) to individual 
# folders and files
#
# Chris Taylor07/09/2011
##########################################################
Function getSQLObjects ($SQLInstance, $DB, $ObjType, $OutputFile)
{
#List of schemas to exclude
$ExcludeSchemas = @("sys","Information_Schema") 
  
#Create smo objects##########################################
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
   
#SQL Instance & DB object
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance
$dbs=$server.Databases
   
#Set ScriptingOption object option
$ScriptingOption = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')        

$ScriptingOption.SchemaQualify = 1
$ScriptingOption.AllowSystemObjects = 0

#Script Drop Objects###########################################
$ScriptingOption.ScriptDrops = 1         
$ScriptingOption.IncludeIfNotExists = 1

#Create Directory for Database and ObjType
$chkFolder = $OutputFile + $DB + "\"
if (!(Test-Path -path $chkFolder))
{
New-Item ($chkFolder) -type directory
}
#write-host $chkFolder
#Create directory for object type within database folder
$chkFolder += $ObjType + "\"
if (!(Test-Path -path $chkFolder))
{
#write-host $chkFolder
New-Item ($chkFolder) -type directory
}

#Generate script for all objects##################################
foreach ($objects in $dbs[$DB].$ObjType)
{
If ($ExcludeSchemas -notcontains $objects.Schema )  
{
$filename = $chkFolder + $($objects.Schema) + "." + $($objects.name) + ".sql"
$objects.Script($ScriptingOption) + "`r GO `r " | out-File $filename 
}
}
#Script Create Objects#########################################
    #Append this to the file generated above with the Drop - possibly 
    #a better way to do it but works for me
$ScriptingOption.ScriptDrops = 0         
$ScriptingOption.IncludeIfNotExists = 0

foreach ($objects in $dbs[$DB].$ObjType)
{
If ($ExcludeSchemas -notcontains $objects.Schema )
{
$filename = $chkFolder + $($objects.Schema) + "." + $($objects.name) + ".sql"
$objects.Script($ScriptingOption) + "`r GO `r " | out-File $filename -Append
}
}
}

Run_getSQLObjects.ps1

##########################################################
# PowerShell Script of DB Objects
#
# Loop CMS passing in database list from a particular 
# server
#
# Chris Taylor07/09/2011
##########################################################
#Loop the CMS Server to list of Servers
foreach ($sqlserver in invoke-sqlcmd -query "SELECT svr.name
 FROM   msdb.dbo.sysmanagement_shared_registered_servers_internal svr
 INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal grp
             ON svr.server_group_id = grp.server_group_id")
{
$ExcludeServers = @("ALL","Servername")
If ($ExcludeServers -notcontains $sqlserver.name )  
{
write-host $sqlserver.name
$SQLInstance = $sqlserver.name 
$OutputFile = "C:\temp\"

#Create Folder for Server, note that this will create a sub folder for any sql instances on that server
#For example, servername\instance will have a folder for servername with a subfolder for instance
$OutputFile += $SQLInstance + "\"
if (!(Test-Path -path $OutputFile))
{
New-Item ($OutputFile) -type directory
}

#Create smo objects##########################################
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
   
#SQL Instance & DB object
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance
$dbs=$server.Databases

#Set what you want to script out
$Objects = @("StoredProcedures","Views","UserDefinedFunctions")

#Loop DBs
foreach ($db in $server.Databases)
{
#write-host $db.name
#Loop the object types
foreach ($objType in $Objects)
{
#write-host $objType + ' ' + $db.name + ' ' + $objType.name + ' ' + $OutputFile
getSQLObjects $SQLInstance $db.name $objType $OutputFile
}
}
}#Don't do anything if exceptions, could use a BREAK in the loop instead
}