use-powershell-to-generate-db-regeneration-scripts

use-powershell-to-generate-db-regeneration-scripts.ps

#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)."

Note: if the write_output_to file location can't be written to, the ScriptTransfer() method will fail without much of an explanation.

The objective is to make reproducible scripts that regenerate a database structure, for testing and deployment.