0

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

2

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 < ...
  • 1
    @Igor why should one use RESTORE DATABASE command instead of RESTORE VERIFYONLY? If you follow your advice you'll get an error about unknown option recovery, two commands are different, do different things, use different options. BOL has 2 different articles for these 2 different commands, so it is NOT one command where you should replace something – sepupic Aug 1 at 10:47
  • you true. I delete my comment – Igor Aug 1 at 10:58

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.