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. Pokémon Nut. 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