An general doubt about how restore in done in SQL server

While performing restore, will the MDF and NDF will be restored first or the LDF files?

  • 2
    Why does it matter? The restore is going to succeed or fail as a whole. – Aaron Bertrand Feb 21 '17 at 17:46
  • Seriously it doesn't matter, but an developer asked me this and I really don't know genuine answer to him. So this keeps bugging me. ha – Raja Feb 21 '17 at 18:26
  • I think, by just logic, the data files need to be restored first, so the logs may be implemented after. – Renato Afonso Feb 21 '17 at 18:36
  • 1
    Why would the developer even ask this? – Tara Kizer Feb 21 '17 at 20:00
  • Probably they are testing me :P . Developer team were analysing an restore job from vendor , so had some clarifications on storage and which files will be saved first. – Raja Feb 21 '17 at 20:23

I agree with the very wise folks in the comments, who noted that this doesn't matter.

And, in terms of what's actually going on in the files, I don't know. None of the files is usable until the restore process is done. So, again, doesn't matter.

Now, what might matter, is when the files are created, and when they reach full size. Especially if the devs were looking at storage during the process.

Just did a quick test to confirm that what I thought I knew, I actually knew. And, as I thought, my MDF and LDF files were both created when the restore started, and are created at full size (whatever size they were before they were backed up). [Technically, the files are created at size 0, then inflated (still empty) to the proper size, then filled; however, the time between creation and inflation will usually be infinitesimally smaller than the time between the inflation and the completion of the filling]

I don't have backups with an NDF file to confirm with, unfortunately. However, I would expect that to behave similarly (with the possible exception of restoring a pre-2008 backup to a 2008+ server, which allows for the full-text indexes to be rebuilt or reset, if appropriate).

| improve this answer | |
  • Just to be clear, those are considered "containers" and must exist before the data can be "streamed" into them. You have the right idea :) I would ask you to take it a step further, run the restore BUT hook up process monitor before you do that and filter on the folder you restore to. Then you should update the answer with your results :) – Sean Gallardy - Retired User Feb 22 '17 at 0:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.