If like me you come across the need to export all your PBM policies and dont want to do the manual right click save (Repeat 20 + times) then you may find the below script useful, it connects to a SQL instance via the SQLPS module and writes out every policy and every custom made policy group (That bit may need to be tweaked!) to an individual .sql script in the location you specify. It saved me some time, and hopefully will you as well.

<#

Export All PBM

#>

IF (!(Get-Module -Name sqlps))
    {
        Write-Host "Loading SQLPS Module" -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location
    }

## Define Server holding policies you wish to export
$SourcePolicyServer = "REXGBASQLMAN001"
## Define locations for policy group and policy
$PolicyRoot = "SQLSERVER:\sqlpolicy\$SourcePolicyServer\default\policies"
$PolicyCateRoot = "SQLSERVER:\sqlpolicy\$SourcePolicyServer\default\policycategories"
## Define where to export SQL Script per policy
$DistinationFolder = "Y:\Temp\"

## Export All user made Policy groups
Set-Location $PolicyCateRoot
## This maybe wrong! works on SQL 2014 to remove premade groups (couldnt find a way to find out if object was made by user)
$UserMadePolicyGroups = Get-ChildItem | Where-Object {$_.ID -gt 10}
Pop-Location

foreach ($UserPolicyGroup in $UserMadePolicyGroups)
    {
        $UserPolicyGroupName = $UserPolicyGroup.Name
        write-host "Exporting Policy Category : $UserPolicyGroupName" 
        $OutputFile = $DistinationFolder + "00 Policy Group - " + $UserPolicyGroupName + ".sql"
        $PolicyGroupScript = $UserMadePolicyGroups[0].ScriptCreate() 
        $PolicyGroupScript.GetScript() | Out-File $OutputFile
    }


## Export All the Policies
Push-Location
Set-Location $PolicyRoot
$Policies = Get-ChildItem
Pop-Location

foreach ($Policy in $Policies)
    {
        $PolicyName = $Policy.Name
        write-host "Exporting Policy : $PolicyName" 
        $OutputFile = $DistinationFolder + "01 Policy - " + $PolicyName + ".sql"
        $ExportPolicyScript = $Policy.ScriptCreateWithDependencies()
        $ExportPolicyScript.Getscript() | Out-File $OutputFile
    }



Below is the Policies sat nicely in SSMS

ExportPolicies01

and here we have my output from the script

pbm02

Now if you want to import them into a new instance you would just run a loop over the scripts to import them one at a time.

Advertisements