Installing SQL Server Express and Restoring a Database With Powershell

Here's a powershell script to install SQL Server Express Edition and restore a database from a commmandline.  It's intended to be used as part of an installation script for an application that needs a local SQL Express instance.  But it also demonstrates several SQL Server and Powershell interop features like handling InfoMessages from the server, dealing with resultsets, embedding TSQL commands with Powershell Here Strings.

Anyway the script assumes that it is sitting in a folder next to the backup file and the SQL Express install media.  You would supply the name of your database to restore and the instance name if you want something other than the default "SQLExpress".

eg

 PS C:\install> dir
    Directory: C:\install
Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----        4/22/2016   5:29 PM                SQLExpress
-a----        4/23/2016   7:42 AM        2380288 foo.bak
-a----        4/23/2016   4:18 PM           2803 InstallSQLExpressAndRestoreDatabase.ps1

And here's the script:

InstallSQLExpressAndRestoreDatabase.ps1

---

 

 #run this script from an elevated command prompt to install SQL Server Express edition and restore 
$ScriptDir = Split-Path $PSCommandPath

 #enter database name
$databaseName = "foo"
#enter backup location
$backupLocation = "$ScriptDir\foo.bak"
#enter instance name
$instanceName = "SQLExpress"

 #---------------------------------------------------------
#              INSTALL SQL SERVER EXPRESS EDITION
#---------------------------------------------------------

 $serviceAccount = "NT Service\MSSQL`$$($instanceName)"
if ($instanceName -eq "MSSQLSERVER")
{
    $serviceAccount = "NT Service\MSSQLSERVER"
}
$cmd  = @"
"$ScriptDir\SqlExpress\setup.exe" /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install  /ROLE=AllFeatures_WithDefaults  /INSTANCENAME=$($instanceName) /SQLSVCACCOUNT="$($serviceAccount)"  
"@

 write-host "Installing"
write-host $cmd
& cmd.exe /c $cmd

 #---------------------------------------------------------
#              RESTORE YOUR DATABASE
#---------------------------------------------------------
$constr = "server=(local)\$instanceName;database=master;integrated security=true"

 $sql = @"
select InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
select InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')
"@
$con = new-object system.data.sqlclient.sqlconnection $constr

 $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($s, $a)  
                foreach ($e in $a.Errors) 
                { 
                    write-host "SQL Message (Number: $($e.Number) Severity: $($e.Class) Line Number: $($e.LineNumber)) $($e.Message)" -ForegroundColor Red
                } 
             }; 
$con.add_InfoMessage($handler); 
$con.FireInfoMessageEventOnUserErrors = $true;

 $con.Open()
$cmd = $con.CreateCommand()

 $cmd.CommandText = "select InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath')"
$dataDir = $cmd.ExecuteScalar().ToString()

 $cmd.CommandText = "select InstanceDefaultDataPath = serverproperty('InstanceDefaultLogPath')"
$logDir = $cmd.ExecuteScalar().ToString()

 $cmd.CommandText = "restore filelistonly from disk='$backupFile' with file=1";
$rdr = $cmd.ExecuteReader()
$dt = new-object System.Data.DataTable
$dt.Load($rdr)

 $dataFileName =  ($dt.Rows | where Type -EQ D | select -First 1).LogicalName
$logFileName =   ($dt.Rows | where Type -EQ L | select -First 1).LogicalName

 $cmd.CommandText = @"

 restore database [$databaseName] 
  from disk='$backupLocation'
  with 
   MOVE '$($dataFileName)' TO '$($dataDir)$($dataFileName)_Data.mdf', 
   MOVE '$($logFileName)'  TO '$($dataDir)$($logFileName)_Log.ldf'
"@;
$cmd.CommandText
$cmd.ExecuteNonQuery() | out-null

 write-host "Restore complete"
$con.Close()