SQL cmdlet restores iniated by SSIS

[rokerstrom]

I've created a PowerShell script to automate restoring databases from one server to another server. In order for our development teams to have a test server to work with. The veeam restores seem to work fine when run the script manually. However when using SQL's SSIS to call the script some databases restore and others randomly fail. Has anyone else had success or issues using SSIS packages with calling the veeam cmdlets?

[HannesK]

Hello,
honestly I have zero experience with SSIS, but do you maybe have some error messages?

Best regards,
Hannes

[dnumrich]

Hi there. I work with Ryan - I'm one of the SQL DBAs at our company. SSIS is a Visual Studio shell used to automate database tasks. We use it to refresh production databases to non-production servers, with some pre- and post- processing tasks to preserve non-prod user ids and to mask production data. We invoke Veeam from SSIS using a call to a powershell script via an Execute Process SSIS task with parameters. At runtime, we are basically executing the following, where VeeamSQL.ps1 is a powershell script with Veeam/SQL cmdlets that performs a restore:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File C:\Veeam_Refresh\Automated\VeeamSQL.ps1 -Source PRODSERVER -Database ProdDatabase -Target NonPRODSERVER -NewDatabase NonProdDatabase -DataFile D:Newpathtodatafile -LogFile Newpathtologfile

This works fine, except when it doesn't. And if it doesn't work, we can just run it again and it usually works if we keep trying.

We've seen quite a variety of messages from Veeam - I've copied several of them below. These all come from the same SSIS package, executed from the same server by the same user:

- ExplorerManagementService: Failed to StartMountSourcePreparationAsync: sessionId - 06d48819-39d9-4e2f-8248-ffdc103cb62e, oibId - 4a04b747-91e2-49bc-94a7-4023e3c41447
- The CancellationTokenSource has been disposed.
- The specified drive letter is incorrect.
- Failed to read block from file: C:\VeeamFLR\SERVER_6f385276\Volume4\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database.mdf The request could not be performed because of an I/O device error.
- Failed to read block from file: C:\VeeamFLR\Server_2dae580c\Volume3\Logs\database.ldf The system cannot find the file specified.
- Unable to attach database.


One thing we recently discovered was that we appear to have duplicate restore points. When we use the Veeam GUI to fetch the most recent restore point, it selects one of the following and succeeds. When we use Powershell, it selects the other and fails. The only visible difference between the two is the ID.

IsExchange : False
IsActiveDirectory : False
IsSharePoint : False
IsSQL : True
IsOracle : False
CreationTime : 5/6/2019 12:15:30 AM
Type : Increment
IsIndexed : False
IsCorrupted : False
Name : Server123
Id : 33022ea7-2de3-4c9d-8a98-42e6b5cb3230

IsExchange : False
IsActiveDirectory : False
IsSharePoint : False
IsSQL : True
IsOracle : False
CreationTime : 5/6/2019 12:15:30 AM
Type : Increment
IsIndexed : False
IsCorrupted : False
Name : Server123
Id : d0568d30-175e-4105-818e-b990a325dce8

We're basically at a loss to explain why this fails intermittently.

[veremin]

The problem seems to be related to the product database - there must be duplicated restore point entities. PS interacting directly with the db returns both restore points.

Kindly, reach our support team and let them confirm your database.

Thanks!