1

I want to restore my database using the backup files in the backup folder.

The SQL Server is running on Windows.

When I want to restore I do right-click on the databases and choose "Restore", then I pick "device", the SQL Server Management Studio opens a popup window to choose the files to restore.

The folder of the backup contain one bak file and a lot of trn files.

I need to select them all in order to restore to complete (I can't select just one bak or one trn file to restore).

After that I see the script that SQL generates and I can see that not all the .trn files are included in the script.

My question is how does SQL Server decide which trn file to use and which not?

Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64)   
Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19043: ) (Hypervisor) 
0

1 Answer 1

5

My question is how does SQL Server decide which trn file to use and which not?

That's not really your question, your question is "How does SSMS decide which files to use or not?". I'm not sure what version of SSMS you're using, but there have been multiple updates and fixes - so please use the latest version of SSMS... but also beware that it installs, IMO rather insidiously, Azure Data Studio along with it without any GUI way to opt-in or opt-out (which is pretty shameful). Mr. Erik C. Darling has a nice post about this:

Example of one fix that may interest you:

Fixed a long outstanding issue where restore plan would fail to find a restore plan, or would generate an inefficient restore plan under certain conditions.

Also, to answer your actual question, SSMS executes a restore filelist and headeronly against each file and attempts to make a restore plan based off various values in the backup information.

There may be other option, such as those given in the comments (copied in the answer, below) that may do a better job.

  1. Auto generate SQL Server restore script from backup files in a directory
  2. sp_DatabaseRestore

Your Answer

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