ExecuteWithResults Exception?

Question

I am running the following command as part of DB Log Shipping POC but getting exception:

$params = @{
    SourceSqlInstance = 'localhost'
    DestinationSqlInstance = 'localhost\MSSQLSERVER01'
    Database = 'Test'
    GenerateFullBackup = $true
    BackupNetworkPath = '\\sql1\logshipping'
    BackupLocalPath = 'C:\Users\...\Documents\DB Log Shipping\Backups'
    CompressBackup = $true
    Force = $true
}

Invoke-DbaDbLogShipping @params

Exception:

Exception calling "ExecuteWithResults" with "1" argument(s): "An exception occurred while executing a Transact-SQL statement or batch."
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.173\allcommands.ps1:78578 char:17
+ ...             $batchresult = $server.ConnectionContext.ExecuteWithResul ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ExecutionFailureException

Cannot index into a null array.
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.173\allcommands.ps1:78580 char:25
+ ...         if ($batchresult.Tables.rows[0] -eq $true -or $batchresult.Ta ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

What could be the issue here?

Seems like `Test-DbaPath` doesn't test that `$batchresult` actually holds a dataset before tryng to enumerate its datatables. Does the path `C:\Users\...\Documents\DB Log Shipping\Backups` (with three dots) actually exist on the SQL Server's drives? — AlwaysLearning, Jul 23 '21 at 08:19
@AlwaysLearning i assumed what is meant by local path is any local path of my choosing, since I am connect to localhost anyways...am i supposed to place the backup folder under the SQL Server 2019 installation directory? — Cataster, Jul 23 '21 at 08:22
It can be anywhere on the SQL Server's local drives. You just need to ensure that: a) it exists, and b) the account running the SQL Server service has sufficient permissions to be able to write there. — AlwaysLearning, Jul 23 '21 at 08:26
@AlwaysLearning it definitely has all the access it needs since its being done under my machine acting as the server, where im Admin — Cataster, Jul 23 '21 at 08:27
You're the admin. What about SQL Server instance, though? What account does it run under? — AlwaysLearning, Jul 23 '21 at 08:28
@AlwaysLearning its connected as me in SSMS, and the sql server service is logged on as `NT Service\MSSQLSERVER` and for instance 01 as `NT Service\MSSQL$MSSQLSERVER01` — Cataster, Jul 23 '21 at 08:30
@AlwaysLearning question, Im readin gthis post here https://sqlbackupandftp.com/blog/powershell-how-to-backup-and-recover-an-sql-server-database-faq and Im confused, is backing up and restoring the same thing as DB log shipping? I see at the end it says how to backup transaction log...doesnt that get backed up as part of the .bak file i thought? — Cataster, Jul 23 '21 at 08:50

ExecuteWithResults Exception?

0 Answers0