SQL Server – How to Check Disk Space Before Overwriting a DB with Restore Command

backuprestoresql server

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

Best Answer

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 < ...
Related Question