How to check there is enough disk space before overwriting a DB with restore command I need to automate the restore of a database each night from a backup but I want to first check if there is enough disk space. In the below Microsoft link it says the verifyonly command is able to do this "Checking for sufficient space on destination devices" but I can't find any examples online of how to use verifyonly with a destination to test. How can I do this?

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql?view=sql-server-2017
but I can't find any examples online of how to use verifyonly with a
  destination to test. How can I do this?


You can use this syntax:

restore verifyonly
from disk = 'V:\SQL_backup\myBak.bak'
with move 'myDb'     to 'C:\myDb.mdf', -- put here your destination
     move 'myDb_log' to 'C:\myDb_log.ldf' -- put here your destination


In case of insufficient space you'll get this error:


  Attempting to restore this backup may encounter storage space
  problems. Subsequent messages will provide details. There is
  insufficient free space on disk volume 'C:\' to create the database.
  The database requires 500254375936 additional free bytes, while only
  57038258176 bytes are available.


This is not an optimal way to check for disk space because this will not only check for a space available and you will wait the whole database backup check to finish.

You can use your own code like this, for example, here I use xp_fixeddrives to check free space:

declare @t table (drive varchar(10), Mb_free bigint);
declare @Mb_free bigint;

insert @t exec xp_fixeddrives;

select @Mb_free = Mb_free
from @t
where drive = 'C';

if @Mb_free &lt; ...