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