Strategy of restoring database with multiple logging files (24 total) at once

All we need is an easy explanation of the problem, so here it is.

I am trying to see whether it is safe and efficient approach if I run a multiple line of SQL command to restore multiple (total 24) logging files at once.

These files are formatted in bak file format, but they are all hourly transactional log files.

I have these SQL statements to run all together:

RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051800.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051801.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051802.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051803.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051804.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051805.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051806.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051807.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051808.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051809.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051810.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051811.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051812.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051813.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051814.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051815.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051816.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051817.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051818.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051819.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051820.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051821.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051822.bak' WITH NORECOVERY
RESTORE LOG [LOG_us_bcan_multi_replica] FROM DISK = 'F:\LOG_us_bcan_multi_replica_2021051823.bak' WITH NORECOVERY

Bottom is screenshot of each files and its size:

Strategy of restoring database with multiple logging files (24 total) at once

Because size of each file could be pretty big at one point (for example: ".._2021051804.bak" file is much bigger size than others, I am not sure whether running the whole SQL statement at once is a good approach.

Or should I break down these whole process into separate SQL Server Agent job steps?

I am not sure how SQL behaves when these whole SQL statement gets executed at once.

Does it run one by one from the top (stops until one is done then goes to the next line) and would not skip any process along the line (if one process takes longer than the other)?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your approach is correct.

SQL Server runs each statement within the batch to completion before starting the next one.

The last restore in the sequence should specify STANDBY if you want the resulting database to be readable, with the option to apply further logs in future.

.bak is not a file format, it is an ordinary Windows file extension. SQL Server does not care about that. Different people use different conventions. A popular one is .bak for a full backup and .trn for transaction logs, but it is just a convention.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply