Posted on Leave a comment

Restore transaction log backup on MS SQL server using powershell

Recently working on a pipeline, I wanted to restore multiple transaction log backups on a MS SQL server. The files were located under R:\files\incremental.

You can use below powershell in order to do the restore automatically.

$folder_for_cleanup = "R:\files\incremental"
Get-ChildItem $folder_for_cleanup | Sort -Property FullName | ForEach-Object {
Write-Host restoring $_.FullName
sqlcmd -Q "RESTORE LOG [Database_Name] FROM DISK=N'$_' WITH NORECOVERY" -o R:\files\results\incrementalresult.txt; #writing a log output
[string]$result = Get-Content R:\files\results\incrementalresult.txt
if ($result.contains('terminates')) {
Write-Host backup is already present in the database, skipping …
Remove-Item $_.FullName -Force -Confirm:$false
}
else {
Write-Host sucessfully restored $_.FullName
Remove-Item $_.FullName -Force -Confirm:$false
}
}

The above powershell will try to restore the X transaction log in the database. If the one is already restored, it will skip, else the log will be restored.