Automating DB Restore from network share

Question

We have a network share that uses different credentials from the currently logged in user. Inside this share we have full, differential and log backups that come through from a third party database that are updated daily.

I've been tasked with automating the restoration of these backups to a local database.

What's the best way to go about designing and implementing a solution for this?

Folder Structure

Log File structure

As can be seen above, there are multiple files in each folder with different file names depending on the date, etc.

Update:

Following on from the solution posted below, I had to modify the suggested PowerShell code slightly in order to make it work:

$pass ="xxx"|ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PsCredential('Domain\Account',$pass)

New-PSDrive -Name K -PSProvider "FileSystem" -Credential $cred -Root '\\UNC PATH TO FOLDER WHERE BACKUP IS STORED';
Get-PsDrive;

Copy-Item K:\ -Recurse M:\DESTINATION;

Restore-DbaDatabase -server MRVAPPSQL01 -path M:\DESTINATION -MaintenanceSolutionBackup -WithReplace;

Remove-PSDrive -Name K;

Remove-Item M:\DESTINATION\*.*;

Answer 1

It appears that these backups are being made using Ola Hallengren's Maintenance Solution so I'm going to write this based upon that assumption.

The dbatools PowerShell module has a function which does exactly what you need - Restore-DbaDatabase. For a basic restore:

Restore-DbaDatabase -server DESTINATIONSERVER -path \\BackupServer\Path\To\Backups -MaintenanceSolutionBackup

There are additional parameters you can specify to put the files in a location other than the instance's default paths, change the name of the database or the files, or even a point in time earlier than the last backup (but this point in time must be reachable from the files given).

The account under which your destination SQL Server instance needs read access to the UNC path where your backups are stored. If that's not possible, you'll need to copy that directory structure to somewhere that is accessible or map a drive using alternate credentials.

To do that, you can use New-PSDrive to map a drive using alternate credentials (you'll want to save those credentials securely if you're doing this from a script that runs via Task Scheduler).

New-PSDrive -Name R -Root \\BackupServer\Path\To\Backups;
Copy-Item -path r:\*.* -destination \\Path\That\Destination\Instance\Can\Read
Restore-DbaDatabase -server DESTINATIONSERVER -path \\Path\That\Destination\Instance\Can\Read;
Remove-PSDrive -Name R;