###########################################################################
# NAME: Restore Backup With Dynamic Filename
#
# COMMENT: Using Ola's backup script creates dynamic file names of the .bak file
#          extension. This script gets the file name and uses it in the sqlcmd restore
###########################################################################

#Variables
$folder = "\\server\networkShare\"
$sqlInstance = "SQLInstance"
$backupFile = Get-ChildItem $folder | Where {$_.Extension -eq ".bak"} | Sort-Object CreationTime | select -last 1
$backupFilePath = $folder + $backupFile

#Build restore string
[string] $dbCommand = 'sqlcmd /Q "RESTORE DATABASE [DB] ' +
"FROM    DISK = N'$backupFilePath' " +
"WITH    FILE = 1, " +
"MOVE N'DB_Data' TO N'C:\Data\DB_Data.MDF', " +
"MOVE N'DB_Log' TO N'C:\Data\DB_Log.LDF'," +
'NOUNLOAD, REPLACE"'

#Run SQLCMD to restore backup
Invoke-Expression $dbCommand