Export-CompleteDatabase.ps1

# ..\Export-CompleteDatabase-Async.ps1 -ServerInstance "YourServer" -DatabaseNames "DB1","DB2","DB3" -OutputPath "C:\DatabaseScripts" -MaxConcurrentJobs 3
# The script handles system objects by default (with the -IncludeSystemObjects switch to include them if needed) 
# and provides comprehensive coverage of all SQL Server database objects.

# Parameters for the SQL Server connection and output location
param (
    [string]$ServerInstance = "YourServerName",
    [string[]]$DatabaseNames = @("DB1", "DB2"),  # Provide database names to script
    [string]$OutputPath = "C:\DatabaseScripts",  # Where to save the scripts
    [PSCredential]$Credential = $null,           # Optional credentials
    [switch]$IncludeSystemObjects = $false,      # Whether to include system objects
    [int]$MaxConcurrentJobs = 4                  # Maximum number of concurrent jobs
)

# Load SQL Server module
if (-not (Get-Module -ListAvailable -Name SqlServer)) {
    Write-Warning "SqlServer module not found. Installing..."
    Install-Module -Name SqlServer -Force -AllowClobber
}
Import-Module SqlServer

# Create output directory if it doesn't exist
if (-not (Test-Path $OutputPath)) {
    New-Item -ItemType Directory -Path $OutputPath | Out-Null
    Write-Host "Created output directory: $OutputPath"
}

# Connection parameters
$connectionParams = @{
    ServerInstance = $ServerInstance
}
if ($Credential) {
    $connectionParams.Credential = $Credential
}

