Note: This tip requires PowerShell 2.0 or above.
In a couple of earlier tips on using SMO, we looked at backing up a SQL database and transaction logs. In today’s tip, we will look at how we can perform a database restore using SQL SMO and PowerShell.
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Add-Type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" $server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName $restore = new-object Microsoft.SqlServer.Management.Smo.Restore -Property @{ Action = 'database' Database = 'MyDB' ReplaceDatabase = $true NoRecovery = $false } $device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem -ArgumentList "C:\Intel\MyDB.bak","File" $restore.Devices.Add($device) $restore.SqlRestore($Server)
Make a note that this code will restore database to the default SQL data file path. If we need to relocate the files to a different path during restore, we need to follow a different path. Let us save it for another day and another tip! 🙂