Regex: from a backup file list to a RESTORE script in under 2 minutes

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 script that looks like this one ...

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.

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, 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.
   

2. Paste the list of files into the text area.
   

3. Switch to List view
   

4. Write the regex expression ((.*)_backup.* in our case) and set the right flags
   

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"

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:
   

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:

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.

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.

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.
• 
• 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 in search/replace
• Regex Workbench 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 for full reference)
• https://regex101.com - similar to regexr.com