Intro

In some scenarios a vendor or other 3rd party will send you a copy of their log shipping backups so you can keep a reporting copy of their database. Today’s post will cover how we can use dbatools to automate the restore process. This post assumes you have already restored the initial full backup and are now at the point where you are ready to automate the transaction log backups you are receiving.

Gather Information

We only need to know a few things to get this script up and running.

  1. SQL Server Instance Name
  2. Database Name
  3. Transaction Log Backup File Path
  4. Standby File Path

The Script

In our development scenario we are going to script the restore of database db_dev on the default instance of SQL Server. To do this we will use the Restore-DbaDatabase command. This script will go through all files and folders within the $BackupPath provided and restore them. All you need to do to take advantage of this is swap out the variable values with your own.

$Instance = '.'
$Database = 'db_dev'
$BackupPath = 'F:\backup_stage'
$StandbyPath = 'F:\standby'

try {
    Restore-DbaDatabase -SqlInstance $Instance -DatabaseName $Database -Path $BackupPath -Continue -StandbyDirectory $StandbyPath -DirectoryRecurse
}
catch {
    "Error occurred restoring DB"
}

Logging

Sometimes unforeseen events occur and cause issues. Capturing logs with your script can make troubleshooting much easier when this happens. I like to use the Start-Transcript command because it is easy to implement and outputs everything the script is doing. Below is an updated version of our script to output logs. It will output a new log file every time it runs.

$FileLog = "F:\script_logs\Restore_" + (Get-Date).ToString("yyyyMMddHHmmss") + ".log"
Start-Transcript -Path $FileLog

$Instance = '.'
$Database = 'db_dev'
$BackupPath = 'F:\backup_stage'
$StandbyPath = 'F:\standby'

try {
    Restore-DbaDatabase -SqlInstance $Instance -DatabaseName $Database -Path $BackupPath -Continue -StandbyDirectory $StandbyPath -DirectoryRecurse
}
catch {
    "Error occurred restoring DB"
}

Stop-Transcript

Conclusion

We covered how to automate the restore process of log files with dbatools. This will leave the database in standby mode so it is ready for the next set of files. We also covered how to implement a basic logging solution so you have some information to work from if issues occur.