Ciao a tutti,

per un’esigenza lavorativo mi sono imbattuto nella gestione del backup di un database MSSQL ma senza i dati “vivi” presenti nel database.

Girando un pochino su internet ho trovato un fantastico script powershell (trovate l’originale qui)

Allo script sottostante non ho apportato nessuna modifica

# Usage: powershell ExportSchema.ps1 "SERVERNAME" "DATABASE" "C:\"

# Start Script
Set-ExecutionPolicy RemoteSigned
# Set-ExecutionPolicy unrestricted

# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.AllowSystemObjects = $false
$options.IncludeDatabaseContext = $true
$options.IncludeIfNotExists = $false
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $false
$options.ToFileOnly = $true
$options.AppendToFile = $true
$options.ScriptDrops = $false

# Set options for SMO.Scripter
$scr.Options = $options

#=============
# Tables
#=============
$options.FileName = $scriptpath + "\$($dbname)_tables.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($tb in $db.Tables)
{
If ($tb.IsSystemObject -eq $FALSE)
{
$smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
$smoObjects.Add($tb.Urn)
$scr.Script($smoObjects)
}
}

#=============
# Views
#=============
$options.FileName = $scriptpath + "\$($dbname)_views.sql"
New-Item $options.FileName -type file -force | Out-Null
$views = $db.Views | where {$_.IsSystemObject -eq $false}
Foreach ($view in $views)
{
if ($views -ne $null)
{
$scr.Script($view)
}
}

#=============
# StoredProcedures
#=============
$StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_stored_procs.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($StoredProcedure in $StoredProcedures)
{
if ($StoredProcedures -ne $null)
{
$scr.Script($StoredProcedure)
}
}

#=============
# Functions
#=============
$UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_functions.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($function in $UserDefinedFunctions)
{
if ($UserDefinedFunctions -ne $null)
{
$scr.Script($function)
}
}

#=============
# DBTriggers
#=============
$DBTriggers = $db.Triggers
$options.FileName = $scriptpath + "\$($dbname)_db_triggers.sql"
New-Item $options.FileName -type file -force | Out-Null
foreach ($trigger in $db.triggers)
{
if ($DBTriggers -ne $null)
{
$scr.Script($DBTriggers)
}
}

#=============
# Table Triggers
#=============
$options.FileName = $scriptpath + "\$($dbname)_table_triggers.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($tb in $db.Tables)
{
if($tb.triggers -ne $null)
{
foreach ($trigger in $tb.triggers)
{
$scr.Script($trigger)
}
}
}
}

#=============
# Execute
#=============
GenerateDBScript $args[0] $args[1] $args[2]

Utilizzando lo script sotto stante in aggiunta a quello precedente (ExportSchema.ps1) così che possiate intgrare utenze, multi istanza ed archivio per data.

Non è necessario che i due script risiedano nella stessa directory, modificate il file (ExportSchemaDB.bat) con i percorsi corretti

@echo OFF
SET PSPath=C:\Windows\System32\WindowsPowerShell\v1.0
SET MasterPath=c:\batch

REM Impostare il nome del server comprensivo di instanza MSSQL in caso ne siano presenti + di 1
REM es.: SrvName=servername\instancename
SET SrvName=servername\instancename
SET DstPath=c:\path_to_destination_backup

for /f %%i in ('%MasterPath%\doff.exe yyyymmdd_hhmi') do set fn=%%i

REM # Usage:  powershell ExportSchema.ps1 "SERVERNAME" "DATABASE" "C:\<YourOutputPath>"
REM echo %PSPath%\powershell.exe %MasterPath%\ExportSchema.ps1 "%SrvName%" "DbName" "%DstPath%\SchemaDB\%fn%_%DbName%"

%PSPath%\powershell.exe %MasterPath%\ExportSchema.ps1 "%SrvName%" "DbName1" "%DstPath%\SchemaDB\%fn%_DbName1"

Download file zip con tutto il necessario ExportSchemaDatabase