You may encounter the following error when querying a compressed or encrypted database:

Msg 823, Level 24, State 2, Line 2
The operating system returned error 31 to SQL Server during a read at offset <offset> in file <database file path>.

This error occurs when SQL Server attempts to access a compressed or encrypted database file (*.mdfx, *.ndfx, *.ldfx, *.mdfe,*.ndfe or *.ldfe) and the HyperBac Control Service is stopped, so the file cannot be decompressed or decrypted.

Resolving this error

Make sure the HyperBac Control Service is running before using a database. You can check the status of the HyperBac Control Service and restart it if necessary in two ways:

• From the SQL Storage Compress user interface – if the service is stopped, a warning appears at the bottom of the window. Click the hyperlink to restart the service.

  

• From the system tray – if the service is stopped, the HyperBac Configuration Manager icon is modified:  Right-click the icon and select Start HyperBac Control Service to restart the service.

If any databases are suspect or recovering after the service has restarted, take each database offline and then bring it back online. To do this:

• Right-click each database in SQL Server Management Studio (SSMS), and select Tasks > Take Offline. Then, right-click and select Tasks > Bring Online.

or,

• Make sure you are connected to the master database, then run the following T-SQL statement for each database:

  ALTER DATABASE <database name> SET OFFLINE

  followed by:

  ALTER DATABASE <database name> SET ONLINE