PowerShell SMO by SqlServer module

Install SqlServer module:

Install-Module -Name sqlserver

Typical warning:

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): y

See installed module:

Get-Module -Name SqlServer -ListAvailable

Import SqlServer module in PowerShell session:

Import-Module -Name SqlServer

Create SMO Server object:

$smoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)\SSDB2019'

See SMO Server object information:

$smoServer.Information

SMO by SQLPS

With SQL Server 2012 SQLPS was changes from a mini-PowerShell to a PowerShell module. This makes is much simpler to import the functionality in a regular PowerShell script.
The examples below are written on a computer running Windows 8 and SQL Server 2012.
SQLPS is imported much simpler than we used to import SQL Server Management Objects SMO assemblies.
Import-Module 'SQLPS'
The parameter „DisableNameChecking“ can be added to avoid a message about unapproved verbs.
A list of exported Cmdlets in the module can be shown
(Get-Module 'SQLPS' -ListAvailable).ExportedCmdlets.Values.Name

Backup-SqlDatabase
Add-SqlAvailabilityDatabase
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Switch-SqlAvailabilityGroup
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityReplica
New-SqlHADREndpoint
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityReplica
Resume-SqlAvailabilityDatabase
Set-SqlAvailabilityGroup
Set-SqlAvailabilityReplica
Set-SqlHADREndpoint
Suspend-SqlAvailabilityDatabase
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
New-SqlAvailabilityGroupListener
Set-SqlAvailabilityGroupListener
Add-SqlAvailabilityGroupListenerStaticIp
Invoke-PolicyEvaluation
Restore-SqlDatabase
Invoke-Sqlcmd
Encode-SqlName
Decode-SqlName
Convert-UrnToPath

Server Object

To create a SMO Server object on a local default SQL Server database instance can be like
$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)'
SMO only connects to the when you ask for something specific. For a start I would like to know the basics about the installation.
$SmoServer.Information

Parent                      : [TITANIUM]
Version                     : 11.0.3128
EngineEdition               : EnterpriseOrDeveloper
ResourceVersion             : 11.0.3000
BuildClrVersion             : 4.0.30319
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 3128
Collation                   : Latin1_General_100_CI_AS_KS_WS_SC
...

The full set of Server property values can be accessed
$SmoServer | Format-List *

AuditLevel                  : Failure
BackupDirectory             : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
BrowserServiceAccount       : NT AUTHORITY\LOCALSERVICE
BrowserStartMode            : Disabled
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 3128
...

Database Object

A SMO Database object can be created on a database with a known name
$SmoDatabase = $SmoServer.Databases['msdb']
This object can give access to some information
$SmoDatabase | Format-Table DataSpaceUsage,IndexSpaceUsage -AutoSize

DataSpaceUsage IndexSpaceUsage
-------------- ---------------
12080            3136

Table Object

Also the Database object can be used to create a SMO Table object. This can be direct on the table name alone
$SmoDatabase.Tables['sysjobhistory']
Or the Table object can be create more specific on the schema name with the table name
$SmoTable= ($SmoDatabase.Tables |
Where-Object -FilterScript { $PSItem.Schema -eq 'dbo' -and $PSItem.Name -eq 'sysjobhistory' })

Again the Table object gives access to some basic information
$SmoTable | Format-Table Schema,Name,DataSpaceUsed,IndexSpaceUsed,RowCount -AutoSize

Schema Name          DataSpaceUsed IndexSpaceUsed RowCount
------ ----          ------------- -------------- --------
dbo    sysjobhistory            32             32       84

Stored Procedure Object

The Database object can also be used to create a SMO Stored Procedure object. Like the Table object it can be created in two ways, but I will be (professional) lazy and only show the creation on the name alone
$SmoStoredProcedure = $SmoDatabase.StoredProcedures['sp_get_job_alerts']
A stored procedure usually has one or more parameters that can be described with
$SmoStoredProcedure.Parameters | Format-Table Name,DataType,DefaultValue,IsOutputParameter -AutoSize

Name      DataType         DefaultValue IsOutputParameter
----      --------         ------------ -----------------
@job_id   uniqueidentifier                          False
@job_name sysname                                   False

Server Job Object

With the installation of the SQL Server database service is also installed SQL Server Agent that is the SQL Server scheduler used for maintenance, monitoring and other automated tasks.
A SMO Job Server object can be created from a Server object property and gives a quick access to some basic SQL Server Agent information
$SmoServer.JobServer

