FIX: Restore of TDE-compressed backup is unsuccessful when backing up database to a 512-byte Emulation disk in SQL Server

Applies to: SQL Server 2017 DeveloperSQL Server 2017 EnterpriseSQL Server 2017 Enterprise Core More

Symptoms


Consider the following scenario:

  • You enable the transparent data encryption (TDE)-compressed function in an instance of Microsoft SQL Server 2016 or 2017.
  • You back up databases to a different server that uses Universal Naming Convention (UNC) paths.
  • The backup on the disk uses the 512-byte Emulation advanced format.
  • You use a nondefault MAXTRANSFERSIZE in the backup command to enable backup compression.

When you restore the backup In this scenario, the operation may be unsuccessful, and you receive error messages for some large databases that resemble the following:

Resolution


This fix is included in the following updates for SQL Server:

Cumulative Update 13 for SQL Server 2017

Cumulative Update 5 for SQL Server 2016 SP2

Note: 

  • This update must be applied on the server in which the backup originates. 
  • This update does not resolve the error if the backup has been taken prior to applying the update. In this situation, a new backup must be taken.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More information


To verify the kind of drive that you have, follow these steps:

  1. Install update KB 982018.
  2. Run the following command from elevated command prompt:
     

    Fsutil fsinfo ntfsinfo <drive>

    Note <drive> represents the drive that you are checking.
  3. Use the values for Bytes per sector and Bytes per physical sector in the following tables to determine the kind of drive that you have.
     
    Bytes per sector value Bytes per physical sector value Drive type
    4096 4096 4K native
    512 4096 Advanced Format (also known as 512E)
    512 512 512-byte native

References


Learn about the standard terminology Microsoft uses to describe software updates.