I need this. You need this. We all need this. Source control all the things.
<#
.SYNOPSIS
Will script an object from SQL Server and CHECKIN/ADD to git.
.EXAMPLE
sql_script_to_git.ps1 -server yourservername -Database yourdatabasename -ScriptType "FULL" -Author yourgitname -Comment "full checkin of database"
#>
# Purpose - given parameters, script out an object from a SQL Server, using SMO, then check it into git.
#Scripting code gleefully copied from Phil Factor - the great parts that work are his, the errors are mine
# https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/
#Other code from others copied and attributed below.
#mdb 2013/12/03 adding ScriptType so that we can add "create database"-specific changes ONLY.
# To do FULLs you will have to have -ScriptType = 'Full' parameter. By default it will do objects.
#mdb 2013/12/05 trying to better bullet-proof the app against TF.exe failures, which appear to occur randomly
#mdb 2013/12/07 works great, though occasionally it cant script out an item. Trying to bulletproof that
#mdb 2013/12/16 broke the individual-item portion, fixed.
#mdb 2024/10/03 we're back, baby! Modifying to handle the sqlauth needed, the keyvault, and git instead of TFS.
#param has to be the first line of the script.
param(
[Parameter(Mandatory=$true,Position=1)]
[string]$Server,
[Parameter(Mandatory=$true,Position=2)]
[string]$Database,
[string]$ScriptType ='Object',
[string]$SchemaToScript,
[string]$ObjectToScript,
[string]$Author = 'powershellautomation0104',
[string]$Comment
)
#make sure not ObjectToScript blank if scripting out the entire DB; makes checkin better
if ($null -eq $SchemaToScript) {$SchemaToScript = ''}
if ($null -eq $ObjectToScript) {$ObjectToScript = ''}
#these are the parameters for testing
#$Server='myservername'# the server it is on
#$Database='mydbname' # the name of the database you want to script as objects
#$SchemaToScript = ''
#$ObjectToScript = ''
#$Author = 'mynameandemail'
#$ScriptType ='Full'
#$Comment = 'author test'
#setting up an error code for later
$myerror = 0
#getting the password from keyvault.
Connect-AzAccount -Tenant tenantnamegoeshere -Identity -AccountId acctnamehjere -Subscription subnamehere
$sqlusername = "sqlloginhere"
$sqlpassword = Get-AzKeyVaultSecret -VaultName "keyvaulthere" -Name $sqlusername -AsPlainText
if ($comment -eq '')
{ $comment = "No Comment Given"}
if ($author -eq '')
{ $author = "authorhere"}
#field is mandatory, if we dont know, use a known-valid.
$ServerNameClean = "$(($server -split "\.")[0] -replace '[\\\/]','__')"
clear
#writing this out for logging and troubleshooting. These are all the parameters except ScriptType
write-host $Server, $Database, $SchemaToScript, $ObjectToScript, $Author, $Comment
#Git workspace folder - whatever you set it up as on your server
$DirectoryToSaveTo='c:\repos\DatabaseSchema' # the directory where you want to store them, within the git repo
#doing a pull prior to checking in, due to "error: failed to push some refs to"
set-location $DirectoryToSaveTo
#handling crappy git responses as per https://github.com/dahlbyk/posh-git/issues/109
$env:GIT_REDIRECT_STDERR = '2>&1'
& 'C:\Program Files\git\bin\git.exe' pull origin master
# Load SMO assembly, and if we are running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
}
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null
set-psdebug -strict # catch a few extra bugs
$ErrorActionPreference = "stop"
$My='Microsoft.SqlServer.Management.Smo'
$srv = new-object ("$My.Server") $Server # attach to the server
$conContext = $srv.ConnectionContext
$conContext.LoginSecure = $false
$conContext.Login = $sqlusername
$conContext.Password = $sqlpassword
if ($srv.ServerType-eq $null) # if it managed to find a server
{
Write-Error "Sorry, but I could not find Server '$Server' "
return
}
$scripter = new-object ("$My.Scripter") $srv # create the scripter
#Add the various options we care about
$scripter.Options.ToFileOnly = $true
$scripter.Options.ExtendedProperties= $true # yes, we want these
$scripter.Options.DRIAll= $true # and all the constraints
$scripter.Options.Indexes= $true # Yup, these would be nice
$scripter.Options.Triggers= $true # This should be includede
$scripter.Options.AppendToFile = $False
$scripter.Options.AllowSystemObjects = $False
$scripter.Options.ClusteredIndexes = $True
$scripter.Options.DriAll = $True
$scripter.Options.ScriptDrops = $False
$scripter.Options.IncludeHeaders = $False #so you do not get the one line "scripted at..." which would be NOW.
#$scripter.Options.ToFileOnly = $True
#$scripter.Options.Indexes = $True
$scripter.Options.Permissions = $True
$scripter.Options.WithDependencies = $False
$scripter.Options.Bindings = $true
$scripter.Options.IncludeDatabaseRoleMemberships = $true
#phil factor does a bit where he checks it in separately, but i'm doing it this way. The only downside is that SMO won't script out database-level properties.
#################################################
#First, script out the database "create" itself.#
#################################################
if (($Database) -and $ObjectToScript -eq '') #if database has a value but there is no object, script out the DB.
{
$db_scripter = new-object ("$My.Scripter") $srv # script out the database creation
$db_scripter.options=$scripter.options # with the same options
$db_scripter.options.filename="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)\$($Database)_database_create.sql" # with the same options
#explcitly creating the path for the DB script here. We still need to do it for all the sub-types.
# Could probably move the $d declaration up here, but leaving it here for readability.
$SavePath="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)"
# create the directory if necessary (SMO does not).
if (!( Test-Path -path $SavePath )) # create it if not existing
{Try { New-Item $SavePath -type directory | out-null }
Catch [system.exception]{
Write-Error "error while creating '$SavePath' $_"
return
}
}
#putting in a try/catch so we get error messages if it breaks, and it can continue.
try
{
$db_scripter.Script($srv.Databases[$Database]) # do it
}
Catch
{
"Error Message trying to script out $SavePath\$Filename"
$error[0].Exception
}
###########################
## Scripting out Objects ##
###########################
# we now get all the object types except extended stored procedures
# first we get the bitmap of all the object types we want
# and we had to exclude a bunch in order to support Azure SQL Database/MI.
# tested by running stuff like this against every option in: https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databaseobjecttypes?view=sql-smo-160 (SMO DatabaseObjectTypes Enum)
# $srv.databases[$Database].EnumObjects( [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseEncryptionKey )
# -bxor in this case means "don't use these!". They are non-scriptable via Azure SQL DB's SMO, which makes sense since it can't include them. Unsure why SMO can't do DatabaseAuditSpecification, though.
$all =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::All `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseEncryptionKey `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseAuditSpecification `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::RemoteServiceBinding `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::SearchPropertyList `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceBroker `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceContract `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceQueue `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceRoute `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::MessageType `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Certificate `
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure #we're ignoring this as per the original version, don't remember why.
# and we store them in a datatable
$d = new-object System.Data.Datatable
# get almost everything; skipping most service broker, information_schema, system_views, certificates (cannot be scripted)
# there are other items that may need to be skipped, like SymmetricKeys in SSISDB
# Yes, I realize the irony in skipping SB given that it is powering this.
#putting in a try/catch so we get error messages if it breaks, and it can continue.
#and note that we're excluding stuff that's above, but also other stuff that does enumerate but we don't want.
try
{
$d=$srv.databases[$Database].EnumObjects( $all) | `
Where-Object {$_.Schema -ne 'sys'-and $_.Schema -ne "information_schema" -and $_.Schema -ne "INFORMATION_SCHEMA" -and $_.DatabaseObjectTypes -ne 'ServiceBroker' `
-and $_.DatabaseObjectTypes -ne 'Certificate' `
-and $_.DatabaseObjectTypes -ne 'MessageType' `
-and $_.DatabaseObjectTypes -ne 'ServiceContract' `
-and $_.DatabaseObjectTypes -ne 'ServiceQueue' `
-and $_.DatabaseObjectTypes -ne 'ServiceRoute' `
-and ($SchemaToScript -eq '' -or $_.Schema -eq $SchemaToScript) `
-and (($ObjectToScript -eq '' -and $ScriptType -eq 'Full') -or $_.Name -eq $ObjectToScript) `
-and (!(($_.DatabaseObjectTypes -eq 'Schema' -or $_.DatabaseObjectTypes -eq 'DatabaseRole') -and $_.Name -match "db(_)*" -and $_.Name -ne 'dbo')) `
-and ($_.Name -notmatch 'sp_MS*') }
# mdb 2013/11/07 previous line skips replication objects. This comment below code as comment lines break extended 1-liner.
# mdb 2024/10/03 previous line to that is because it would script out the db_datareader etc roles.
}
Catch
{
"Error Message trying to enumerate the database - may be logshipped or being restored"
$myerror = 1
$error[0].Exception
write-host $error
}
# List every item that we are going to do
$d = $d | sort
# $d | select databaseobjecttypes, schema, name
if ($d.Count -gt 10000)
{
"skipping the database objects - more than 10000"
}
# Now write out each scriptable object as a file in the directory you specify
#it appears that an empty array never actually enters the FOREACH, leaving variables unset
# -and -$d.Count -ne 0
if ($myerror -eq 0 -and $d.Count -lt 10001) #20k of objects takes up 5gb of RAM in the PS script and causes problems
{
$d| FOREACH-OBJECT { # for every object we have in the datatable.
#"" #blank line so each block of error messages is separated out
$SavePath="$($DirectoryToSaveTo)\$ServerNameClean\$Database\$($_.DatabaseObjectTypes)"
# create the directory if necessary (SMO does not).
if (!( Test-Path -path $SavePath )) # create it if not existing
{Try { New-Item $SavePath -type directory | out-null }
Catch [system.exception]{
Write-Error "error while creating '$SavePath' $_"
return
}
}
# tell the scripter object where to write it, and make sure it is actually writeable
if ($_.schema)
{
$Filename = "$($_.schema -replace '[\\\/\:\.]','-').$($_.name -replace '[\\\/\:\.\ ]','-').sql";
}
else
{
$Filename = "$($_.name -replace '[\\\/\:\.]','-').sql";
}
$scripter.Options.FileName = "$SavePath\$Filename"
$scripter.Options.FileName #print it out so we know what is being done
$UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')
$URNCollection.add($_.urn)
try
{
$scripter.script($URNCollection)
}
Catch
{
"Error Message trying to script out $SavePath\$Filename"
$error[0].Exception
}
#"script done"
}
}
}
<# GIT checkin - somewhat easier than before #>
Write-Host "Checking in of Files to Git "
Set-Location "$DirectoryToSaveTo"
#force commit and push all files
#git add .
#git commit -m "$($Comment)" --author="$($Author)"
#git push -f --porcelain #the two switches don't work together.
#git push --porcelain
#doing it this way since the container(?) account doesn't have the path for git.
#and this is so incredibly finicky. can't use -m and -c, have to have values set for -c since it's a network service account, etc.
#you need to run, as a job step,
#do NOT get clever with the path. putting it in quotes so you can say program files causes failures.Maybe.
& 'C:\Program Files\git\bin\git.exe' add .
& 'C:\Program Files\git\bin\git.exe' commit -m "$($Comment)" --author="$($Author)"
& 'C:\Program Files\git\bin\git.exe' push --porcelain
# -m "$($Comment)"
#go back to script directory
Set-Location $PSScriptRoot
No comments:
Post a Comment