#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=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=, 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"

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

Filed in: Columns, Tips and Tricks Tags: , , ,
© 2018 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: