Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chris's SQL Blog

SQL Server Consultant and Exceptional DBA Awards 2011 Finalist with extensive experience in the financial, health and retail sectors. I’m extremely strong in all areas of SQL Server and specialise in Security but really get my kicks from System Optimisation and Performance Tuning. Being a DBA is a thankless task at times but sadly I do enjoy it. Any spare time I have (when not playing rugby, football, cricket, in the gym or spending time with the family) I’m trying to help out others via social network sites such as the #sqlhelp hastag on Twitter (follow me @billysassage),www.sqlservercentral.com (ChrisTaylor) or by attending and contributing to the UK SQL User Groups (www.sqlserverfaq.com) held in Manchester and Leeds.

Powershell to Script out SQL Server Objects using a CMS list

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 Taylor		07/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 Taylor		07/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
}


Comments

Leave a comment on the original post [chrisjarrintaylor.co.uk, opens in a new window]

Loading comments...