The SQL Server Agent objects are in the Microsoft.SqlServer.Management.Smo.Agent namespace. Examples To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. Summary: Microsoft PFE, Thomas Stringer, talks about using Windows PowerShell and tuning SQL Server Management operations. Microsoft Scripting Guy, Ed Wilson, is here. I would like to welcome back guest blogger, Thomas Stringer I spend a lot of time talking with customers and working with them regarding how they can manage their large enterprise SQL Server environments seamlessly with.
- Set Property Devices To Accomplish This Action. (microsoft.sql Server.express.smo)
- Set Property Devices To Accomplish This Action. (microsoft.sqlserver.smo)
| # Example of using SMO to restore |
| # requires -Version 4.0 -Modules dbatools |
| # fyi: example assumes your backup path is C:backup |
| set-Variable-Name DbRestoreTime -value (get-date) -Force |
| Get-ChildItem-LiteralPath (Read-Host-Prompt 'C:Program Files (x86)Microsoft SQL Server130SDKAssemblies') -Filter 'Microsoft.SqlServer.*'|ForEach-Object-Process { |
| Add-Type-LiteralPath $_.FullName-PassThru |
| } |
| $ServerName= (Read-Host-Prompt 'Server Name') |
| $DatabaseName= (Read-Host-Prompt 'DatabaseName') |
| $BackupPath= [io.path]::combine('$ServerNamec$','BACKUP') |
| $MatchedBackups=@(Get-ChildItem$BackupPath-Filter *.bak).Where({ |
| $_.BaseName-match'.*$DatabaseName.*' |
| }) |
| $BackupToRestore=@($MatchedBackups|Sort-Object-Property CreationTime -Descending).Where({ |
| $_ |
| },'First',1) |
| $DataDirectory= (Read-Host-Prompt 'Path for Data/Log Files') |
| $LogDirectory= (Read-Host-Prompt 'Path for Log Files') |
| $s= [Microsoft.SqlServer.Management.Smo.Server]::New($ServerName) |
| $s.ConnectionContext.StatementTimeout=0 |
| #otherwise large restores fail in approx 10 m |
| Set-Variable-Name BackupDirectory -Value $s.Settings.BackupDirectory-Verbose -Force |
| Set-Variable-Name DataDirectory -Value $s.Settings.DefaultFile-Verbose -Force |
| Set-Variable-Name LogDirectory -Value $s.Settings.DefaultLog-Verbose -Force |
| $restore= [Microsoft.SqlServer.Management.Smo.Restore]::new() |
| $restore.Devices.AddDevice($BackupPath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File) |
| $restore.ReplaceDatabase=$true#$($force.IsPresent) |
| $restore.Database=$DatabaseName |
| #event handler to stream back progress on the restore (found from post on http://www.sqlservercentral.com/Forums/Topic1671065-3411-1.aspx) |
| $percentEventHandler= [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { |
| Write-Progress-Activity ('RESTORE: {0}'-f$DatabaseName) -PercentComplete $_.Percent |
| } |
| $completedEventHandler= [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { |
| Write-Information-MessageData ('RESTORE COMPLETED: $DatabaseName`nTotal time to process: {0:g}'-f [timespan]::fromseconds(((Get-Date)-$DbRestoreTime).Totalseconds).ToString('hh:mm:ss')) |
| } |
| $restore.add_PercentComplete($percentEventHandler) |
| $restore.add_Complete($completedEventHandler) |
| $restore.PercentCompleteNotification=1 |
| $dataFileNumber=0 |
| #$files = $restore.ReadFileList($s) |
| foreach ($filein$restore.ReadFileList($s)) |
| { |
| $relocateFile= [Microsoft.SqlServer.Management.Smo.RelocateFile]::new() |
| #$relocateFile = new-object 'Microsoft.SqlServer.Management.Smo.RelocateFile'; |
| $relocateFile.LogicalFileName=$file.LogicalName |
| if ($file.Type-eq'D') |
| { |
| if($dataFileNumber-ge1) |
| { |
| $suffix='_$($dataFileNumber)' |
| } |
| else |
| { |
| $suffix=$null |
| } |
| $relocateFile.PhysicalFileName= ([io.path]::combine($DataDirectory,'$($DatabaseName)$($suffix).mdf')) |
| $dataFileNumber++ |
| } |
| else |
| { |
| $relocateFile.PhysicalFileName= ([io.path]::combine($LogDirectory,'$($DatabaseName)$($suffix).ldf')) |
| } |
| $null=$restore.RelocateFiles.Add($relocateFile) |
| } |
| Write-Information-MessageData ('------ READY TO BEGIN RESTORE ON: $ServerName for $DatabaseName ------ |
| Processing $BackupToRestore |
| SqlServerInstance: $($ServerName) |
| DatabaseName: $($DatabaseName) |
| LogicalFileName: $($relocateFile.LogicalFileName) |
| FileType $($file.Type) |
| ' |
| ) -Tags Summary |
| Write-Information-MessageData ($restore.RelocateFiles| |
| Format-Table| |
| Out-String) -Tags Summary |
| $restore.SqlRestore($s) |
Hi cwils14,
I give an answer I found in the web:
'From my experience... if that happens:
1. make sure the User Account you are logged in as has DBO rights on both, the MASTER Db and the DB you want to backup.
2. using the SQL Express Manager, Expand the SQL Server Objects in the left pane and find 'Backup Devices'
create a new 'Device to File' and call it the same as the DB to be backed up... pointing to a directory on your server (e.g.: C:MySQLBackups...)
This will create an 'empty file' with the .bak extention. (e.g. C:MySQLBackupsDatabaseName.bak)
Then right-click on this new device and select 'backup database'...
The rest is straight forward...
Set Property Devices To Accomplish This Action. (microsoft.sql Server.express.smo)
This worked for me... Good luck!'
Source: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/e235c1d6-d690-4fa0-80cf-315b7526d1cf
Set Property Devices To Accomplish This Action. (microsoft.sqlserver.smo)
Let us know if does it work.