AgentDomainGroup          : NT SERVICE\SQLSERVERAGENT
AgentLogLevel             : Errors, Warnings
AgentMailType             : SqlAgentMail
AgentShutdownWaitTime     : 15
DatabaseMailProfile       : 
ErrorLogFile              : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
...

More interesting is the information om a job, where a SMO Server Job object can be created on the Server object
$SmoJob = $SmoServer.JobServer.Jobs['syspolicy_purge_history']
This gives access to some basic information on the job and its execution
$SmoJob | Format-Table CurrentRunStatus,LastRunDate,LastRunOutCome,NextRunDate -AutoSize

CurrentRunStatus LastRunDate         LastRunOutcome NextRunDate        
---------------- -----------         -------------- -----------        
            Idle 05-04-2013 02:00:00      Succeeded 21-05-2013 02:00:00

Reference

There are much more on SMO in MSDN Library, you can start with
„Microsoft.SqlServer.Management.Smo Namespace“
SMO is much larger which is indicated at
„SQL Server Management Objects Reference“, take a look at the index to the left.

Check database using SMO

For a long time I have checked the databases by DBCC CHECKDB, but I would like to check the database in PowerShell with SMO to include the check in a more general maintenance script with additional features.

The check can be done with the SMO Database class method CheckTables().
[string]$ServerName = 'DNDBA01.dn.lan'
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.Databases | ForEach-Object {
"{0:s}  Checking database [$($_.Name)]..." -f $([System.DateTime]::Now)
$_.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None, [Microsoft.SqlServer.Management.Smo.RepairOptions]::AllErrorMessages)
}

All errormessages are included to help the administrator if a check fail. Also it can be used for analytics.

Additional features to the script can be considered after reading the discussion by Cindy Gross.

A nice introduction to checking database integrity with SMO is
"Getting Started with SMO in SQL 2005 - Integrity Checks" by Jasper Smith.

Script a database definition

Recreating a database in a higher environment is a common task. Quite often it is not that smart just to restore a back, as you then get database users, file sizes, obsolete schemas etc into the higher environment.
Scripting out a database can be done from SQL Server Management Studio, but this is a manual operation. When you are serving a small army of developers, they need a definition of the production database on a regular basis. Doing that by SQL Server Management Studio quickly gets boring.
There are third party tools like Red Gate SQL Compare that can generate a schema snapshot or a direct comparison, but such a tool is expensive for a simple task like this. In many other situations I am really happy with such tools, but that is another story.
This PowerShell script generates the T-SQL script to create a database and its structure. The script can be edited and reviewed before execution, and can serve as documentation in a Change procedure.
[string]$SqlServerName = '(local)'
[string]$DbName = 'AdventureWorks'

[string]$SqlBatchSeperator = 'GO'  # SQL Server Management Studio default: GO

"/*`nT-SQL script to define the structure of the database '$($DbName)' on the server '$($SqlServerName)'."
"Script generated $($(Get-Date).ToUniversalTime().ToString('s')) UTC"
"  by the user $([Security.Principal.WindowsIdentity]::GetCurrent().Name)"
"  on the computer $($env:COMPUTERNAME)"
"  with the script file '$($MyInvocation.InvocationName)'"
"*/"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServerName
$Options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$Options.IncludeHeaders = $true
$Options.Permissions = $true

"`n--*** Logins ***"
$Server.Logins | Where-Object { ($_.Name -ne 'sa') -and ($_.Name -notlike '##MS_Policy*') -and ($_.Name -notlike 'NT *') } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n--*** Database definition ***"
$Server.Databases[$DbName].Script();$SqlBatchSeperator
"`n--EXECUTE [$DbName].dbo.sp_changedbowner @loginame = N'sa', @map = true;`n$SqlBatchSeperator"
"`nALTER AUTHORIZATION ON DATABASE::[$DbName] TO [sa];`n$SqlBatchSeperator"
"`nUSE [$DbName];`n$SqlBatchSeperator"
"`n`n--*** Userdefined applicationroles ***"
$Server.Databases[$DbName].ApplicationRoles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined databaseroles ***"
$Server.Databases[$DbName].Roles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined schemas ***"
$Server.Databases[$DbName].Schemas | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

$Options.IncludeDatabaseRoleMemberships = $true

"`n`n--*** Database users ***"
$Server.Databases[$DbName].Users | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

