This is causing me to pull my hair out. I'm trying to use DbaTools to restore a backup and log files to a new SQL Server. This is what I am running:

$File = Get-ChildItem '\\bckp01\sql_backups$\Business\FULL_COPY_ONLY', '\\bckp01\sql_backups$\Business\LOG' | Where-Object {$_.CreationTime -gt (Get-Date).AddDays(-1)}

$File | Restore-DbaDatabase -SqlInstance Server2 -Database Business -NoRecovery -OutputScriptOnly -ReuseSourceFolderStructure -WhatIf

The error that is returned is:

[Restore-DbaDatabase] Failure | Cannot index into a null array.

I have verified $File contains a list of all the DB full and logfile backups.

The filter for the last 24 hours of files I put in to cut the scanning time down - we save 4 days of log backups. I only need files for the last day. The 24 hour window includes the last full backup and all transaction log backups since the full.

If I just try to restore a full backup, things work.

What am I doing wrong?

This is causing me to pull my hair out. I'm trying to use DbaTools to restore a backup and log files to a new SQL Server. This is what I am running:

$File = Get-ChildItem '\\bckp01\sql_backups$\Business\FULL_COPY_ONLY', '\\bckp01\sql_backups$\Business\LOG' | Where-Object {$_.CreationTime -gt (Get-Date).AddDays(-1)}

$File | Restore-DbaDatabase -SqlInstance Server2 -Database Business -NoRecovery -OutputScriptOnly -ReuseSourceFolderStructure -WhatIf

The error that is returned is:

[Restore-DbaDatabase] Failure | Cannot index into a null array.

I have verified $File contains a list of all the DB full and logfile backups.

The filter for the last 24 hours of files I put in to cut the scanning time down - we save 4 days of log backups. I only need files for the last day. The 24 hour window includes the last full backup and all transaction log backups since the full.

If I just try to restore a full backup, things work.

What am I doing wrong?

• database-restore
• dbatools

1 Answer 1

Never found the reason for this, which is annoying as this is pretty much exactly the same as Example 9 at the man page for the command (https://docs.dbatools.io/Restore-DbaDatabase.html)

My workaround was to break the process up into two pieces - one command for the full backup restore and one for the log backup restores:

$File = Get-ChildItem -LiteralPath '\\it-sql-bckp-01\sql_backups$\Business\FULL_COPY_ONLY' | Where-Object {$_.CreationTime -gt (Get-Date).AddDays(-1)}
$File | Restore-DbaDatabase -SqlInstance Server2 -Database Business -NoRecovery -ReuseSourceFolderStructure

$File = Get-ChildItem -LiteralPath '\\it-sql-bckp-01\sql_backups$\Business\LOG' | Where-Object {$_.CreationTime -gt (Get-Date).AddDays(-1)}
$File | Restore-DbaDatabase -SqlInstance Server2 -Database Business -NoRecovery -ReuseSourceFolderStructure -Continue

Apparently, putting files from two different directories into $File at once doesn't work (again, even though that is what the dbatools example shows).