Problem description
During the process of restoring an SQL database to an alternate SQL instance, the restore operation fails with the following error:
Error: VSS 1 (VSS Writer failure)
Cause
Transparent data encryption(TDE) is enabled on SQL database
Traceback
Phoenix.log
SyncError: VSS writer has reported failure : [state = 13, result_code = -2147212300], writer details are = [writer class id : {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}, writer name : SqlServerWriter] (#100040001) (Error Code : VSS1)Log Name: Application Source: SQLWRITER Date: 07-02-2024 Event ID: 24583 Task Category: None Level: Error Keywords: Classic User: N/A Computer: abc.local Description: The description for Event ID 24583 from source SQLWRITER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
ICommandText::Execute 0x80040e14 SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft OLE DB Driver for SQL Server Error message: RESTORE FILELIST is terminating abnormally. SQLSTATE: 42000, Native Error: 33111 Error state: 3, Severity: 16 Source: Microsoft OLE DB Driver for SQL Server Error message: Cannot find server certificate with thumbprint '0x65568B52C4B817B088E7ECEAD70CDCED87FC4E01'.
Verification
First, confirm that the database is indeed encrypted with TDE by running the following query on the source SQL server.
SQL
select name,is_encrypted from sys.databases;
If the value in the
is_encryptedcolumn for your database is1, it confirms that TDE is enabled and is the likely cause of the VSS 1 error.
From the output, if the value of “is_encrypted” is “1” means the database is encrypted. The databases, tempdb and TDE_DB are encrypted.
Resolution
Performing a File-Based Restore (Workaround)
As the article suggests, one workaround is to perform the restore as a database file. This bypasses the typical VSS snapshot restore process.
Instead of restoring a full database backup, restore the individual data (
.mdf) and log (.ldf) files from the backup(File server backup as flat files).
Refer to Microsoft Documentation on TDE:
For more information on Transparent Data Encryption (TDE) and how to handle encryption during database restore operations, refer to the official Microsoft documentation: Transparent Data Encryption (TDE) in SQL Server