functions/private/Invoke-DBScriptObjects.ps1
|
################################################################################ # Function Script Out Database Object # # Function that accepts a Smo.Database-related object or an object with a valid # Urn property, then calls the already instantiated Scripter object to write # sql script to file. # # Args: $dbObj = Database object from either a "list" (generated by EnumObjects # method) or a database object with a Urn property (i.e. Table, # StoredProcedure, etc) ################################################################################ function ScriptOutDbObj($scripter, $dbObj, $SavePath, $WriteProgressActivity, $WriteProgressCount, $WriteProgressTotal ) { # Create a single element URN array $UrnCollection = New-Object ("Microsoft.SqlServer.Management.Smo.UrnCollection") $UrnCollection.Add($dbObj.Urn) # get the valid Urn.Type string for the file name $typeName = "$($UrnCollection.Item(0).Type)s" if ($typeName -ieq "Databases") { $typeName = "Database" } $ignore = $null # a regex variable be defined external to this script to allow ignoring types with matching names. EX: $ignoreSchemas = ".*?_" $ignore = Get-Variable -Name "ignore$typeName" -ValueOnly -ErrorAction SilentlyContinue if ($ignore -and $dbObj.Name -imatch $ignore) { Write-Verbose "IGNORING: $typeName - $($dbObj.Name)" return; } $tempPath = Resolve-Path $SavePath; if ("$($dbObj.Schema)".Length -gt 0) { $safeName = "$($dbObj.Schema).$($dbObj.Name).sql" } else { $safeName = "$($dbObj.Name).sql" } $tempPath = [IO.Path]::Combine($SavePath, $dbObj.Schema, $typeName, $safeName.Split([IO.Path]::GetInvalidFileNameChars()) -join '_') $dir = [IO.Path]::GetDirectoryName($tempPath) if (!(Test-Path $dir -PathType Container)) { New-Item $dir -ItemType Directory | Out-Null } ## tell the scripter object where to write it $scripter.Options.Filename = $tempPath $WriteProgressCount++ $partialPath = $scripter.Options.FileName -ireplace [regex]::escape($SavePath), "." Write-Progress -Activity $WriteProgressActivity ` -Status “Scripting file $WriteProgressCount of $WriteProgressTotal [$($partialPath)] ” ` -PercentComplete (([decimal]$WriteProgressCount / [decimal]$WriteProgressTotal) * 100.00) # a bit of progress reporting... Write-Information $scripter.Options.FileName try { #and write out the object to the specified file $scripter.Script($UrnCollection) } catch { Write-Warning "ERROR SCRIPTING ($($typeName)): $tempPath, EXCEPTION: $($_.Exception.GetBaseException().Message)" } # some sys items wont script out except as empty files, this is the easiest way to get rid of them if ((Get-Item $scripter.Options.Filename).length -le 0kb) { Remove-Item $scripter.Options.Filename -Verbose:$VerbosePreference } return $WriteProgressCount } # End of Function ScriptOutDbObj ################################################################################ |