Regex: from a backup file list to a RESTORE script in under 2 minutes
Tags: productivity, sql, regex
Published: 2020-02-11 00:16:29

> Max, our staging server is offline. Can you restore all our staging DBs from backup files to DEV box ASAP?

> Easy. Where is the restore script?
 
> On the staging server ...

So, what is the best way to generate a [RESTORE DB](https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql) script that looks like this one ...

```sql
RESTORE DATABASE C_H6ASW2GXATYN_base FROM  DISK = N'E:\base.bak\C_H6ASW2GXATYN_backup_2019_10_29_010001_8027311.bak' WITH  FILE = 1,  MOVE N'C_H6ASW2GXATYN' TO N'E:\sql_data\db\C_H6ASW2GXATYN_base.mdf',  MOVE N'C_H6ASW2GXATYN_log' TO N'E:\sql_data\log\C_H6ASW2GXATYN_base_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
```
... given a long list of file names that look like these ...
```
SESSION_LOCAL_backup_2019_10_31_020001_2385721.bak
STATS_backup_2019_11_05_010004_0668746.bak
TRANSLATION_backup_2019_11_15_010004_0614568.bak
4VIP_backup_2019_12_04_010004_5580242.bak
C_H6ASW2GXATYN_3_backup_2019_10_29_010001_8027311.bak
C_PDDWPLNP6E0J_backup_2019_12_04_010001_8548995.bak
C_0MVKWAGGXJJW_backup_2019_12_04_010001_9642766.bak
```
... when you have a team of 5 waiting?


### My options

**By hand**: copy-paste + search and replace is too error prone for 153 confusing DB names.

**A script**: there is a clear repetitive pattern in the naming, but writing a script and testing it [may take a while](https://xkcd.com/1319/).

**Regex**: extract the parts from the file names and put them in the SQL RESTORE statement for all DBs.

## Regex to the rescue

https://regexr.com/ is a very easy to use Regex playground website. It has different [Regex flavours](https://en.wikipedia.org/wiki/Comparison_of_regular-expression_engines#Part_1), docs and a handy cheatsheet in a sidebar. This is how I used it for this task:

1. Clear the expression at the top and the text area.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/s7d1txbychsqwlfk8ebg.png)

