SQL SERVER – Msg 3254 – The Volume on Device Path\File is Empty. RESTORE DATABASE is Terminating Abnormally

During the recent consulting engagement Comprehensive Database Performance Health Check with SQL Server 2017/2019, my client’s DBA asked me if I can help with an error message which they are seeing while restoring a backup. This was a very interesting error to me and realize that not many people might have hit this error – Msg 3254 – The volume on device <Path\File> is empty. Followed by Msg 3013 – RESTORE DATABASE is terminating abnormally.

SQL SERVER - Msg 3254 - The Volume on Device Path\File is Empty. RESTORE DATABASE is Terminating Abnormally terminating-abnormally-800x385

The complete error message which they received was as follows:

Msg 3254, Level 16, State 1, Line 14
The volume on device ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\ProdDB.bak’ is empty.
Msg 3013, Level 16, State 1, Line 14
RESTORE DATABASE is terminating abnormally.

In my lab environment, I was able to get the same error while I provided a text file which was not a real database backup file.

Solarwinds

SQL SERVER - Msg 3254 - The Volume on Device Path\File is Empty. RESTORE DATABASE is Terminating Abnormally restore-empty-err-01

Command:

RESTORE DATABASE SQLAuthority
FROM DISK = 'C:\Temp\SQLAuthority.bak'

Output:

Msg 3254, Level 16, State 1, Line 12
The volume on device ‘C:\Temp\SQLAuthority.bak’ is empty.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.

WORKAROUND/SOLUTION – Terminating Abnormally

If restore is failing with this error, I would always check the physical size of the backup file first. I have tried various combinations and found that if the file size is less than 512 KB then I was getting the same error. Once file size was touched 512 KB then error message was this:

Msg 3241, Level 16, State 0, Line 15
The media family on device ‘C:\Temp\SQLAuthority.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 15
RESTORE DATABASE is terminating abnormally.

The below image shows the timestamp, size and error message change.

SQL SERVER - Msg 3254 - The Volume on Device Path\File is Empty. RESTORE DATABASE is Terminating Abnormally restore-empty-err-02

From my test, it was clear that the backup file was not a correct file as it can never be less than 512 KB in size. So, if you are getting “device is empty” error, check the size and use another backup of the database which is healthy.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
Work from Home Contest – Download Spotlight And Win USD 100
Next Post
SQL SERVER – Remote Monitoring While Working from Home

Related Posts

1 Comment. Leave new

  • Not the error message itself, but the fact that you found out that the limit is 512KB is pretty cool. :-)

    Reply

Leave a Reply

Menu