Restoring database with powershell

Related to  SFTP Transfer script with logging -post, I needed to a way to schedule database restore every night. After a bit of googleing and copy pasteing I came up with following script.

Short descrition

1. Look for latest file in folder
2. Drop existing datatabase
3. Restore latest backup
4. Add dbuser rights

Requirements:
- PowerShell Logging Module (https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85)
- SQLServer -Powershell module
- SQLASCmdlets -Powershell module


<#.Synopsis
This scripts restores SQL Database from BAK file and relocates files

.Description
Writen by Miikka Kallberg

.Parameter SQLInstance
SQL Instance name

.Parameter Database
Database name

.Parameter DBBAKPath
Path of BAK file

.Parameter DBDataPath
Database Data Path

.Parameter DBLogPath
Database Log Path

.Parameter logpath
Script log Path

.Parameter synclogfile
Script Log file

.Parameter DBUser
user account that will be added to database

.Parameter DBUserRole
role for that user

.Example
.\restore_tk.ps1
Restores Database with values configured below

#>
param (
# Edited 30.03.2017 Miikka Kallberg /CSC - IT Center for Science Ltd.

$SQLInstance = "",  # SQL Instance name
$Database = "", # Database name
$DBLogicalName = "", # Database logical name
$DBBAKPathFolder = "", #Path of BAK file
$DBDataPath = "", #Database Data Path
$DBLogPath = "", #Database Log Path
$logpath = "", # Log Path
$synclogfile = "", # Log file
$DBUser = "", #user account that will be added to database
$DBUserRole ="" #role for that user
)
#This script requires Powershell Logging and SQLASCmdlets modules to be imported to powershell modules folder

# This is a custom module and needs to be installed before running this script
# Default location for PowerShell Modules is: C:\Windows\System32\WindowsPowerShell\v1.0\Modules
# https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85

Import-Module SQLServer
Import-Module SQLASCmdlets
Import-Module PowerShellLogging

#Add timestamp to script log
get-date | out-file -filePath (Join-Path $logpath $synclogfile) -Encoding ascii -Force

#Start logging
#Format date and time for the logfilename
$sdate = ((get-date).ToString("yyyyMMdd_HHmmss_"))

#Combine logfile name with time
$synclog =  $sdate + $synclogfile

#Define logfile
$LogFile = Enable-LogFile -Path (Join-Path $logpath $synclog)

# Note - it is necessary to save the result of Enable-LogFile to a variable in order to keep the object alive.  As soon as the $LogFile variable is reassigned or falls out of scope, the LogFile object becomes eligible for garbage collection.

$VerbosePreference = 'Continue'
$DebugPreference = 'Continue'

#Start restore script

# Get latest file from network path
$DBBAKfilename = Get-ChildItem $DBBAKPathFolder | select -last 1
$DBBAKfile = $DBBAKfilename.Name
$DBBAKPath = $DBBAKPathFolder + $DBBAKfile

#Drop existing Database
$databasePath = join-path $SQLInstance "Databases\$Database"
Invoke-SqlCmd "USE [master]; ALTER DATABASE [$Database] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$Database] "

#$DBLogicalName = $Database
$DBDataRelocPath =  $DBDataPath + $Database + ".mdf"
$DBLogRelocPatch =  $DBLogPath + $Database + ".log"
$DBLogicalName_Log = $DBLogicalName +"_log"
$Database_Log = $Database +"_log"
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$DBLogicalName", "$DBDataRelocPath")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$DBLogicalName_Log","$DBLogRelocPatch")

Restore-SqlDatabase -ServerInstance $SQLInstance -Database $Database -BackupFile $DBBAKPath  -ReplaceDatabase  -RelocateFile @($RelocateData,$RelocateLog) -RestoreAction Database

#Rename logical name
Invoke-SqlCmd "ALTER DATABASE [$Database] MODIFY FILE (NAME=N'$DBLogicalName',NEWNAME=N'$Database') "
Invoke-SqlCmd "ALTER DATABASE [$Database] MODIFY FILE (NAME=N'$DBLogicalName_Log',NEWNAME=N'$Database_Log') "

# Add user rights to database
# Define server defaults
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance

# Define Databse
$db = $svr.Databases[$Database]

# Add User
$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $DBUser)
$usr.Login = $DBUser
$usr.Create()

#Add user to role
$Rol = $db.Roles[$DBUserRole]
$Rol.AddMember($DBUser)
Write-Host "$DBUser added to $DBUserRole Role in $Database on $SQLInstance "
 
# Disable logging before the script exits (good practice, but the LogFile will be garbage collected so long as this variable was not in the Global Scope, as a backup in case the script crashes or you somehow forget to call Disable-LogFile).

$LogFile | Disable-LogFile