Last active
March 30, 2021 13:46
-
-
Save aev-mambro2/668e028093c0b9ba81c1f7e142b73340 to your computer and use it in GitHub Desktop.
use-powershell-to-generate-db-regeneration-scripts
This file contains 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
#Based on the article by Phil Factor, 2012, for Redgate: | |
#https://www.red-gate.com/simple-talk/sql/db-administration/automated-script-generation-with-powershell-and-smo/ | |
Write-Host "This script writes 1 file that contains the SQL to regenerate an SQL Server Database. What file name should the script give it?"; | |
$save_chooser = New-Object -Typename System.Windows.Forms.SaveFileDialog; | |
$save_chooser.ShowDialog(); | |
$write_output_to=$save_chooser.FileName; | |
write-host "Saving to: " $write_output_to | |
$server_instance=(Read-Host -Prompt "Which server?"); | |
$db_name=(Read-Host -Prompt "Which database?"); | |
$user=Get-Credential -Message "Authenticate to $($server_instance):" -UserName "sa"; | |
$db=Get-SqlDatabase -ServerInstance $server_instance -Credential $user -Name $db_name -ErrorAction Continue; | |
if ($null -eq $db -or $db.name -ne $db_name){ | |
Write-host "Error: Could not find '$db_name' in $server_instance"; | |
return -1; | |
} | |
$transfer = new-object Microsoft.SqlServer.Management.Smo.Transfer $db; | |
if ($null -eq $transfer) { | |
Write-host "Error: failed to load Transfer class. See https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.transfer?view=sql-smo-160"; | |
return -2; | |
} | |
$transfer.CopySchema=$true; | |
$transfer.CopyData=$false; | |
$transfer.Options.AnsiPadding=$true; | |
$transfer.Options.ContinueScriptingOnError=$true; | |
$transfer.Options.ConvertUserDefinedDataTypesToBaseType=$false; | |
$transfer.Options.DriAll=$true; | |
$transfer.Options.ExtendedProperties=$true; | |
$transfer.Options.Filename=$write_output_to; | |
$transfer.Options.IncludeDatabaseContext=$true; | |
$transfer.Options.IncludeHeaders=$true; | |
$transfer.Options.IncludeIfNotExists=$true; | |
$transfer.Options.Indexes=$true; | |
$transfer.Options.Permissions=$true; | |
$transfer.Options.SchemaQualify=$true; | |
$transfer.Options.ScriptSchema=$true; | |
$transfer.Options.ScriptBatchTerminator=$true; | |
$transfer.Options.ToFileOnly=$true; | |
$transfer.Options.Triggers=$true; | |
$transfer.ScriptTransfer(); | |
Write-Host "All done. Wrote file to: $($transfer.Options.FileName)." |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The objective is to make reproducible scripts that regenerate a database structure, for testing and deployment.