$backupRoot = Get-ChildItem -Path "F:\F_SQLBackup1\SQLBackup\CTASK0046007"
$datafilesDest = "F:\F_SQLData1\SQLData"
$logfilesDest = "F:\F_SQLLog1\SQLLog"
$FilestreamDest = "F:\F_SQLData1\SQLData"
$server = "USPILVMUMCSSQ01\INST1"
# Create some “Relocate” file objects to pass to the Restore-SqlDatabase cmdlet…
## For each folder in the backup root directory...
#
foreach($folder in $backupRoot)
{
# Get the most recent .bak files for all databases...
$backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*.bak" -Recurse | Sort-Object -Property CreationTime -Descending | Select-Object -First 1
# For each .bak file...
foreach ($backupFile in $backupFiles)
{
# Restore the header to get the database name...
$query = "RESTORE HEADERONLY FROM DISK = N'"+$backupFile.FullName+"'"
$headerInfo = Invoke-Sqlcmd -ServerInstance $server -Query $query
$databaseName = $headerInfo.DatabaseName
# Restore the file list to get the logical filenames of the database files...
$query = "RESTORE FILELISTONLY FROM DISK = N'"+$backupFile.FullName+"'"
$files = Invoke-Sqlcmd -ServerInstance $server -Query $query
# Differentiate data files from log files...
$dataFile = $files | Where-Object -Property Type -EQ "D"
$logFile = $files | Where-Object -Property Type -EQ "L"
$Filestream = $files | Where-Object -Property Type -EQ "S"
# Set some variables...
$dataFileName = $dataFile.LogicalName
$logFileName = $logFile.LogicalName
$FilestreamName = $Filestream.LogicalName
# Set the destination of the restored files…
$relocate=@()
foreach ($dataFileName_one in $datafilename){
$dataFileFullPath = $datafilesDest+”\”+$dataFileName_one+”.mdf”
$obj= New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dataFileName_one,$dataFileFullPath)
$Relocate+=$obj
}
foreach ($logFileName_one in $logFileName){
$logFileFullPath = $logfilesDest+”\”+$logFileName_one+”.ldf”
$obj= New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logFileName_one,$logFileFullPath)
$Relocate+=$obj
}
foreach ($Filestream_one in $FilestreamName){
$FilestreamPath = $FilestreamDest+”\”+$Filestream_one
$obj= New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Filestream_one,$FilestreamPath)
$Relocate+=$obj
}
# Create some "Relocate" file objects to pass to the Restore-SqlDatabase cmdlet...
$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $dataFileName, $dataFileFullPath
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $logFileName, $logFileFullPath
# Perform the database restore... and then go around the loop.
Restore-SqlDatabase -ServerInstance $server -Database $databaseName -BackupFile $backupFile.FullName -RelocateFile $relocate -ReplaceDatabase
}
}
