Powershell with SMO Assemblies

Posted By 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:

  1. Included in SQL Server Management Studio or
  2. 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”


Blog Posted In Blog Posted In: Project Server
Blog Posted In 

Leave a Reply

Your email address will not be published. Required fields are marked *