# Define the scripting function that will be run as a job
$scriptingFunction = {
    param (
        [string]$ServerInstance,
        [string]$DatabaseName,
        [string]$OutputPath,
        [bool]$IncludeSystemObjects,
        [PSCredential]$Credential = $null
    )
    
    # Import the required module in the job context
    Import-Module SqlServer
    
    # Create database folder
    $dbFolder = Join-Path $OutputPath $DatabaseName
    if (-not (Test-Path $dbFolder)) {
        New-Item -ItemType Directory -Path $dbFolder | Out-Null
    }
    
    # Connect to the SQL Server
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance
    if ($Credential) {
        $server.ConnectionContext.LoginSecure = $false
        $server.ConnectionContext.Login = $Credential.UserName
        $server.ConnectionContext.SecurePassword = $Credential.Password
    }
    
    # Get database object
    $database = $server.Databases[$DatabaseName]
    
    if (-not $database) {
        Write-Warning "Database '$DatabaseName' not found. Skipping..."
        return
    }
    
    Write-Host "Processing database: $DatabaseName" -ForegroundColor Cyan
    
    # Create base scripting options
    $baseScriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $baseScriptingOptions.IncludeHeaders = $true
    $baseScriptingOptions.ScriptOwner = $true
    $baseScriptingOptions.AllowSystemObjects = $IncludeSystemObjects
    $baseScriptingOptions.Permissions = $true
    $baseScriptingOptions.NoCommandTerminator = $false
    $baseScriptingOptions.DriAll = $true
    $baseScriptingOptions.ScriptForCreateDrop = $true
    $baseScriptingOptions.ScriptForAlter = $false
    $baseScriptingOptions.AnsiFile = $true
    $baseScriptingOptions.EnforceScriptingOptions = $true
    $baseScriptingOptions.WithDependencies = $false  # Set to true for dependencies, but could make scripts very large
    
    # 0. Script Database Creation
    $databaseCreationFile = Join-Path $dbFolder "00_Database_Creation.sql"
    Write-Host "  Scripting database creation: $DatabaseName"
    
    $dbScriptOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $dbScriptOptions.IncludeHeaders = $true
    $dbScriptOptions.ScriptData = $false
    $dbScriptOptions.ScriptDrops = $false
    $dbScriptOptions.IncludeIfNotExists = $true
    $dbScriptOptions.Default = $true
    $dbScriptOptions.FileName = $databaseCreationFile
    
    # Script the database creation
    $database.Script($dbScriptOptions) | Out-File -FilePath $databaseCreationFile -Encoding unicode
    
    # Add filegroups and files to the database creation script
    $fileGroupsAndFiles = @"

-- Add Filegroups and Files
USE [master]
GO

"@
    
    foreach ($fileGroup in $database.FileGroups) {
        $fileGroupsAndFiles += @"
ALTER DATABASE [$($database.Name)] ADD FILEGROUP [$($fileGroup.Name)]
GO

"@
        foreach ($file in $fileGroup.Files) {
            $fileGroupsAndFiles += @"
ALTER DATABASE [$($database.Name)] ADD FILE (
    NAME = N'$($file.Name)',
    FILENAME = N'$($file.FileName)',
    SIZE = ${$file.Size}KB,
    FILEGROWTH = ${$file.Growth}KB
) TO FILEGROUP [$($fileGroup.Name)]
GO

"@
        }
    }
    
    # Add log files
    foreach ($logFile in $database.LogFiles) {
        $fileGroupsAndFiles += @"
ALTER DATABASE [$($database.Name)] ADD LOG FILE (
    NAME = N'$($logFile.Name)',
    FILENAME = N'$($logFile.FileName)',
    SIZE = ${$logFile.Size}KB,
    FILEGROWTH = ${$logFile.Growth}KB
)
GO

"@
    }
    
    # Add database options
    $databaseOptions = @"

-- Database Options
USE [master]
GO
ALTER DATABASE [$($database.Name)] SET RECOVERY $($database.RecoveryModel)
GO
ALTER DATABASE [$($database.Name)] SET MULTI_USER
GO
ALTER DATABASE [$($database.Name)] SET READ_WRITE
GO
-- Set compatibility level
ALTER DATABASE [$($database.Name)] SET COMPATIBILITY_LEVEL = $($database.CompatibilityLevel)
GO

"@
    
    # Add filegroups, files and options to the database creation script
    Add-Content -Path $databaseCreationFile -Value $fileGroupsAndFiles
    Add-Content -Path $databaseCreationFile -Value $databaseOptions
    
    # 1. Script Schemas
    $schemasFolder = Join-Path $dbFolder "01_Schemas"
    if (-not (Test-Path $schemasFolder)) {
        New-Item -ItemType Directory -Path $schemasFolder | Out-Null
    }
    
    foreach ($schema in $database.Schemas) {
        if ($schema.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $schemasFolder "$($schema.Name).sql"
        Write-Host "  Scripting schema: $($schema.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $schema.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 2. Script User-Defined Types
    $typesFolder = Join-Path $dbFolder "02_UserDefinedTypes"
    if (-not (Test-Path $typesFolder)) {
        New-Item -ItemType Directory -Path $typesFolder | Out-Null
    }
    
    # Scalar User-Defined Types
    foreach ($type in $database.UserDefinedDataTypes) {
        if ($type.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $typesFolder "ScalarType_$($type.Schema).$($type.Name).sql"
        Write-Host "  Scripting scalar type: $($type.Schema).$($type.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $type.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # Table-Valued User-Defined Types
    foreach ($tabletype in $database.UserDefinedTableTypes) {
        if ($tabletype.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $typesFolder "TableType_$($tabletype.Schema).$($tabletype.Name).sql"
        Write-Host "  Scripting table type: $($tabletype.Schema).$($tabletype.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $tabletype.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 3. Script Tables
    $tablesFolder = Join-Path $dbFolder "03_Tables"
    if (-not (Test-Path $tablesFolder)) {
        New-Item -ItemType Directory -Path $tablesFolder | Out-Null
    }
    
    foreach ($table in $database.Tables) {
        if ($table.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $tablesFolder "$($table.Schema).$($table.Name).sql"
        Write-Host "  Scripting table: $($table.Schema).$($table.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        $scriptingOptions.ClusteredIndexes = $true
        $scriptingOptions.NonClusteredIndexes = $true
        $scriptingOptions.PrimaryObject = $true
        $scriptingOptions.ForeignKeys = $true
        $scriptingOptions.DriAll = $true
        $scriptingOptions.Indexes = $true
        $scriptingOptions.Triggers = $true
        $scriptingOptions.Statistics = $true
        
        $table.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 4. Script Views
    $viewsFolder = Join-Path $dbFolder "04_Views"
    if (-not (Test-Path $viewsFolder)) {
        New-Item -ItemType Directory -Path $viewsFolder | Out-Null
    }
    
    foreach ($view in $database.Views) {
        if ($view.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $viewsFolder "$($view.Schema).$($view.Name).sql"
        Write-Host "  Scripting view: $($view.Schema).$($view.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        $scriptingOptions.Indexes = $true
        $scriptingOptions.Triggers = $true
        
        $view.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 5. Script Functions
    $funcsFolder = Join-Path $dbFolder "05_Functions"
    if (-not (Test-Path $funcsFolder)) {
        New-Item -ItemType Directory -Path $funcsFolder | Out-Null
    }
    
    foreach ($func in $database.UserDefinedFunctions) {
        if ($func.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $funcsFolder "$($func.Schema).$($func.Name).sql"
        Write-Host "  Scripting function: $($func.Schema).$($func.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $func.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 6. Script Stored Procedures
    $procsFolder = Join-Path $dbFolder "06_StoredProcedures"
    if (-not (Test-Path $procsFolder)) {
        New-Item -ItemType Directory -Path $procsFolder | Out-Null
    }
    
    foreach ($proc in $database.StoredProcedures) {
        if ($proc.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $procsFolder "$($proc.Schema).$($proc.Name).sql"
        Write-Host "  Scripting stored procedure: $($proc.Schema).$($proc.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $proc.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 7. Script Triggers (Database level)
    $triggersFolder = Join-Path $dbFolder "07_DatabaseTriggers"
    if (-not (Test-Path $triggersFolder)) {
        New-Item -ItemType Directory -Path $triggersFolder | Out-Null
    }
    
    foreach ($trigger in $database.Triggers) {
        if ($trigger.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $triggersFolder "$($trigger.Name).sql"
        Write-Host "  Scripting database trigger: $($trigger.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $trigger.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 8. Script Assemblies
    $assembliesFolder = Join-Path $dbFolder "08_Assemblies"
    if (-not (Test-Path $assembliesFolder)) {
        New-Item -ItemType Directory -Path $assembliesFolder | Out-Null
    }
    
    foreach ($assembly in $database.Assemblies) {
        if ($assembly.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $assembliesFolder "$($assembly.Name).sql"
        Write-Host "  Scripting assembly: $($assembly.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $assembly.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 9. Script Synonyms
    $synonymsFolder = Join-Path $dbFolder "09_Synonyms"
    if (-not (Test-Path $synonymsFolder)) {
        New-Item -ItemType Directory -Path $synonymsFolder | Out-Null
    }
    
    foreach ($synonym in $database.Synonyms) {
        if ($synonym.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $synonymsFolder "$($synonym.Schema).$($synonym.Name).sql"
        Write-Host "  Scripting synonym: $($synonym.Schema).$($synonym.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $synonym.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 10. Script Sequences
    $sequencesFolder = Join-Path $dbFolder "10_Sequences"
    if (-not (Test-Path $sequencesFolder)) {
        New-Item -ItemType Directory -Path $sequencesFolder | Out-Null
    }
    
    foreach ($sequence in $database.Sequences) {
        if ($sequence.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $sequencesFolder "$($sequence.Schema).$($sequence.Name).sql"
        Write-Host "  Scripting sequence: $($sequence.Schema).$($sequence.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $sequence.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 11. Script User-Defined Aggregates
    $aggregatesFolder = Join-Path $dbFolder "11_UserDefinedAggregates"
    if (-not (Test-Path $aggregatesFolder)) {
        New-Item -ItemType Directory -Path $aggregatesFolder | Out-Null
    }
    
    foreach ($aggregate in $database.UserDefinedAggregates) {
        if ($aggregate.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $aggregatesFolder "$($aggregate.Schema).$($aggregate.Name).sql"
        Write-Host "  Scripting user-defined aggregate: $($aggregate.Schema).$($aggregate.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $aggregate.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 12. Script Database Security - Roles
    $securityFolder = Join-Path $dbFolder "12_Security"
    $rolesFolder = Join-Path $securityFolder "Roles"
    if (-not (Test-Path $rolesFolder)) {
        New-Item -ItemType Directory -Path $rolesFolder -Force | Out-Null
    }
    
    foreach ($role in $database.Roles) {
        if ($role.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $rolesFolder "$($role.Name).sql"
        Write-Host "  Scripting database role: $($role.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $role.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 13. Script Database Security - Users
    $usersFolder = Join-Path $securityFolder "Users"
    if (-not (Test-Path $usersFolder)) {
        New-Item -ItemType Directory -Path $usersFolder -Force | Out-Null
    }
    
    foreach ($user in $database.Users) {
        if ($user.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $usersFolder "$($user.Name).sql"
        Write-Host "  Scripting database user: $($user.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $user.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 14. Script Database Security - Application Roles
    $appRolesFolder = Join-Path $securityFolder "ApplicationRoles"
    if (-not (Test-Path $appRolesFolder)) {
        New-Item -ItemType Directory -Path $appRolesFolder -Force | Out-Null
    }
    
    foreach ($appRole in $database.ApplicationRoles) {
        if ($appRole.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $appRolesFolder "$($appRole.Name).sql"
        Write-Host "  Scripting application role: $($appRole.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $appRole.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 15. Script XML Schema Collections
    $xmlSchemaFolder = Join-Path $dbFolder "13_XMLSchemaCollections"
    if (-not (Test-Path $xmlSchemaFolder)) {
        New-Item -ItemType Directory -Path $xmlSchemaFolder | Out-Null
    }
    
    foreach ($xmlSchema in $database.XmlSchemaCollections) {
        if ($xmlSchema.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $xmlSchemaFolder "$($xmlSchema.Schema).$($xmlSchema.Name).sql"
        Write-Host "  Scripting XML schema collection: $($xmlSchema.Schema).$($xmlSchema.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $xmlSchema.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 16. Script Partition Functions
    $partitionFolder = Join-Path $dbFolder "14_Partitioning"
    $partFuncFolder = Join-Path $partitionFolder "PartitionFunctions"
    if (-not (Test-Path $partFuncFolder)) {
        New-Item -ItemType Directory -Path $partFuncFolder -Force | Out-Null
    }
    
    foreach ($partFunc in $database.PartitionFunctions) {
        $fileName = Join-Path $partFuncFolder "$($partFunc.Name).sql"
        Write-Host "  Scripting partition function: $($partFunc.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $partFunc.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 17. Script Partition Schemes
    $partSchemeFolder = Join-Path $partitionFolder "PartitionSchemes"
    if (-not (Test-Path $partSchemeFolder)) {
        New-Item -ItemType Directory -Path $partSchemeFolder -Force | Out-Null
    }
    
    foreach ($partScheme in $database.PartitionSchemes) {
        $fileName = Join-Path $partSchemeFolder "$($partScheme.Name).sql"
        Write-Host "  Scripting partition scheme: $($partScheme.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $partScheme.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 18. Script Full-Text Catalogs
    $ftFolder = Join-Path $dbFolder "15_FullText"
    $ftCatalogFolder = Join-Path $ftFolder "Catalogs"
    if (-not (Test-Path $ftCatalogFolder)) {
        New-Item -ItemType Directory -Path $ftCatalogFolder -Force | Out-Null
    }
    
    foreach ($ftCatalog in $database.FullTextCatalogs) {
        $fileName = Join-Path $ftCatalogFolder "$($ftCatalog.Name).sql"
        Write-Host "  Scripting full-text catalog: $($ftCatalog.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $ftCatalog.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 19. Script Full-Text Stoplists
    $ftStoplistFolder = Join-Path $ftFolder "Stoplists"
    if (-not (Test-Path $ftStoplistFolder)) {
        New-Item -ItemType Directory -Path $ftStoplistFolder -Force | Out-Null
    }
    
    foreach ($ftStoplist in $database.FullTextStopLists) {
        $fileName = Join-Path $ftStoplistFolder "$($ftStoplist.Name).sql"
        Write-Host "  Scripting full-text stoplist: $($ftStoplist.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $ftStoplist.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 20. Script Service Broker Objects
    $brokerFolder = Join-Path $dbFolder "16_ServiceBroker"
    
    # 20a. Message Types
    $msgTypesFolder = Join-Path $brokerFolder "MessageTypes"
    if (-not (Test-Path $msgTypesFolder)) {
        New-Item -ItemType Directory -Path $msgTypesFolder -Force | Out-Null
    }
    
    foreach ($msgType in $database.ServiceBroker.MessageTypes) {
        if ($msgType.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $msgTypesFolder "$($msgType.Name).sql"
        Write-Host "  Scripting message type: $($msgType.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $msgType.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 20b. Contracts
    $contractsFolder = Join-Path $brokerFolder "Contracts"
    if (-not (Test-Path $contractsFolder)) {
        New-Item -ItemType Directory -Path $contractsFolder -Force | Out-Null
    }
    
    foreach ($contract in $database.ServiceBroker.ServiceContracts) {
        if ($contract.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $contractsFolder "$($contract.Name).sql"
        Write-Host "  Scripting service contract: $($contract.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $contract.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 20c. Queues
    $queuesFolder = Join-Path $brokerFolder "Queues"
    if (-not (Test-Path $queuesFolder)) {
        New-Item -ItemType Directory -Path $queuesFolder -Force | Out-Null
    }
    
    foreach ($queue in $database.ServiceBroker.Queues) {
        if ($queue.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $queuesFolder "$($queue.Schema).$($queue.Name).sql"
        Write-Host "  Scripting queue: $($queue.Schema).$($queue.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $queue.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 20d. Services
    $servicesFolder = Join-Path $brokerFolder "Services"
    if (-not (Test-Path $servicesFolder)) {
        New-Item -ItemType Directory -Path $servicesFolder -Force | Out-Null
    }
    
    foreach ($service in $database.ServiceBroker.Services) {
        if ($service.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $servicesFolder "$($service.Name).sql"
        Write-Host "  Scripting service: $($service.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $service.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 20e. Remote Service Bindings
    $bindingsFolder = Join-Path $brokerFolder "RemoteServiceBindings"
    if (-not (Test-Path $bindingsFolder)) {
        New-Item -ItemType Directory -Path $bindingsFolder -Force | Out-Null
    }
    
    foreach ($binding in $database.ServiceBroker.RemoteServiceBindings) {
        $fileName = Join-Path $bindingsFolder "$($binding.Name).sql"
        Write-Host "  Scripting remote service binding: $($binding.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $binding.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 20f. Routes
    $routesFolder = Join-Path $brokerFolder "Routes"
    if (-not (Test-Path $routesFolder)) {
        New-Item -ItemType Directory -Path $routesFolder -Force | Out-Null
    }
    
    foreach ($route in $database.ServiceBroker.Routes) {
        if ($route.IsSystemObject -and -not $IncludeSystemObjects) { continue }
        
        $fileName = Join-Path $routesFolder "$($route.Name).sql"
        Write-Host "  Scripting route: $($route.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $route.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 21. Script Plan Guides
    $planGuidesFolder = Join-Path $dbFolder "17_PlanGuides"
    if (-not (Test-Path $planGuidesFolder)) {
        New-Item -ItemType Directory -Path $planGuidesFolder | Out-Null
    }
    
    foreach ($planGuide in $database.PlanGuides) {
        $fileName = Join-Path $planGuidesFolder "$($planGuide.Name).sql"
        Write-Host "  Scripting plan guide: $($planGuide.Name)"
        
        $scriptingOptions = $baseScriptingOptions.Clone()
        
        $planGuide.Script($scriptingOptions) | Out-File -FilePath $fileName -Encoding unicode
    }
    
    # 22. Script Database Level Permissions
    $permissionsFile = Join-Path $securityFolder "DatabasePermissions.sql"
    Write-Host "  Scripting database permissions"
    
    $dbPerms = @"
-- Database Permissions
USE [$($database.Name)]
GO

"@
    
    # Get database level permissions
    $scriptingOptions = $baseScriptingOptions.Clone()
    $scriptingOptions.Permissions = $true
    $scriptingOptions.NoCommandTerminator = $false
    
    # Get all permission statements
    $permissions = $database.EnumDatabasePermissions() | ForEach-Object {
        "GRANT $($_.PermissionState) ON DATABASE::[$($database.Name)] TO [$($_.Grantee)]"
    }
    
    $dbPerms += $permissions -join "`r`nGO`r`n"
    $dbPerms += "`r`nGO`r`n"
    
    # Write permissions to file
    $dbPerms | Out-File -FilePath $permissionsFile -Encoding unicode
    
    # 23. Create a "master deployment script" that executes all the scripts in the correct order
    $masterScriptFile = Join-Path $dbFolder "00_DeployAll.sql"
    Write-Host "  Creating master deployment script"
    
    $masterScript = @"
-- Master Deployment Script for Database $($database.Name)
-- Generated on $(Get-Date -Format "yyyy-MM-dd HH:mm:ss")
-- This script will execute all the scripts in the correct order

USE [master]
GO

"@
    
    # Add Database Creation
    $masterScript += @"
PRINT 'Creating Database $($database.Name)'
GO
:r .\00_Database_Creation.sql
GO

USE [$($database.Name)]
GO

-- Schemas
PRINT 'Creating Schemas'
GO

"@
    
    # Add schemas
    Get-ChildItem -Path $schemasFolder -Filter "*.sql" | ForEach-Object {
        $masterScript += ":r .\01_Schemas\$($_.Name)`r`nGO`r`n"
    }
    
    # Add types
    $masterScript += @"

-- User-Defined Types
PRINT 'Creating User-Defined Types'
GO

"@
    
    Get-ChildItem -Path $typesFolder -Filter "*.sql" | ForEach-Object {
        $masterScript += ":r .\02_UserDefinedTypes\$($_.Name)`r`nGO`r`n"
    }
    
    # Add tables
    $masterScript += @"

-- Tables
PRINT 'Creating Tables'
GO

"@
    
    Get-ChildItem -Path $tablesFolder -Filter "*.sql" | ForEach-Object {
        $masterScript += ":r .\03_Tables\$($_.Name)`r`nGO`r`n"
    }
    
    # Continue with each object type...
    $masterScript += @"

-- Views
PRINT 'Creating Views'
GO

"@
    
    Get-ChildItem -Path $viewsFolder -Filter "*.sql" | ForEach-Object {
        $masterScript += ":r .\04_Views\$($_.Name)`r`nGO`r`n"
    }
    
    $masterScript += @"

-- Functions
PRINT 'Creating Functions'
GO

"@# Continue adding each object type to the master script
    Get-ChildItem -Path $funcsFolder -Filter "*.sql" | ForEach-Object {
        $masterScript += ":r .\05_Functions\$($_.Name)`r`nGO`r`n"
    }
    
    $masterScript += @"

-- Stored Procedures
PRINT 'Creating Stored Procedures'
GO

"@
    
    Get-ChildItem -Path $procsFolder -Filter "*.sql" | ForEach-Object {
        $masterScript += ":r .\06_StoredProcedures\$($_.Name)`r`nGO`r`n"
    }
    
    # Database Triggers
    $masterScript += @"

-- Database Triggers
PRINT 'Creating Database Triggers'
GO

"@
    
    Get-ChildItem -Path $triggersFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\07_DatabaseTriggers\$($_.Name)`r`nGO`r`n"
    }
    
    # Assemblies
    $masterScript += @"

-- Assemblies
PRINT 'Creating Assemblies'
GO

"@
    
    Get-ChildItem -Path $assembliesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\08_Assemblies\$($_.Name)`r`nGO`r`n"
    }
    
    # Synonyms
    $masterScript += @"

-- Synonyms
PRINT 'Creating Synonyms'
GO

"@
    
    Get-ChildItem -Path $synonymsFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\09_Synonyms\$($_.Name)`r`nGO`r`n"
    }
    
    # Sequences
    $masterScript += @"

-- Sequences
PRINT 'Creating Sequences'
GO

"@
    
    Get-ChildItem -Path $sequencesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\10_Sequences\$($_.Name)`r`nGO`r`n"
    }
    
    # User-Defined Aggregates
    $masterScript += @"

-- User-Defined Aggregates
PRINT 'Creating User-Defined Aggregates'
GO

"@
    
    Get-ChildItem -Path $aggregatesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\11_UserDefinedAggregates\$($_.Name)`r`nGO`r`n"
    }
    
    # Security - Roles
    $masterScript += @"

-- Security - Roles
PRINT 'Creating Database Roles'
GO

"@
    
    Get-ChildItem -Path $rolesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\12_Security\Roles\$($_.Name)`r`nGO`r`n"
    }
    
    # Security - Users
    $masterScript += @"

-- Security - Users
PRINT 'Creating Database Users'
GO

"@
    
    Get-ChildItem -Path $usersFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\12_Security\Users\$($_.Name)`r`nGO`r`n"
    }
    
    # Security - Application Roles
    $masterScript += @"

-- Security - Application Roles
PRINT 'Creating Application Roles'
GO

"@
    
    Get-ChildItem -Path $appRolesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\12_Security\ApplicationRoles\$($_.Name)`r`nGO`r`n"
    }
    
    # XML Schema Collections
    $masterScript += @"

-- XML Schema Collections
PRINT 'Creating XML Schema Collections'
GO

"@
    
    Get-ChildItem -Path $xmlSchemaFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\13_XMLSchemaCollections\$($_.Name)`r`nGO`r`n"
    }
    
    # Partition Functions
    $masterScript += @"

-- Partition Functions
PRINT 'Creating Partition Functions'
GO

"@
    
    Get-ChildItem -Path $partFuncFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\14_Partitioning\PartitionFunctions\$($_.Name)`r`nGO`r`n"
    }
    
    # Partition Schemes
    $masterScript += @"

-- Partition Schemes
PRINT 'Creating Partition Schemes'
GO

"@
    
    Get-ChildItem -Path $partSchemeFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\14_Partitioning\PartitionSchemes\$($_.Name)`r`nGO`r`n"
    }
    
    # Full-Text Catalogs
    $masterScript += @"

-- Full-Text Catalogs
PRINT 'Creating Full-Text Catalogs'
GO

"@
    
    Get-ChildItem -Path $ftCatalogFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\15_FullText\Catalogs\$($_.Name)`r`nGO`r`n"
    }
    
    # Full-Text Stoplists
    $masterScript += @"

-- Full-Text Stoplists
PRINT 'Creating Full-Text Stoplists'
GO

"@
    
    Get-ChildItem -Path $ftStoplistFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\15_FullText\Stoplists\$($_.Name)`r`nGO`r`n"
    }
    
    # Service Broker - Message Types
    $masterScript += @"

-- Service Broker - Message Types
PRINT 'Creating Service Broker Message Types'
GO

"@
    
    Get-ChildItem -Path $msgTypesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\16_ServiceBroker\MessageTypes\$($_.Name)`r`nGO`r`n"
    }
    
    # Service Broker - Contracts
    $masterScript += @"

-- Service Broker - Contracts
PRINT 'Creating Service Broker Contracts'
GO

"@
    
    Get-ChildItem -Path $contractsFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\16_ServiceBroker\Contracts\$($_.Name)`r`nGO`r`n"
    }
    
    # Service Broker - Queues
    $masterScript += @"

-- Service Broker - Queues
PRINT 'Creating Service Broker Queues'
GO

"@
    
    Get-ChildItem -Path $queuesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\16_ServiceBroker\Queues\$($_.Name)`r`nGO`r`n"
    }
    
    # Service Broker - Services
    $masterScript += @"

-- Service Broker - Services
PRINT 'Creating Service Broker Services'
GO

"@
    
    Get-ChildItem -Path $servicesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\16_ServiceBroker\Services\$($_.Name)`r`nGO`r`n"
    }
    
    # Service Broker - Remote Service Bindings
    $masterScript += @"

-- Service Broker - Remote Service Bindings
PRINT 'Creating Service Broker Remote Service Bindings'
GO

"@
    
    Get-ChildItem -Path $bindingsFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\16_ServiceBroker\RemoteServiceBindings\$($_.Name)`r`nGO`r`n"
    }
    
    # Service Broker - Routes
    $masterScript += @"

-- Service Broker - Routes
PRINT 'Creating Service Broker Routes'
GO

"@
    
    Get-ChildItem -Path $routesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\16_ServiceBroker\Routes\$($_.Name)`r`nGO`r`n"
    }
    
    # Plan Guides
    $masterScript += @"

-- Plan Guides
PRINT 'Creating Plan Guides'
GO

"@
    
    Get-ChildItem -Path $planGuidesFolder -Filter "*.sql" -ErrorAction SilentlyContinue | ForEach-Object {
        $masterScript += ":r .\17_PlanGuides\$($_.Name)`r`nGO`r`n"
    }
    
    # Security - Database Permissions
    $masterScript += @"

-- Database Permissions
PRINT 'Applying Database Permissions'
GO
:r .\12_Security\DatabasePermissions.sql
GO

"@
    
    # Final completion message
    $masterScript += @"

PRINT 'Database $($database.Name) deployment completed successfully!'
GO
"@
    
    # Write the master script to file
    $masterScript | Out-File -FilePath $masterScriptFile -Encoding unicode
    
    # Create a readme file with instructions
    $readmeFile = Join-Path $dbFolder "README.md"
    $readme = @"
# Database Scripts for $($database.Name)

This folder contains SQL scripts to recreate the database $($database.Name) and all its objects.

## How to Deploy

You can deploy the database in two ways:

### Option 1: Deploy everything at once

Run the master deployment script:

sqlcmd -S YourServerName -i 00_DeployAll.sql

### Option 2: Deploy objects manually in the correct order

The scripts are numbered to indicate the order in which they should be executed:

1. 00_Database_Creation.sql - Creates the database
2. 01_Schemas - Creates schemas
3. 02_UserDefinedTypes - Creates user-defined types
4. 03_Tables - Creates tables and their constraints
5. ... and so on

## Object Types

* 00_Database_Creation.sql - Database creation script
* 01_Schemas - Database schemas
* 02_UserDefinedTypes - User-defined data types and table types
* 03_Tables - Tables with constraints, indexes, and triggers
* 04_Views - Views
* 05_Functions - User-defined functions (scalar, table-valued, etc.)
* 06_StoredProcedures - Stored procedures
* 07_DatabaseTriggers - Database-level triggers
* 08_Assemblies - CLR assemblies
* 09_Synonyms - Synonyms
* 10_Sequences - Sequences
* 11_UserDefinedAggregates - User-defined aggregates
* 12_Security - Database roles, users, application roles, and permissions
* 13_XMLSchemaCollections - XML schema collections
* 14_Partitioning - Partition functions and schemes
* 15_FullText - Full-text catalogs and stoplists
* 16_ServiceBroker - Service Broker objects
* 17_PlanGuides - Plan guides

## Generated on: $(Get-Date -Format "yyyy-MM-dd HH:mm:ss")
"@
    
    $readme | Out-File -FilePath $readmeFile -Encoding unicode
    
    Write-Host "Completed scripting for database: $DatabaseName" -ForegroundColor Green
    
    return "Completed: $DatabaseName"
}

# Now use PowerShell jobs to run the tasks in parallel
$jobs = @()

Write-Host "Starting parallel database scripting jobs..." -ForegroundColor Cyan

# Create throttling semaphore using a queue with the maximum number of concurrent jobs
$throttleSemaphore = [System.Collections.Concurrent.ConcurrentQueue[int]]::new()
for ($i = 0; $i -lt $MaxConcurrentJobs; $i++) {
    $throttleSemaphore.Enqueue($i)
}

# Process each database asynchronously but with throttling
foreach ($dbName in $DatabaseNames) {
    # Wait for an available slot (throttling)
    $slot = $null
    while (-not $throttleSemaphore.TryDequeue([ref]$slot)) {
        Start-Sleep -Milliseconds 200
        
        # Check if any jobs have completed and release their slots
        $completedJobs = $jobs | Where-Object { $_.State -eq "Completed" }
        foreach ($job in $completedJobs) {
            if ($job.HasMoreData) {
                $result = Receive-Job -Job $job
                Write-Host "Job result: $result" -ForegroundColor Green
            }
            
            # Release the slot back to the queue
            $throttleSemaphore.Enqueue(0) 
            
            # Remove the job from our tracking array
            $jobs = $jobs | Where-Object { $_.Id -ne $job.Id }
            
            # Dispose of the completed job
            Remove-Job -Job $job
        }
    }
    
    # Start a new background job for this database
    Write-Host "Starting job for database: $dbName" -ForegroundColor Yellow
    $job = Start-Job -ScriptBlock $scriptingFunction -ArgumentList $ServerInstance, $dbName, $OutputPath, $IncludeSystemObjects, $Credential
    $jobs += $job
}

# Wait for all remaining jobs to complete
Write-Host "Waiting for all jobs to complete..." -ForegroundColor Cyan
$remainingJobs = $jobs
while ($remainingJobs.Count -gt 0) {
    Start-Sleep -Seconds 2
    
    # Check for completed jobs
    $completedJobs = $remainingJobs | Where-Object { $_.State -eq "Completed" }
    foreach ($job in $completedJobs) {
        if ($job.HasMoreData) {
            $result = Receive-Job -Job $job
            Write-Host "Job result: $result" -ForegroundColor Green
        }
        
        # Remove the job from our tracking array
        $remainingJobs = $remainingJobs | Where-Object { $_.Id -ne $job.Id }
        
        # Dispose of the completed job
        Remove-Job -Job $job
    }
}

Write-Host "All database scripting tasks completed!" -ForegroundColor Green
Write-Host "Scripts are saved to: $OutputPath" -ForegroundColor Cyan