2

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

Filed in: Columns, Tips and Tricks Tags: , , ,

2 Responses to "#PSTip Restoring a SQL Database using SMO"

  1. Greg Bray says:

    Note you probably want to catch any exceptions that occur so that you know what the issue is. I used:

    try {
    $restore.SqlRestore($server)
    } catch [Exception] {
    Write-Error $_.Exception.ToString()
    }

Leave a Reply

Submit Comment

© 2016 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: