Forked from laurencee/Reverse engineer sql database.ps1
Created
July 23, 2018 03:53
-
-
Save gjonespf/4ba660388833cf3125638bb09e3b7088 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