Database restore failing -

I am having issues with restore database. I am using the template from octopus library center, here is my issues
I am trying to restore a Dev database to Stage database, they databases have different names and located on same server

I changed the template to accept a file name, please find the script in attachments

Issue#1: the process kept failing saying “cannot find the file”, i made sure the printed path is correct and the file is exist. I changed the check of file existence to use .NET API, but still no luck. so I commented the check loop.

Issue#2: KillAllProcess was failing, as you see below.

The user i am connecting with has db_owner permissions. should it be systemadmin?

Tentacle script execution
Error 11:32:52
Exception calling “KillAllProcesses” with “1” argument(s): "Drop all active
Error 11:32:52
database connections failed for Server ‘myserverName’. "
Error 11:32:52
At C:\Windows\system32\config\systemprofile\AppData\Local\Tentacle\Temp\aeef247
Error 11:32:52
5-d418-4751-b9b6-7c5bcc3e1fa5.ps1:110 char:9
Error 11:32:52

  •     $server.KillAllProcesses($dbName)

Error 11:32:52

  •     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Error 11:32:52
+ CategoryInfo : NotSpecified: (:slight_smile: [], ParentContainsErrorRecordE
Error 11:32:52
Error 11:32:52
+ FullyQualifiedErrorId : FailedOperationException
Info 11:32:52
Connecting to server using SQL authentication as dbname_stg.
Attempting to restore database serverName.dbName_db from:
C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\Backup\dbName_1.0.4134.bak
Info 11:32:53

PowerShell exit code: 1

Fatal 11:32:53
PowerShell script returned a non-zero exit code: 1
Tentacle version (5 KB)

Issue#1: Commented and the script worked without it.
Issue#2: I added sql login user to dbcreator role

I ran the script directly on server, and it works fine.

I am going to run it through octopus and see! Will let you know.

still having the below issue when i run the script from octopus deploy

Cannot find backup device C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\Backup\client_db_project-Dev_1.0.4141.bak

the same script works if I execute from powershell command line window!

Can please someone help


Thanks for getting in touch! Sounds like you are not having much fun here. In these situations where a script works under your user but not via Octopus, we recommend using our scrip console to troubleshoot.

Octopus bootstraps and uses its Tentacle user to run PowerShell scripts. You can change the user that Tentacle runs under if required.

Hopefully this will help you troubleshoot the problem.

Let me know how you go!