Home → Questions →

Restoring target database from nightly backup.

• Edit

Dale Schroeder

29 Nov, 2017 09:29 PM

I am looking to restore a database before runing a new deployment so we can have a clean database. I have a share folder that stores the nightly backup. I would like to have octopus grab the latest .bak file and restore it to the test environment. Currently it is asking for the name of the backup. Is there a way to use wilds to get it to pull the latest backup instead of a certian back?

1. Support Staff 1 Posted by Michael Compton on 30 Nov, 2017 01:42 AM

   

   Hi,

   Thanks for getting in touch.

   What process are using to do your restore - is it the SQL - Restore Database community step template? If it is, you can include variables in the parameters: e.g. in "Backup Directory" and "Backup file suffix". Would that work for your use case? For example: if the backups had a naming scheme that matched easily to what you could pick up in a variable.

   Another option is that you can change the powershell script that's run for that template, so if variables really can't get what you need, that would allow you to do whatever logic you need to find the right backup.

   Hope those suggestions can get you started. If you need more help, please get back to me and give me the details of your step setup (a screen shot is great, but mark the conversation as private if you add that) and the details of the backups.

   Michael

   • Edit

2. 2 Posted by Dale Schroeder on 30 Nov, 2017 01:23 PM

   

   Databasenameyyyyddmm is what we are using will that work?

   • Edit

3. Support Staff 3 Posted by Michael Compton on 01 Dec, 2017 05:54 AM

   

   Hi,

   I'm assuming that you are using the SQL - Restore Database community step template.

   I've been trying to get your example naming convention to work with the template. Unfortunately there are two blockers: 1. the community template always inserts a '-' between the database name and the suffix;, and 2. the date it guesses for the name includes the minutes and seconds. So out of the box, that's not going to work.

   I'm going to investigate updating that template to give it a few more options.

   In the meantime, here's a workaround for you: Go to "Library -> Step Templates -> SQL - Restore Database". Once you've selected the step temple, there is an option to save a copy, do that and pick a name for your copy. In your copy, expand the script content. Then replace line 64 with

   $deviceName = $dbName + $timestamp + ".bak"

   and line 139 with

   $timestamp = if(-not [string]::IsNullOrEmpty($Stamp)) { $Stamp } else { Get-Date -format yyyyMMdd }

   then save.

   If you then use that template instead of the standard one, it will pick today's backup to restore.

   Let me know if that didn't work for you.

   Michael

   • Edit

4. Support Staff 4 Posted by Michael Compton on 01 Dec, 2017 05:58 AM

   

   Sorry, just noticed that your date formate was `ddmm, so then the correct line would be

   $timestamp = if(-not [string]::IsNullOrEmpty($Stamp)) { $Stamp } else { Get-Date -format yyyyddMM }

   Michael

   • Edit

5. Support Staff 5 Posted by Michael Compton on 06 Dec, 2017 04:13 AM

   

   Hi,

   Just letting you know that we've updated the step template and the new version allows to pick the date format as a parameter, so you can achieve your restore with the standard script now.

   (you'll need to update the community step template)

   Michael

   • Edit

6. 6 Posted by Dale Schroeder on 06 Dec, 2017 01:55 PM

   

   Thank you.

   • Edit