Last active
September 4, 2022 08:43
-
-
Save laurencee/79963e148d1518d6488234bf0ea7a458 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Usage: powershell ExtractInitialDatabaseScript.ps1 "SERVERNAME" "DATABASE" "C:\<YourOutputPath>" | |
| function GenerateDBScript([string]$ServerName, [string]$Database, [string]$scriptpath) { | |
| $outFile = "$scriptpath\$($Database)_Inital_setup.sql" | |
| if (-not (Test-Path $scriptpath)) { | |
| [System.IO.Directory]::CreateDirectory($scriptpath) | |
| } | |
| If (Test-Path $outFile) { | |
| Remove-Item $outFile | |
| } | |
| If (Test-Path "$outFile.utf16") { | |
| Remove-Item "$outFile.utf16" | |
| } | |
| # Load SMO assembly, and if we're 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") $ServerName # attach to the server | |
| if ($srv.ServerType -eq $null) { # if it managed to find a server | |
| Write-Error "Sorry, but I couldn't find Server '$ServerName' " | |
| return | |
| } | |
| $scripter = new-object ("$My.Scripter") $srv # create the scripter | |
| $scripter.Options.IncludeDatabaseContext = $false | |
| $scripter.Options.IncludeIfNotExists = $true | |
| $scripter.Options.ToFileOnly = $true | |
| $scripter.Options.AllowSystemObjects = $false | |
| $scripter.Options.Permissions = $true | |
| $scripter.Options.DriAllConstraints = $true | |
| $scripter.Options.SchemaQualify = $true | |
| $scripter.Options.ScriptDrops = $false | |
| $scripter.Options.SchemaQualifyForeignKeysReferences = $true | |
| $scripter.Options.Indexes = $true | |
| $scripter.Options.DriAll = $true | |
| $scripter.Options.DriIndexes = $true | |
| $scripter.Options.DriClustered = $true | |
| $scripter.Options.DriNonClustered = $true | |
| $scripter.Options.NonClusteredIndexes = $true | |
| $scripter.Options.ClusteredIndexes = $true | |
| $scripter.Options.FullTextIndexes = $true | |
| $scripter.Options.Triggers = $true | |
| $scripter.Options.Permissions = $true | |
| $scripter.Options.EnforceScriptingOptions = $true | |
| $scripter.Options.WithDependencies = $true | |
| $scripter.Options.AppendToFile = $true | |
| $scripter.Options.FileName = "$outFile.utf16" | |
| $scripterNonTable = new-object ("$My.Scripter") $srv # create the scripter | |
| $scripterNonTable.Options.IncludeDatabaseContext = $false | |
| $scripterNonTable.Options.IncludeIfNotExists = $true | |
| $scripterNonTable.Options.ToFileOnly = $true | |
| $scripterNonTable.Options.AllowSystemObjects = $false | |
| $scripterNonTable.Options.SchemaQualify = $true | |
| $scripterNonTable.Options.Permissions = $true | |
| $scripterNonTable.Options.AppendToFile = $true | |
| $scripterNonTable.Options.FileName = "$outFile.utf16" | |
| # first we get the bitmap of all the object types we want | |
| $all = [long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all ` | |
| -bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure | |
| # and we store them in a datatable | |
| $d = new-object System.Data.Datatable | |
| # get everything except the servicebroker object, the information schema and system views | |
| $d = $srv.databases[$Database].EnumObjects([long]0x1FFFFFFF -band $all) | ` | |
| Where-Object { $_.Schema -ne 'sys' ` | |
| -and !($_.IsSystemObject) ` | |
| -and $_.Schema -ne "information_schema" } | |
| # and write out each scriptable object as a file in the directory you specify | |
| $d | FOREACH-OBJECT { # for every object we have in the datatable. | |
| Write-Host "$($_.DatabaseObjectTypes) : $($_.name -replace '[\\\/\:\.]','-')" | |
| if ($_.DatabaseObjectTypes -eq 'Table' ` | |
| -or $_.DatabaseObjectTypes -eq 'View' ) { | |
| $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection') | |
| $URNCollection.add($_.urn) | |
| $scripter.script($URNCollection) | |
| Write-Host "$($_.DatabaseObjectTypes) : $($_.name -replace '[\\\/\:\.]','-')" | |
| } | |
| if ($_.DatabaseObjectTypes -eq 'DatabaseRole' ` | |
| -or $_.DatabaseObjectTypes -eq 'Schema' ` | |
| -or $_.DatabaseObjectTypes -eq 'Synonym' ) { | |
| $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection') | |
| $URNCollection.add($_.urn) | |
| $scripterNonTable.script($URNCollection) | |
| Write-Host "$($_.DatabaseObjectTypes) : $($_.name -replace '[\\\/\:\.]','-')" | |
| } | |
| } | |
| #MSSQL outputs UTF16, which git will see as binary. This converts it to UTF8 so diff will work. | |
| Get-Content -en Unicode "$outFile.utf16" | Out-File -en utf8 "$outFile" | |
| Remove-Item "$outFile.utf16" | |
| } | |
| #============= | |
| # Execute | |
| #============= | |
| GenerateDBScript $args[0] $args[1] $args[2] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment