Restore SQL database to another environment

To restore a SQL database with a runbook see restore SQL database.

This section shows you how to restore a database to a different environment, for instance restoring from production down to test. Using a runbook, you can create a self-service method for developers to restore the production database to a lower level environment to test bugs, fixes, and even the deployment process itself.

Using the runbook means developers don’t need any extra permissions to the database server itself, eliminating the time normal spent filling out a support ticket or tracking down a DBA to perform the restore.

Create the runbook

  1. To create a runbook, navigate to Project ➜ Operations ➜ Runbooks ➜ Add Runbook.
  2. Give the runbook a name and click SAVE.
  4. Add a new step template from the community library called SQL - Restore Database.
  5. Fill out all the parameters in the step. We recommend using variables rather than entering the values directly in the step parameters.
ServerName database serverSQLserver1
DatabaseName of the database to restoreMyDatabase
Backup DirectoryLocation of where the backup file resides\\mybackupserver\backupfolder
SQL loginName of the SQL Account to use (leave blank for Integrated Authentication)MySqlLogin
SQL passwordPassword for the SQL AccountMyPassword
Compression OptionUse compression for this backupEnabled
DevicesThe number of backup devices to use for the backup1
Backup file suffixSpecify a suffix to add to the backup file names. If left blank, the current date, in the format given by the DateFormat parameter, is usedProdRestore
SeparatorSeparator used between database name and suffix_
Date FormatDate format to use if backup is suffixed with a date stamp (e.g. yyyy-MM-dd)yyyy-MM-dd
  1. Add a new step template from the community library called SQL - Fix Orphaned User. This is needed because the SID associated with the login for the database will be different and needs to be re-associated.
  2. Fill out all the parameters in the step.
SQL ServerName of the serverSQLserver1
SQL LoginName of the SQL Account to use (leave blank for Integrated Authentication)MySqlLogin
SQL PasswordPassword for the SQL AccountMyPassword
Database NameName of the database for the accountMyDatabase
SQL LoginName of the account to be fixedMyOrphanedAccount

After adding all of the required parameters, click Save, and you have a runbook to restore your SQL database to another environment and fix the orphaned user accounts.

You can also add additional steps to add security to your runbooks, such as a manual intervention step for business approvals.


We have a Target - Windows Space on our Samples instance of Octopus. You can sign in as Guest to take a look at this example and more runbooks in the OctoFX project.

Learn more

Help us continuously improve

Please let us know if you have any feedback about this page.

Send feedback

Page updated on Wednesday, October 4, 2023