Database restore failing -

Legacy Uncategorised
1

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
xception
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 2.6.5.1010

restore.ps (5 KB)

2

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.

3

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

4

Hi,

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. http://docs.octopus.com/display/OD/Script+Console

Octopus bootstraps and uses its Tentacle user to run PowerShell scripts. You can change the user that Tentacle runs under if required. http://docs.octopus.com/display/OD/Run+Tentacle+under+a+specific+user+for+use+in+PowerShell

Hopefully this will help you troubleshoot the problem.

Let me know how you go!

Thanks,
Daniel