$Options.DriAll = $true
$Options.Indexes = $true

"`n`n--*** Userdefined assemblies ***"
$Server.Databases[$DbName].Assemblies | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined certificates ***"
$Server.Databases[$DbName].Certificates | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined defaults ***"
$Server.Databases[$DbName].Defaults | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined fulltext catalogs ***"
$Server.Databases[$DbName].FullTextCatalogs | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined rules ***"
$Server.Databases[$DbName].Rules | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined stored procedures ***"
$Server.Databases[$DbName].StoredProcedures | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined tables with indexes ***"
$Server.Databases[$DbName].Tables | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined Triggers ***"
$Server.Databases[$DbName].Triggers | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined functions ***"
$Server.Databases[$DbName].UserDefinedFunctions | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined datatypes ***"
$Server.Databases[$DbName].UserDefindTypes | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined views ***"
$Server.Databases[$DbName].Views | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

The script covers the basics, and I think that it is easy to fit it to a equal task.

The script can be used in a Visual Studio solution to compare with a database in development. Such a comparison can give a deployment script for a higher and isolated environment.

Reference

TechNet Library: SMO "Server Class"

Using a SMO object as parameter value

It looks like a SMO object is implicit converted to a String object, when it is used in a function call by a parameter name.

I have a function, where the parameter is defined as
function Import-FileGroup {
param ([Microsoft.SqlServer.Management.Smo.Database]$smoDatabase = $(throw 'Value requered for parameter "smoDatabase" in function Import-FileGroup.'))

...
}

When I call the function with a parameter name
Import-FileGroup -$smoDatabase $smoDb
it fail with this message
Import-FileGroup : Cannot process argument transformation on parameter 'smoDatabase'. Cannot convert the "-" value of type "System.String" to type "Microsoft.SqlServer.Management.Smo.Database".
At U:\sqladmin\dn_sqladmin\Development\Import-Database.ps1:***** char:17
+ Import-FileGroup <<<<  -$smoDatabase $smoDb
    + CategoryInfo          : InvalidData: (:) [Import-FileGroup], ParameterBindin...mationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Import-FileGroup

But when I call the function without a parameter name
Import-FileGroup $smoDb
everything is working fine.

Not what I expected, but I have now made the note for future use...

The variable „$smoDb“ is created like this
$smoSrv = New-Object Microsoft.SqlServer.Management.Smo.Server 'SANDY.sqladmin.lan'
$smoDb = $smoSrv.Databases['my_database']

Differential database backup using SMO

A differential database backup of a single database can be done like this using SMO and PowerShell:

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SsdbName = 'Sandbox\Ssdb0'
$BackupPath = 'Z:\Backup\Ssdb0'
$Server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $SsdbName
$Database = $Server.Databases['msdb']
$Backup = New-Object 'Microsoft.SqlServer.Management.Smo.Backup'
$Backup.Checksum = $true
$Backup.Database = $Database.Name
$Backup.Incremental = $true
$Backup.BackupSetDescription = 'Differential backup of the database [' + $Database.Name + '].'
$BackupFileName = $Database.Name + '_Diff.bak'
$BackupDevice = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem'
$BackupDevice.DeviceType = 'File'
$BackupDevice.Name = [System.IO.Path]::Combine($BackupPath, $BackupFileName)
$Backup.Devices.Add($BackupDevice)
$Backup.BackupSetName = $Database.Name + ' ' + $BackupType + ' backup'
$Backup.SqlBackup($Server)
$Backup.Wait()
"[{0}] is backed up to the file '{1}'." -f $Database.Name, $BackupDevice.Name

The generated backup file is named "msdb_Diff.bak". A more unique filename is preferred, but this is just a simple example.

The output of the script is:
[msdb] is backed up to the file 'T:\Backup\Ssdb0\msdb_Diff.bak'.

A more detailed description of SMO backup using PowerShell is done by Muthusamy Anantha Kumar in a article at Database Journal (link).

SMO Backup class moved

Having worked on a (new) backup solution for my employer, I have spend several hours fighting with examples not working.
Until I found the explanation and a solution at Allen Whites blog: http://sqlblog.com/blogs/allen_white/archive/2008/12/07/loading-smo-assemblies-into-powershell.aspx

I still wonder why the the classes listed in another blog entry by Allen (http://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspx) were moved and then destroying the compability between SQL 2005 and SQL 2008...