Esportazione di oggetti di database SQL Server

Per un'origine offline, si caricano file di script esportati che descrivono gli oggetti del database di origine. È possibile esportarli utilizzando qualsiasi strumento disponibile. Le schede seguenti descrivono due approcci: SQL Server Management Studio (SSMS) e SQL Server Management Objects (SMO).

SSMS

1. In SSMS, in Object Explorer, fate clic con il pulsante destro del mouse sul database da esportare. Scegliete Attività, quindi scegliete Genera script.

2. Scegliete Salva come file di script.

3. In File da generare, scegli Un file di script per oggetto per creare un .sql file separato per ogni oggetto del database.

4. Per Directory Name, inserite il percorso di una directory per i file di output. Si consiglia di creare una directory con lo stesso nome del database.

5. Per Salva con nome, scegli Testo Unicode.

6. Scegliete Avanzate per aprire la finestra di dialogo Opzioni di scripting avanzate.

7. Impostate le opzioni in base alle seguenti tabelle.

8. Scegliete OK, quindi scegliete Avanti per esaminare il riepilogo.

9. Scegliete Avanti per generare gli script.

10. Scegli Fine per chiudere la procedura guidata.

Opzioni di scripting avanzate

Impostate le seguenti opzioni nella finestra di dialogo Opzioni di script avanzate.

Opzioni generali

Opzione	Valore
Imbottitura ANSI	False
Aggiungi al file	False
Verifica l'esistenza dell'oggetto	False
Continua a scrivere script in caso di errore	True
Convertire gli UDDT in tipi di base	False
Genera script per oggetti dipendenti	True
Includi intestazioni descrittive	False
Includi l'intestazione dei parametri di scripting	False
Includi i nomi dei vincoli di sistema	False
Lo schema qualifica i nomi degli oggetti	True
Associazione di script	False
Collazione degli script	True
Impostazioni predefinite degli script	True
Script DROP AND CREATE	Script CREATE
Proprietà estese dello script	True
Accessi agli script	False
Autorizzazioni per gli script Object-Level	False
Proprietario dello script	False
Statistiche dello script	Non utilizzare script per le statistiche
Script USE DATABASE	True
Tipi di dati da scrivere	Solo schema

Table/View opzioni

Opzione	Valore
Monitoraggio delle modifiche agli script	False
Vincoli di Script Check	True
Opzioni di compressione dei dati degli script	False
Chiavi esterne dello script	True
Full-Text Indice degli script	True
Indice degli script	True
Chiavi primarie dello script	True
Trigger di script	True
Chiavi uniche dello script	True

SMO

SQL Server Management Objects (SMO) è un'API.NET per la gestione programmatica di SQL Server. È possibile utilizzare SMO con PowerShell o C# per creare script di oggetti di database e generare file DDL da utilizzare con un'origine offline.

Prerequisiti

• Windows PowerShell 5.1 o versione successiva o 7+ PowerShell

• Il SqlServer PowerShell modulo (da installare conInstall-Module SqlServer)

• .NET Framework 4.6.2 o versione successiva oppure .NET 6+ per multipiattaforma

Configurazione degli script chiave

Quando usi SMO per esportare script per una fonte offline, configura le seguenti opzioni di script:

Opzioni di scripting SMO richieste

Opzione	Valore
ToFileOnly	True
Encoding	Unicode
AppendToFile	False
IncludeHeaders	True
IncludeDatabaseContext	True
SchemaQualify	True
AnsiPadding	True
Default	True
DriAll	True
Indexes	True
Triggers	True
FullTextIndexes	True
ExtendedProperties	True
ScriptDataCompression	True
ScriptDrops	False
IncludeIfNotExists	False
WithDependencies	False
NoCollation	True
Permissions	False
ContinueScriptingOnError	True

Script di esempio PowerShell

Lo script seguente esporta tutti gli oggetti del database in singoli .sql file. Ogni file contiene un'CREATEistruzione per un singolo oggetto di database.

# Export SQL Server database objects using SMO
# Usage: .\Export-Database.ps1 -Database "MyDB" -OutputDir "C:\export\MyDB"

param(
    [string] $ServerInstance = "localhost",         # SQL Server instance name
    [Parameter(Mandatory)] [string] $Database,     # Database to export
    [Parameter(Mandatory)] [string] $OutputDir,    # Output directory for .sql files
    [switch] $TrustServerCertificate               # Skip certificate validation
)

$ErrorActionPreference = "Stop"

# Load the SqlServer module (includes SMO assemblies)
Import-Module SqlServer -ErrorAction Stop

# Connect to SQL Server
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
$srv.ConnectionContext.EncryptConnection = $true
if ($TrustServerCertificate) {
    $srv.ConnectionContext.TrustServerCertificate = $true
}

