Posted by: EPM Partners on February 21, 2013
SQL Server Management Objects (SMO) are .NET objects introduced by Microsoft which has been created to allow for programmatic management of Microsoft SQL Server. Using SMO, programmers can design applications or scripts which perform similar in functionality to Microsoft’s SQL Server Management Studio.
As outlined in Microsoft article (http://msdn.microsoft.com/en-us/library/hh245202.aspx) you can load SMO assemblies in Windows Powershell by including the below lines at the start of your ps1 script.
# # Loads the SQL Server Management Objects (SMO) #
$sqlpsreg=”HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps”
if (Get-ChildItem $sqlpsreg -ErrorAction “SilentlyContinue”) { throw “SQL Server Provider for Windows PowerShell is not installed.” } else { $item = Get-ItemProperty $sqlpsreg $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path) }
$assemblylist = “Microsoft.SqlServer.Management.Common”, “Microsoft.SqlServer.Smo”, “Microsoft.SqlServer.Dmf “, “Microsoft.SqlServer.Instapi “, “Microsoft.SqlServer.SqlWmiManagement “, “Microsoft.SqlServer.ConnectionInfo “, “Microsoft.SqlServer.SmoExtended “, “Microsoft.SqlServer.SqlTDiagM “, “Microsoft.SqlServer.SString “, “Microsoft.SqlServer.Management.RegisteredServers “, “Microsoft.SqlServer.Management.Sdk.Sfc “, “Microsoft.SqlServer.SqlEnum “, “Microsoft.SqlServer.RegSvrEnum “, “Microsoft.SqlServer.WmiEnum “, “Microsoft.SqlServer.ServiceBrokerEnum “, “Microsoft.SqlServer.ConnectionInfoExtended “, “Microsoft.SqlServer.Management.Collector “, “Microsoft.SqlServer.Management.CollectorEnum”, “Microsoft.SqlServer.Management.Dac”, “Microsoft.SqlServer.Management.DacEnum”, “Microsoft.SqlServer.Management.Utility”
foreach ($asm in $assemblylist) { $asm = [Reflection.Assembly]::LoadWithPartialName($asm) }
Note*: The server you will be running the script needs to have SMO assemblies installed:
- Included in SQL Server Management Studio or
- SharedManagementObjects.msi included in the Feature Pack (http://www.microsoft.com/en-us/download/details.aspx?id=16978) – installs the required SMO assemblies only.
Implementation/Example
The most common function is to programmatically backup and restore databases. See below of example script for backing up, restoring databases and configuring security using Powershell with SMO.
# Function to backup database
function BackupDatabase {
param($sqlserver=$(throw ‘sqlserver required.’), $filepath=$(throw ‘filepath required.’), $action=”, $force, $incremental, $copyonly, $dbname=$(throw ‘dbname required.’), $name=”, $description=”)
#Action can equal ‘Database’ or ‘Log’
$server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $sqlserver
Write-Host “RunSQLBackUp $($server.Name) $dbname”
$backup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
$backupDevice = new-object (“Microsoft.SqlServer.Management.Smo.BackupDeviceItem”) ($filepath+$name), ‘File’
$backup.Action = $action
$backup.BackupSetDescription = $description
$backup.BackupSetName = $name
if (!$server.Databases.Contains(“$dbname”)) {throw ‘Database $dbname does not exist on $($server.Name).’} $backup.Database = $dbname
$backup.Devices.Add($backupDevice)
$backup.Initialize = $force
$backup.Incremental = $incremental
if ($copyOnly) { if ($server.Information.Version.Major -ge 9 -and $smoVersion -ge 10) { $backup.CopyOnly = $true }
else { throw ‘CopyOnly is supported in SQL Server 2005(9.0) or higher with SMO version 10.0 or higher.’ } }
trap {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException) {
Write-Output $ex.InnerException.message
$ex = $ex.InnerException };
continue }
$backup.SqlBackup($server)
if ($?) { Write-Host “$action backup of $dbname to $filepath complete.” -foregroundcolor green }
else { Write-Host “$action backup of $dbname to $filepath failed.” -foregroundcolor red}
}
# Function to restore databases
function RestoreDatabase {
param($sqlserver=$(throw ‘sqlserver required.’), $filepath=$(throw ‘filepath required.’), $action=”, $force, $norecovery, $keepreplication, $filename=$(throw ‘filename required.’), $dbname=$(throw ‘dbname required.’))
#Action can equal ‘Database’ or ‘Log’
$server = new-object (“Microsoft.SqlServer.Management.Smo.Server”)
$sqlserver #$db = $server.Databases($dbname)
Write-Output “RunSQLRestore – $($server.Name) $($filepath+$filename)”
$restore = new-object (“Microsoft.SqlServer.Management.Smo.Restore”)
$restoreDevice = new-object (“Microsoft.SqlServer.Management.Smo.BackupDeviceItem”) ($filepath+$filename), ‘File’
$restore.Action = $action
$restore.Database = $dbname
$restore.Devices.Add($restoreDevice)
$restore.ReplaceDatabase = $force
$restore.NoRecovery = $norecovery
$restore.KeepReplication = $keepreplication
$restoreDetails = $restore.ReadBackupHeader($server)
Write-host (“Backup Header: “+$restoreDetails.Rows.Count)
$restoreFiles = $restore.ReadFileList($server)
Write-Host (“Read Files: “+$restoreFiles.Rows.Count)
if ($restoreFiles.Rows.count -ne 2) { throw “More than 2 logical files found in backup set, not sure how to handle this!!” }
Write-Host (“File Logical Name: “+$restoreFiles.Rows[0].LogicalName)
Write-Host (“File Physical Name: “+$server.Information.MasterDBPath+”\”+$restore.Database+”_Data.mdf”)
$restoreFile = new-object(“Microsoft.SqlServer.Management.Smo.RelocateFile”)
$restoreFile.LogicalFileName = $restoreFiles.Rows[0].LogicalName
$restoreFile.PhysicalFileName = ($server.Information.MasterDBPath+”\”+$restore.Database+”_Data.mdf”)
$restore.RelocateFiles.Add($restoreFile)
Write-Host (“Log Logical Name: “+$restoreFiles.Rows[1].LogicalName)
#If the database resides on a different drive to the master database update the Replace function accordingly
Write-Host (“Log Physical Name: “+($server.Information.MasterDBLogPath.Replace(“S:\”,”T:\”))+”\”+$restore.Database+”_Log.ldf”)
$restoreLog = new-object(“Microsoft.SqlServer.Management.Smo.RelocateFile”)
$restoreLog.LogicalFileName = $restoreFiles.Rows[1].LogicalName
#If the database resides on a different drive to the master database update the Replace function accordingly
$restoreLog.PhysicalFileName = (($server.Information.MasterDBLogPath.Replace(“S:\”,”T:\“))+”\”+$restore.Database+”_Log.ldf”)
$restore.RelocateFiles.Add($restoreLog)
trap {
$ex = $_.Exception
Write-Host $ex.message -foregroundcolor Red
$ex = $ex.InnerException
while ($ex.InnerException) {
Write-Host $ex.InnerException.message -foregroundcolor Red
$ex = $ex.InnerException };
continue }
$restore.SqlRestore($server)
if ($?) {
Write-Host “$action Restore of $dbname from $($filepath+$filename) Complete.” -foregroundcolor Green }
else { Write-Host “$action Restore of $dbname from $($filepath+$filename) Failed.” -foregroundcolor Red }
}
# Function to Configure Security
function ConfigureSecurity {
param($sqlserver=$(throw ‘sqlserver required.’), $dbname, $loginname, $rolename)
Write-Host “Configuring Security for: $sqlserver – $dbname – $loginname – $rolename”
$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)
$sqlserver $database = $srv.Databases[$dbname]
$login = $srv.Logins[$loginname]
if ($login -eq $null) {
Write-Host (“$loginname is not an existing user”)
break }
$usr = $database.Users[$loginname]
if ($usr -eq $null) {
# Not present, so add it
$usr = New-Object (‘Microsoft.SqlServer.Management.Smo.User’) ($database, $loginname)
$usr.Login = $loginname
$usr.Create() }
if ($usr.IsMember($rolename) -ne $True) {
# Not a member, so add that role
$cn = new-object system.data.SqlClient.SqlConnection(“Data Source=$sqlserver;Integrated Security=SSPI;Initial Catalog=$dbname”);
$cn.Open()
$q = “EXEC sp_addrolemember @rolename = N’$rolename’, @membername = N’$loginname’”
$cmd = new-object “System.Data.SqlClient.SqlCommand” ($q, $cn)
$cmd.ExecuteNonQuery() | out-null $cn.Close() }
#$role = $database.Roles[$rolename]
#$role.AddMember($user.Name) }
Write-Host “” Write-Host “1. Backup Databases – Confirm?” -foregroundcolor Green
BackupDatabase -sqlserver “DBServer1″ -filepath “T:\Backups\” -action “Database” -force $true -incremental $false -copyonly $false -dbname “MyDatabase” -name “MyDatabase_20130219.bak” -description “Backup for Restore – 20130219″
Write-Host “” Write-Host “2. Restore Databases – Confirm?” -foregroundcolor Green
RestoreDatabase -sqlserver “DBServer1″ -filepath “T:\Backups\” -action “Database” -force $true -norecovery $false -keepreplication $false -filename “MyDatabase_20130219.bak” -dbname “MyDatabase”
Write-Host “” Write-Host “3. Configure Database Security – Confirm?” -foregroundcolor Green
ConfigureSecurity -sqlserver “DBServer1″ -dbname “MyDatabase” -loginname “MYDOMAIN\sampleAccount1″ -rolename “db_owner”
