0

Restoring from a single transaction log backup, containing multiple backups. Currently using restore by specifying FILE in order.

RESTORE LOG db from DISK='c:\trans.bak' WITH NORECOVERY, FILE=1;
RESTORE LOG db from DISK='c:\trans.bak' WITH RECOVERY, FILE=2;

The above approach works. Is it possible to restore each sequence automatically with a single RESTORE and let SQL server process them in order? Or is there another way to handle restoring all of the backups contained in the single transaction file?

Not using different file names for log backups because there are many databases and 4 transaction backups through the day (yes I know about the risk of corrupt files, please no lectures). I want to minimize o/s file naming/creating/checking/processing etc. at both backup and restore time, and I'm looking for a way to manage and automate restores.

(Windows Server 2019, SQL Server 2016, Full recovery)

Improve this question

1 Answer 1

Reset to default
2

There isn't an out-of-the-box way that I know of. However, there is a fairly simple way with the open source dbatools powershell module.

Get-DbaBackupInformation -SqlInstance TargetDbServer -Path PathToBackups |
   Restore-DbaDatabase -SqlInstance TargetDbServer -NoRecovery;

Note - PathToBackups needs to be a path that's relative to the target db server. That is, you can't specify a path on your laptop have it work (the command is running restore headeronly on the files found to get the relevant information for all of the files it finds).

For your first time running it, I'd suggest specifying the OutputScriptOnly parameter to the Restore-DbaDatabase command so you can see what restore commands it's going to run. Also, I tend to specify the NoRecovery parameter out of habit (leaving me to run restore database TargetDb with recovery; by hand if that's the right thing to do or leave it in recovery for things like adding a replica to an availability group).

Even though you said "no lectures" regarding putting multiple backups into a single file, I will say that that is a non-standard configuration based on over 15 years of experience and it's likely to bite you at some point. Unless you're in an environment where you're charged a fee for each file you create, I'd suggest creating one file (or more! you can stripe large backups over multiple files) per backup. If it's a matter of automation, take a look at the Ola Hallengren backup solution. Also open source.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Never tried, but can you not do FILE = 1, FILE = 2
@Charlieface - If that question is directed at me, I was answering the OP's question of "Is it possible to restore each sequence automatically with a single RESTORE and let SQL server process them in order?". You can certainly manually do restore ... , file=1'; restore... , file=2; ... and the OP knows that. But that isn't a single RESTORE statement.
I didn't mean that, I meant a single statement RESTORE LOG db from DISK='c:\trans.bak' WITH NORECOVERY, FILE=1, FILE=2;
Ooh... interesting! I tried it on a local copy of AdventureWorks. It appears to be valid syntactically, but when I tried it, it complained about a missing LSN (as though I'd missed a log file). When I did the same logical restore chain but specifying each of file = 1, file = 2, etc as its own restore log statement, it worked just fine.
It looks like it's taking the last one specified.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.