#PSTip Restoring a SQL Database using SMO

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! 🙂