Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Set-StrictMode -Version 2.0
function Get-SqlDataScript {
param(
[Parameter(Mandatory, Position = 0)]
[string] $ServerName,
[Parameter(Mandatory, Position = 1)]
[string] $InstanceName,
[Parameter(Mandatory, Position = 2)]
[string] $DatabaseName,
[Parameter(Mandatory, Position = 3)]
[Hashtable] $TargetObjectNames,
[Switch] $GenerateDeleteScript)
Push-Location
try {
Import-Module 'sqlps' -DisableNameChecking
}
finally {
Pop-Location
}
$serverObject = Get-Item "SQLSERVER:\SQL\$ServerName\$InstanceName"
$scripter = New-Object 'Microsoft.SqlServer.Management.Smo.Scripter' $serverObject
$urns = @()
foreach ($type in $TargetObjectNames.Keys) {
$path = "SQLSERVER:\SQL\$ServerName\$InstanceName\Databases\$DatabaseName\$type"
$objects = Get-ChildItem $path | ? { $TargetObjectNames[$type] -ieq $_.Name }
$urns += $objects.Urn
}
$scripter.Options.ScriptData = $true
$scripter.Options.ScriptSchema = $false
$scripts = @()
if ($GenerateDeleteScript) {
$scripter.Options.ScriptDrops = $true
$scripts += $scripter.EnumScriptWithList($urns)
}
$scripter.Options.ScriptDrops = $false
$scripts += $scripter.EnumScriptWithList($urns)
return $scripts
}
$targetObjectNames = @{
Tables = @(
'Customers',
'Orders',
'Products'
)
}
Get-SqlDataScript -ServerName 'localhost' -InstanceName 'DEFAULT' -DatabaseName 'SalesDb' -TargetObjectNames $targetObjectNames -GenerateDeleteScript
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment