Powershell script to iterate through folder and restore SQL backups I am working on a PowerShell script which will loop through a directory containing SQL Server database backup .bak files; move the files to a different location; restore each of those files to the local SQL instance using OSQL.exe; output the SQL output to a log file. 

This works OK, but I am willing to improve it. 

$sourceBackupDirectory = "C:\FTP"
$sqlDatabaseLocation = "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\"
$backupsLocation = "C:\DatabaseRestore\"

Get-ChildItem $sourceBackupDirectory -Filter "*.bak" -Recurse | Move-Item -Destination $backupsLocation #move files
$backupsLocationFiles = Get-ChildItem $backupsLocation -Filter "*.bak" #load files into array

$todayUK = Get-Date -format yyyyMMdd
$logsFolder = "LogFiles\"
$logFile = $backupsLocation + $logsFolder + $todayUK + ".txt"

foreach ($file in $backupsLocationFiles)
{
    $filePath = $backupsLocation + $file
    $file = [io.path]::GetFileNameWithoutExtension($file)
    #$filePath
    #$file

    RestoreDB $file $filePath
}

function global:RestoreDB ([string] $newDBName, [string] $backupFilePath)
{
    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
    "FROM    DISK = N'$backupFilePath' " +
    "WITH    FILE = 1, " + 
    "MOVE N'$newDBName' " + 
    "TO N'$sqlDatabaseLocation" + "$newDBName.mdf', " + 
    "MOVE N'$newDBName" + "_log' " +
    "TO N'$sqlDatabaseLocation" + "$newDBName" + "_log.ldf', " + 
    "NOUNLOAD, REPLACE, STATS = 5"
    Write-Host $dbCommand

    OSQL.EXE -E -Q $dbCommand &gt;&gt; $logFile #this ouputs the SQL output into a logfile. 
}
This is mostly style things, but:


You should store all your constants at the top (including $logsFolder)
#this ouputs the SQL output into a logfile: you misspelled outputs
You should use the string concatenation syntax:



"TO N'$($sqlDatabaseLocation)$($newDBName)_log.ldf',"




You only use $todayUK once, so you should include it directly as a string instead of declaring it.
Instead of concatenating each string component together, use a multi line string operator like @" or a trailing backtick `.



    [string] $dbCommand = @"RESTORE DATABASE [$newDBName]
FROM    DISK = N'$backupFilePath' 
WITH    FILE = 1,
MOVE N'$newDBName'
TO N'$($sqlDatabaseLocation)$($newDBName).mdf', 
MOVE N'$($newDBName)_log'
TO N'$($sqlDatabaseLocation)$($newDBName)_log.ldf',
NOUNLOAD, REPLACE, STATS = 5"




Now, you can just directly call that string instead of assigning it to $dbCommand:



    Write-Host @"RESTORE DATABASE [$newDBName]
FROM    DISK = N'$backupFilePath' 
WITH    FILE = 1,
MOVE N'$newDBName' #more stuff here