Order of SQL Server backup restore

Posted on

Question :

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?

Answer :

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

Leave a Reply

Your email address will not be published.