Day 11 of 31 Days of SQL Server Backup and Restore using PowerShell: Simple Database Restore

As we’ve seen in the past 10 days, performing SQL Server backups with PowerShell is pretty straight forward. Now we’re going to move on to Restores. This is where PowerShell really starts to shine. SQL Server doesn’t offer any real options for automating restores of Databases for checking or verification. You can schedule specific pieces of T-SQL, but that only applies to specific databases, or you end up tying yourself in knot with dynamic SQL.

As we did with backups we’ll start off with the simplest form of restore to introduce the basic concepts we’ll be building on. Here we’re just going to take a full database backup where we know which database it’s from, restore and recover it.

So here’s the scripts, first off here’s the full SMO version:

import-module "SQLPS" -DisableNameChecking

$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$backupname = "C:\psbackups\psrestore.bak"

$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($backupname,$devicetype)

$restore.Database = "psrestore"
$restore.ReplaceDatabase = $True

As you can see this is pretty much identical to the basic PowerShell backup script¬†from Day 1. The only real differences are that we have an $restore object of type Microsoft.SqlServer.Management.Smo.Restore and we’re calling a sqlrestore method, all the device creation methods are exactly the same.

And here’s the simple form of the Restore-SQLDatabase cmdlet:

Restore-SqlDatabase -ServerInstance "Server1" -Database "psrestore" -BackupFile "C:\psbackups\psrestore.bak"

Which is even simpler.

So far so simple. But as we’ll explore over the next days this gets a lot more complicated when you try to make it flexible, able to cope with complex restores and robust enough to ensure a properly recovered database.

The next step towards that is tomorrow’s post on restoring transaction logs.

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.

Tagged ,

One thought on “Day 11 of 31 Days of SQL Server Backup and Restore using PowerShell: Simple Database Restore

Leave a Reply

Your email address will not be published. Required fields are marked *