3

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 Folder

Log file 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\*.*;
4

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;
  • Thank you for this suggestion. I never knew about dbatools; it looks very useful. – Asher May 10 '18 at 2:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.