2. Paste the list of files into the text area.
![Initial screen](https://dev-to-uploads.s3.amazonaws.com/i/f77gq1e6elm5gh2nwrlj.png)

3. Switch to *List view*
![Text area](https://dev-to-uploads.s3.amazonaws.com/i/7j9107fql9uxbygks6zb.png)

4. Write the regex expression (`(.*)_backup.*` in our case) and set the right flags
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/3zpey2tlrjuvvipy4rae.png)

5. Check the results by looking up capture groups
   * `$&\n` in *List view* means "*output the entire match + `\n` to start a new line*"
   * `$1\n` means "*output the first capture group + `\n`*"
![Lists side by side](https://dev-to-uploads.s3.amazonaws.com/i/4pnmwx3zpfa6hfqmr4gf.png)

6. Switch to *Replace view* and paste the script template with variable parts as `$1` for DB name only and `$&` for the entire file name:
```sql
RESTORE DATABASE [$1_base] FROM DISK = N'E:\base.bak\$&' WITH FILE = 1, MOVE N'$1' TO N'E:\sql_data\db\$1_base.mdf', MOVE N'$1_log' TO N'E:\sql_data\log\$1_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5\nGO\n
```

#### The results pane at the bottom of the screen should now display the entire script for all DBs:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/6jzobcbexoalmjitht0z.png)

All in all, it took me a few minutes of grabbing the file list, generating a sample line of restore statement using *SQL Management Studio* and writing the regex. The actual regexr.com part took me under 2 minutes.

**Time spent learning Regex was one of the best investments I ever made.**

```sql
RESTORE DATABASE [REPORTING_ROx_base] FROM DISK = N'E:\base.bak\REPORTING_ROx_backup_2019_10_31_020001_2229467.bak' WITH FILE = 1, MOVE N'REPORTING_ROx' TO N'E:\sql_data\db\REPORTING_ROx_base.mdf', MOVE N'REPORTING_ROx_log' TO N'E:\sql_data\log\REPORTING_ROx_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [REPORTING_SPT_base] FROM DISK = N'E:\base.bak\REPORTING_SPT_backup_2019_10_31_020001_2229467.bak' WITH FILE = 1, MOVE N'REPORTING_SPT' TO N'E:\sql_data\db\REPORTING_SPT_base.mdf', MOVE N'REPORTING_SPT_log' TO N'E:\sql_data\log\REPORTING_SPT_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [SESSION_base] FROM DISK = N'E:\base.bak\SESSION_backup_2019_10_31_020001_2229467.bak' WITH FILE = 1, MOVE N'SESSION' TO N'E:\sql_data\db\SESSION_base.mdf', MOVE N'SESSION_log' TO N'E:\sql_data\log\SESSION_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [SESSION_LOCAL_base] FROM DISK = N'E:\base.bak\SESSION_LOCAL_backup_2019_10_31_020001_2385721.bak' WITH FILE = 1, MOVE N'SESSION_LOCAL' TO N'E:\sql_data\db\SESSION_LOCAL_base.mdf', MOVE N'SESSION_LOCAL_log' TO N'E:\sql_data\log\SESSION_LOCAL_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [STATS_base] FROM DISK = N'E:\base.bak\STATS_backup_2019_11_05_010004_0668746.bak' WITH FILE = 1, MOVE N'STATS' TO N'E:\sql_data\db\STATS_base.mdf', MOVE N'STATS_log' TO N'E:\sql_data\log\STATS_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [TRANSLATION_base] FROM DISK = N'E:\base.bak\TRANSLATION_backup_2019_11_15_010004_0614568.bak' WITH FILE = 1, MOVE N'TRANSLATION' TO N'E:\sql_data\db\TRANSLATION_base.mdf', MOVE N'TRANSLATION_log' TO N'E:\sql_data\log\TRANSLATION_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [4VIP_base] FROM DISK = N'E:\base.bak\4VIP_backup_2019_12_04_010004_5580242.bak' WITH FILE = 1, MOVE N'4VIP' TO N'E:\sql_data\db\4VIP_base.mdf', MOVE N'4VIP_log' TO N'E:\sql_data\log\4VIP_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [C_H6ASW2GXATYN_base] FROM DISK = N'E:\base.bak\C_H6ASW2GXATYN_backup_2019_10_29_010001_8027311.bak' WITH FILE = 1, MOVE N'C_H6ASW2GXATYN' TO N'E:\sql_data\db\C_H6ASW2GXATYN_base.mdf', MOVE N'C_H6ASW2GXATYN_log' TO N'E:\sql_data\log\C_H6ASW2GXATYN_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [C_PDDWPLNP6E0J_base] FROM DISK = N'E:\base.bak\C_PDDWPLNP6E0J_backup_2019_12_04_010001_8548995.bak' WITH FILE = 1, MOVE N'C_PDDWPLNP6E0J' TO N'E:\sql_data\db\C_PDDWPLNP6E0J_base.mdf', MOVE N'C_PDDWPLNP6E0J_log' TO N'E:\sql_data\log\C_PDDWPLNP6E0J_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE DATABASE [C_0MVKWAGGXJJW_base] FROM DISK = N'E:\base.bak\C_0MVKWAGGXJJW_backup_2019_12_04_010001_9642766.bak' WITH FILE = 1, MOVE N'C_0MVKWAGGXJJW' TO N'E:\sql_data\db\C_0MVKWAGGXJJW_base.mdf', MOVE N'C_0MVKWAGGXJJW_log' TO N'E:\sql_data\log\C_0MVKWAGGXJJW_base_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO

and 143 more ...
```

## `(.*)_backup.*` pattern explained

*Explain view* of regexr.com has a pretty good way of showing how your regex expression works or why it doesn't. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/o9eqy9ihou2q8klk9cii.png)

Our `(.*)_backup.*` pattern is quite simple. It can be translated into plain English as something like this:

* Start with `(.*)` to match any character except new line and store the match in *Capture Group #1* up until you encounter `_backup` and then match any character again with `.*` up to the end of the line.
* ![Regex explanation](https://dev-to-uploads.s3.amazonaws.com/i/38n3r7z40eb1o72f01ww.png)
* The very first part `4VIP` is called *Capture Group #1* because its pattern `.*` is enclosed in `(` and `)` so that we can reference it as `$1` in the results.


## Other Regex tools for Windows users

* VS Code, Visual Studio and SSMS support a [mish-mash of regex flavours](https://stackoverflow.com/questions/42179046/what-flavor-of-regex-does-visual-studio-code-use#42184299) in search/replace
* [Regex Workbench](https://marketplace.visualstudio.com/items?itemName=stephen-riley.regexworkbench) a useful addon to VS Code for testing and working with custom text outside the main code window
* Notepad++ supports Regex for search/replace (see [Boost C++ library](https://www.boost.org/doc/libs/1_72_0/libs/regex/doc/html/boost_regex/syntax.html) for full reference)
* https://regex101.com - similar to regexr.com