Failed to convert parameter value from a SqlGuid to a String.(System.Data) when creating transaction log

Question

I am applying log shipping to a database using Invoke-DbaDbLogShipping, and i am following the example in the documentation. The jobs get created and the Database is restored on the secondary server instance from what it appears, yet the jobs are failing. This is what the Backup job log states:

Backing up transaction log. Primary Database: 'Test14' Log Backup File: 'C:\Users...\Documents\DB Log Shipping\Backups\Local\Test14\Test14_20210822193000.trn' Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) Error: Object must implement IConvertible.(mscorlib) First attempt to backup database 'Test14' to file 'C:\Users...\Documents\DB Log Shipping\Backups\Local\Test14\Test14_20210822193000.trn' failed because Cannot open backup device 'C:\Users...\Documents\DB Log Shipping\Backups\Local\Test14\Test14_20210822193000.trn

Why is it failing?

Here is my script:

$params = @{
    SourceSqlInstance = $PrimaryServerInstance
    DestinationSqlInstance = $SecondaryServerInstance
    Database = $DatabaseName
    GenerateFullBackup = $true
    BackupNetworkPath = $SharedPath
    BackupLocalPath = $LocalPath
    CompressBackup = $true
    Standby = $true
    BackupScheduleFrequencyType = 'daily' 
    BackupScheduleFrequencyInterval = 1
    CopyScheduleFrequencyType = 'daily'
    CopyScheduleFrequencyInterval = 1
    RestoreScheduleFrequencyType = 'daily'
    RestoreScheduleFrequencyInterval = 1
    CopyDestinationFolder = $TransactionLogsCopyPath
    Force = $true #Ignore some errors in the parameters and assume defaults. It will also remove the any present schedules with the same name for the specific job.
}

Invoke-DbaDbLogShipping @params

Looks like the PS script has issues with error handling. The underlying error is not being able to access the backup location. Is the backup location located on *the server* (not your client workstation) and is it accessible to the SQL Server service account (I suspect not given the path, it looks like a local user account's Documents) — Charlieface, Aug 22 '21 at 21:32
@Charlieface i changed the logon account to my local service account logon so this way any permissions required would be good to go since my local account acts as admin on the entire directory structures. I restarted the agent jobs following this change, yet, the error still persisted... — Cataster, Aug 22 '21 at 23:00
The account you login to SQL Server with (or that a job is executing with) is not relevant, what is relevant is `NT SERVICE\MSSQLSERVER` login and its permissions, this is the account that the `sqlservr.exe` service process uses — Charlieface, Aug 22 '21 at 23:01
@Charlieface yep, so i changed `NT SERVICE\MSSQLSERVER` to my local logon since im admin — Cataster, Aug 22 '21 at 23:02
You changed SQL Server to use your own login and restarted the service? Inadvisable to run as an admin or local user. You are better off using the normal service account, this is why it exists, just give the service account the right permissions. Is the directory created already? Is there no file with the same name? — Charlieface, Aug 22 '21 at 23:21
@Charlieface This is what the latest log is showing: `Starting transaction log backup. Retrieved backup settings. Backup Directory: 'C:\Users\...\Documents\DB Log Shipping\Backups\Local\Test18'. Backing up transaction log. Log Backup File: 'C:\Users\...\Documents\DB Log Shipping\Backups\Local\Test18\Test18_20210822232500.trn'. First attempt to backup database 'Test18' to file 'C:\Users\...\Documents\DB Log Shipping\Backups\Local\Test18\Test18_20210822232500.trn' failed because Cannot open backup device 'C:\Users\...\Documents\DB Log Shipping\Backups\Local\Test18\Test18_20210822232500.trn'` — Cataster, Aug 22 '21 at 23:34
@Charlieface I resolved the issue! At `C:\Users\...\Documents\DB Log Shipping\Backups\Local` there was no `Test18` directory created. I created the folder Test18 manually, and the backup is now succeeding. However, this is insane, because the `Invoke-DbaDbLogShipping` should have created the Test18 folder when i executed the command. Apparently its only creating the folder under the `$SharedPath` and `$TransactionLogsCopyPath` but NOT `$LocalPath`. I ended up just leaving the local path the same as the network drive path because I wont create the folders in local path manually, obviously ;) — Cataster, Aug 23 '21 at 00:56

Cataster · Accepted Answer · 2021-09-08T05:14:15.073

I resolved the issue!

At C:\Users\...\Documents\DB Log Shipping\Backups\Local there was no Test14 directory created. I created the folder Test18 manually, and the backup is now succeeding.

However, this is insane, because the Invoke-DbaDbLogShipping should have created the Test18 folder AUTOMATICALLY when i executed the command. Apparently its only creating the folder under the $SharedPath and $TransactionLogsCopyPath but NOT $LocalPath. For automation purposes, I ended up just setting the local path to the $SharedPath

Failed to convert parameter value from a SqlGuid to a String.(System.Data) when creating transaction log

1 Answers1