I have a requirement to restore database using powershell with multiple backup files.
I think the below query prints out what needs to be executed but i am unsure of how to use invoke-sqlcmd and execute everything together.
Appreciate your help
$backupRoot = Get-ChildItem -Path "\\<network shared drive\"
$dbname = 'Database1'
$server = 'server1'
$query0 = "USE [master] RESTORE DATABASE $dbname"
foreach($folder in $backupRoot)
# Get the most recent .bak files for all databases...
$backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*$dbname*.bak" -Recurse | Sort-Object -Property CreationTime | Select-Object -First 1
# For each .bak file...
foreach ($backupFile in $backupFiles)
$query1 = "FROM DISK = N'"+$backupFile.FullName+"'"
#Invoke-Sqlcmd -ServerInstance $server -Query $query
$query2 = "WITH FILE = 1,
MOVE N'INR_Data' TO N'G:\Database\$dbname.mdf', MOVE N'INR_Log' TO N'L:\Log\$dbname.ldf',
NOUNLOAD, STATS = 5