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