Restore Database Using PowerShell

Quick script to restore AdventureWorks to a local instance. This script was designed to run on a local box, so the connection was trusted and therefore no username/password was required. This also goes some way to explain why I am using the location of the master database files as the same place to restore the AdventureWorks database! But with only a few changes it can easily be altered to restore any database, and the file location of the mdf/ldf’s can also be altered to reflect different locations.

The script needs to be saved in the same location as the .bak file for it to run successfully.

#restore adventureworks
$path = get-childitem $PSScriptRoot -include "AdventureWorks2014.bak" -recurse
$SQLDatabase = $path.Name
$SQLDatabase = $SQLDatabase.TrimEnd(".bak")

$SQLConn = New-Object System.Data.SQLClient.SQLConnection
$SQLConn.ConnectionString = "Server=$SQLServer; Trusted_Connection=True"

try
{
$SQLConn.Open()

write-host "success" -ForegroundColor Green
$SqlConn.Close()
}
catch {
Write-warning "An exception was caught while attempting to open the SQL connection"
Break
}

$SQLConn.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command = $SQlconn.CreateCommand()
$Command.CommandTimeout =0
$Command.CommandText = "select top 1 physical_name
from master.sys.master_files
where database_id = 1"
try{
$Reader = $Command.ExecuteReader()
while ($Reader.Read())
{
$r = $Reader.GetValue($1)
$r = $r.ToString()
$r = $r.TrimEnd("\master.mdf")
}
$SQLConn.Close()
}
catch
{
write-host "something went wrong"
write-host "$_"
}

$SQLConn.Open()
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLcmd = $SQLconn.CreateCommand()
$sqlcmd.commandtimeout=0
$SQLcmd.CommandText="IF EXISTS(select * from sys.databases where name='$SQLDatabase')
ALTER DATABASE $SQLDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE $SQLDatabase
FROM DISK = '$path'
WITH  FILE = 1, REPLACE,
MOVE N'AdventureWorks2014_Data'
TO N'$r\AdventureWorks2014_Data.mdf',
MOVE N'AdventureWorks2014_Log'
TO N'$r\AdventureWorks2014_Log.ldf'"

$starttime = Get-date
try{
$SQLcmd.Executenonquery() | out-null
write-host "aventureworks deployed" -ForegroundColor Green
}
catch{
write-warning "An Exception was caught while restoring the database!"
write-warning "$_"
write-warning "attempting to recover the database"
}
Write-Host "Press Any Key To Exit"
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
(Get-Host).SetShouldExit(0)

Happy Scripting!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s