RESTORE HEADERONLY Error 3013 after sp1 was applied to SQL Server 2014

Question

I recently applied SP1 to SQL Server 2014, made and dusty, no problem. A few weeks later, trying to use one of my stored procedures to restore one of the databases from a bak file from the network, the following error message was sent:

RESTORE error HEADERONLY aborts abnormally. Error 3013.

Inside the stored procedure, I have the following two lines of code to get the database name from the bak file.

SET @strCheck = N'RESTORE HEADERONLY FROM DISK =''' +@backupFile +''''; INSERT INTO #headerOnly EXEC(@strCheck); 

After a long day with scratches, I realized that SQL Server 2014 sp1 added 3 new columns to the output of Restore HeaderOnly. The temporary table in the code was created in a previous version of SQL Server, SQL Server 2014 and did not have the last three columns, so the insert failed with error 3013.

The new columns in SQL Server 2014 sp1 are the following 3:

 KeyAlgorithm nvarchar(32); EncryptorThumbprint varbinary(20); EncryptorType nvarchar(32); 

Answer 1

We have a Windows C / S application where databases can be exported, imported and copied. To test SQL Server 2014, we imported some databases from SQL Server 2008 and several (successful) tests from 2014 ago.
Now we installed SP1, and then we also had problems with other error messages (for example, "Cannot open backup device"). I also lost the whole day until I found information that SP1 will add three new columns (unfortunately, before I found this message).

So, I had to add three new fields in the SP to the restoreheader file definition:

 CREATE TABLE #restoreheader( BackupName nvarchar(128) , BackupDescription nvarchar(255) , BackupType smallint , ExpirationDate datetime , Compressed tinyint , Position smallint , DeviceType tinyint , UserName nvarchar(128) , ServerName nvarchar(128) , DatabaseName nvarchar(128) , DatabaseVersion int , DatabaseCreationDate datetime , BackupSize numeric(20,0) , FirstLSN numeric(25,0) , LastLSN numeric(25,0) , CheckpointLSN numeric(25,0) , DatabaseBackupLSN numeric(25,0) , BackupStartDate datetime , BackupFinishDate datetime , SortOrder smallint , [CodePage] smallint , UnicodeLocaleId int , UnicodeComparisonStyle int , CompatibilityLevel tinyint , SoftwareVendorId int , SoftwareVersionMajor int , SoftwareVersionMinor int , SoftwareVersionBuild int , MachineName nvarchar(128) , Flags int , BindingID uniqueidentifier , RecoveryForkID uniqueidentifier , Collation nvarchar(128) , FamilyGUID uniqueidentifier , HasBulkLoggedData bit , IsSnapshot bit , IsReadOnly bit , IsSingleUser bit , HasBackupChecksums bit , IsDamaged bit , BeginsLogChain bit , HasIncompleteMetaData bit , IsForceOffline bit , IsCopyOnly bit , FirstRecoveryForkID uniqueidentifier , ForkPointLSN numeric(25,0) NULL , RecoveryModel nvarchar(60) , DifferentialBaseLSN numeric(25,0) NULL , DifferentialBaseGUID uniqueidentifier , BackupTypeDescription nvarchar(60) , BackupSetGUID uniqueidentifier NULL , CompressedBackupSize bigint NULL , containment tinyint not NULL , KeyAlgorithm nvarchar(32) , EncryptorThumbprint varbinary(20) , EncryptorType nvarchar(32) ) 

Notes:
See the last four fields in the definition.
In SQL-Server 2012 , a tinyint containment field was added , not NULL , so .. you also need to add this field (if it has not been added yet).
I just added new fields at the end of the definition .. and now everything works as before (no further changes).

So this is a breaking change on SQL-Server 2014 SP1 as soon as you use the headheader definition in your SPs. I did not find any information about this ( needs to be changed ) in the official MS notes. Only the information I found, see below ...

MS text for "SQL Server 2014 Service Pack 1 Release Notes":

1957464 RESTORE HEADERONLY for a file with an encrypted archive. The database does not show whether the backup is encrypted or not. After you apply SP1, the RESTORE HEADONLY output will include three additional columns: KeyAlgorithm, EncryptorThumbprint, and EncryptorType, which can provide additional information about encrypted backups.