RESTORE VERIFYONLY: How does it check for available space on destination devices?

  • Article

 

“How does RESTORE VERIFYONLY checks for space on destination drives when a drive isn’t specified as part of the statement?” was the question being asked this time.

And this was my answer to that one:

This is what SQL does:

  1. In order to complete the space check, it iterates over all data files, transaction log files, FTS files, filestream data files, and filestream log files it encounters in the backup set, and accumulates the space each of these files requires from each volume in the volume list,
  2. then it iterates over the list of volumes and makes sure each of them have sufficient space available to accommodate what has been found as required in the previous step. If any of the volumes doesn’t meet that minimum, informational message 3257 is reported. (There is insufficient free space on disk volume '%ls' to create the database. The database requires %I64u additional free bytes, while only %I64u bytes are available.)

Now, some curious findings about how this space availability feature works internally beyond what’s explained above and which are not thoroughly  documented:

  • Trace flag 3105 (available on retail builds of every version ranging from 2005 up to 2014, both included) causes this space check functionality to be skipped.
  • For RAW volumes, that space check is skipped by default, whether or not the mentioned TF is enabled.
  • If the instance of SQL running the RESTORE VERIFYONLY statement doesn’t “see” the volume on which a given file is expected to be restored (either the one specified in the backupset or the overridden value via the WITH MOVE clause), it reports informational message 3181 (Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.)
  • If the name of the database whose backup set is being verified is found in the sys.databases table of the instance where the RESTORE VERIFYONLY command is being executed, SQL enumerates all the files associated to that database, and for each of them does this:
    • gets the size of the file,
    • gets the current volume on which the file is located,
    • if that volume matches any of the volumes on which the restore is being expected to happen (based on the physical paths in the backupset or the overridden paths indicated through the WITH MOVE clause), the size of the file is deducted from the space required in that volume as calculated in step 1 above.

The tricky thing here is that RESTORE VERIFYONLY doesn’t let you override the name of the database as it comes in the backup set, so when the name of the database in the backupset matches that of a database in the instance in which you’re executing the RESTORE VERIFYONLY command, the calculations made by the space check logic might not coincide with what you were expecting. Keep in mind that SQL treats that backup as one that is expected to be restored on top of (replacing) an existing database with the exact same name, and you may be planning to restore it side by side, under a different database name.