# Get the database
$db = $srv.Databases[$Database]
if (-not $db) { Write-Error "Database '$Database' not found."; exit 1 }
if (-not (Test-Path $OutputDir)) { New-Item -ItemType Directory -Path $OutputDir -Force | Out-Null }

# Configure scripting options for offline source compatibility
$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
$opts = $scripter.Options
$opts.ToFileOnly             = $true       # Write directly to files
$opts.Encoding               = [System.Text.Encoding]::Unicode  # Unicode encoding
$opts.AppendToFile           = $false      # One object per file
$opts.IncludeHeaders         = $true       # Add descriptive headers
$opts.IncludeDatabaseContext = $true       # Add USE [database] statement
$opts.SchemaQualify          = $true       # Use schema.object format
$opts.AnsiPadding            = $true       # Include ANSI_PADDING settings
$opts.Default                = $true       # Include default constraints
$opts.DriAll                 = $true       # Include all constraints and keys
$opts.Indexes                = $true       # Include indexes
$opts.Triggers               = $true       # Include triggers
$opts.FullTextIndexes        = $true       # Include full-text indexes
$opts.ExtendedProperties     = $true       # Include extended properties
$opts.ScriptDataCompression  = $true       # Include data compression settings
$opts.ScriptDrops            = $false      # CREATE only, no DROP statements
$opts.IncludeIfNotExists     = $false      # No IF NOT EXISTS wrapper
$opts.WithDependencies       = $false      # Do not script dependent objects
$opts.NoCollation            = $true       # Omit collation clauses
$opts.Permissions            = $false      # Omit permissions
$opts.ContinueScriptingOnError = $true     # Skip errors, continue with next object

# Define which object types to export
$collections = [ordered]@{
    "Schemas"               = "Schema"
    "Tables"                = "Table"
    "Views"                 = "View"
    "StoredProcedures"      = "StoredProcedure"
    "UserDefinedFunctions"  = "UserDefinedFunction"
    "Sequences"             = "Sequence"
    "Synonyms"              = "Synonym"
    "UserDefinedDataTypes"  = "UserDefinedDataType"
    "UserDefinedTableTypes" = "UserDefinedTableType"
    "UserDefinedTypes"      = "UserDefinedType"
    "UserDefinedAggregates" = "UserDefinedAggregate"
    "XmlSchemaCollections"  = "XmlSchemaCollection"
    "Rules"                 = "Rule"
    "Defaults"              = "Default"
    "PartitionSchemes"      = "PartitionScheme"
    "PartitionFunctions"    = "PartitionFunction"
    "Assemblies"            = "SqlAssembly"
    "FullTextCatalogs"      = "FullTextCatalog"
    "FullTextStopLists"     = "FullTextStopList"
    "SearchPropertyLists"   = "SearchPropertyList"
    "SecurityPolicies"      = "SecurityPolicy"
    "ExternalDataSources"   = "ExternalDataSource"
    "ExternalFileFormats"   = "ExternalFileFormat"
}

# System schemas to skip
$sysSchemas = 'dbo','guest','INFORMATION_SCHEMA','sys',
    'db_owner','db_accessadmin','db_securityadmin','db_ddladmin',
    'db_backupoperator','db_datareader','db_datawriter',
    'db_denydatareader','db_denydatawriter'

# Export the CREATE DATABASE statement
$dbScripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
$dbScripter.Options.ToFileOnly = $true
$dbScripter.Options.Encoding = [System.Text.Encoding]::Unicode
$dbScripter.Options.IncludeDatabaseContext = $true
$dbScripter.Options.FileName = Join-Path $OutputDir "$Database.Database.sql"
$dbScripter.Script($db)

# Export each object to a separate .sql file
foreach ($colName in $collections.Keys) {
    $col = $db.$colName
    if (-not $col -or $col.Count -eq 0) { continue }

    foreach ($obj in $col) {
        # Skip system objects
        if ($obj.PSObject.Properties['IsSystemObject'] -and $obj.IsSystemObject) { continue }
        if ($colName -eq "Schemas" -and $obj.Name -in $sysSchemas) { continue }

        # Build the output file name: schema.objectname.type.sql
        $schema = if ($obj.PSObject.Properties['Schema']) { $obj.Schema } else { "" }
        $safeName = $obj.Name -replace '[\\/:*?"<>|]', '_'
        $fileName = if ($schema) { "$schema.$safeName.$($collections[$colName]).sql" }
                    else { "$safeName.$($collections[$colName]).sql" }

        $opts.FileName = Join-Path $OutputDir $fileName
        $scripter.Script($obj)
    }
}