Back in late 2016 I had just gotten back from an amazing 2.5 week vacation on Maui and Kauai before starting a new gig. I was in paradise for so long that I actually forgot how to DBA. I wound up having to google some pretty shameful things.....all of which were surprisingly SFW but NOT SFE (safe for ego).

Soon after starting the new position I had to do a simple restore, as DBAs tend to do, and forgot the share location for the backups. This normally isn't an issue if you only have 1 backup share, or have more than a few weeks under your belt at a job. However, We have a few shares/domains, and having to memorize each location just wasn't my top priority on day 3. After all, We had an existing DR architecture, so one off restores were not a concern.

I decided to be lazy and write something quick and dirty to get the metadata from the msdb backup tables. What had started small and simple, wound up becoming more complicated.

At first this was simply going to get me back into the swing of writing some good old fashioned T-SQL again, and keep me from having to look in my notes for each domain share. The thought behind this automation endeavor was that it would enable me to work harder initially, but save team members time further down the road.

The first creation of the script was written only to get the latest full backup. That was it!  Soon after though another restore request had to be fulfilled using DIFFs and LOGs, which pushed me to alter the script a little more, then a little more..... then a little more. What had started out with only a few lines of code, turned into over 900!

By utilizing the MSDB tables this script is obviously not very helpful if the instance is unavailable. However,  something like dbatools does gives you that flexibility of pointing it to a share and letting it do the dirty work for you. I highly recommend looking into it! I know I know dbatools is great at migrations, restores and ending world hunger, but sometimes team members just aren't comfortable enough with powershell.

I've also used this script to show team members, and in speaking sessions, just how much work goes into something like dbatools behind the scenes.

Although we do have DR, we sometimes have the need to restore the same DB to another instance, or the same instance, or reseed the old faithful Log Shipping.

There are currently 17 customizable variables to control several aspects of how the restore script is built.
recover, norecover, with move, different log locations, different backup locations, singleuser and stats.

Beyond the standard MIT license and declaration of customizable variables, are the SET commands for all the customizable variables.

*disclaimer, some examples are screenshots* (and by some I mean all)

With all of the top 3 parameters set to 0 and @singleDB = 0, this script will get the last full, diff and all logs for each database on the current instance. (based on date not LSN currently)

This script isn't small, but it's no sp_WhoIsActive or FirstResponderKit either.

The result of running this is a full T-SQL restore statement of all the target Instance/database(s), which you can then copy and paste to the target.

Enabling @OnlyLastFull will do just that. Only getting the last full backup will default to RECOVER unless @DRRecover is enabled.

Enabling @OnlyLastDiff will restore up to the last DIFF, if available. If a DIFF is not available, then the last FULL will be the default. If both are enabled, then @OnlyLastFull takes precedence.

There is a basic option to  move the data and log files, so using the @WithMove and @TargetDataPath and @TargetLogPath would accomplish this.

Alter the location of the full and diff backup locations using the @CustomBAKPath value once the @CustomBAKSource is enabled. 

I will be adding a variation to the current xml path tom foolery that extracts the filename, so that I can grab everything beyond the instance name OR DBName as an option. 

This would be very helpful when your share is being replicated to another location.

The same can be done for the TRN path as well. I decoupled the TRN from BAK/DIFF due to flexibility. Some transaction log backups may not be going to the same location in some environments.

If enabled, the debug option will output more information into the script (commented out of course)

Keep in mind that this script wasn't planned from the beginning. It was built on the fly and as such has some design issues that need to be worked out, along with a healthy dose of improvements.

This was also not written as a stored procedure, due to the nature of how this is run. Current team members only use this to re-seed our Log shipping instances or one off restores to other locations.

I will be updating the bak path to split using the servername from the backup path to capture the folder structure, so that changing the file locations won't rely on one folder for all. (yeah I know)

Please feel free to fork, add issues and submit PRs, or taunt me for using bitwise.

GitHub Repo: https://github.com/warren2600/DynamicBuildRestore