Hello guys, I am a new Octopus user and I have a question about Octopus and SQL.
Is it possible to take a backup file from my production DB and reload into my Pre Production DB with Octopus automatically every day?
If it’s possible, I would like to check the existing file (before the reload) with the format yyyyMMdd-hhmm (meaning no reload till time xx; yyyyMMdd should be the day of operation). if such file exist with past date, delete it, if file exist for today, skip the process.
Thank you in advance.
Ney Santos
Hi @nsantos.br,
Thanks for getting in touch. I can see two potential ways of achieving this.
The first option would be to handle this externally from Octopus and focus on a solution using MS SQL tools. There are a few ways (example 1, example 2) to automate the backup and restore of an MS SQL database.
The main drawbacks of this would be security and speed. Octopus uses a master key to secure sensitive information; this key is unique per Octopus installation which means that to make this solution work you would need to configure your Pre Production Octopus instance to use the same master key.
This method would also be restoring the entire database, so if your instance is large, this could take some time.
The second option would be to make use of our migrator tool. This tool allows you to export and import the entire Octopus Server configuration without needing to touch the database. This method would likely be quicker as it will skip any items that already exist by default.
It is worth mentioning that both of these options will only move the configuration data; items such as task logs, artefacts, and packages wouldn’t move during this although task logs can be included in the migrator export.
I hope this helps point you in the right direction, please let me know if you have any further questions.
Best regards,
Paul
Hello Paul,
Thank you for your response. I created my project and when I try to deploy it, I get this error:
“Failed to connect to server xxxxx.xxx.xxx”. It seems that Octopus cannot connect to my SQL Data base. Is there anything that I must enable on the SQL Server?
Thank you.
Hi,
When you first create the Octopus Instance you choose the method of SQL authentication and database location/name. This is stored in the OctopusServer.config file located in C:\Octopus by default.
I’m unclear on what steps you have taken to result in this error though. Is this occurring after you have restored a copy of the database? Are you able to login successfully but then it only returns the database error when trying to deploy?
Regards,
Paul