将 sp1 应用于 SQL Server 2014 后，RESTORE HEADERONLY 错误 3013 [英] RESTORE HEADERONLY Error 3013 after the sp1 was applied to SQL Server 2014

问题描述

我最近将 SP1 应用到 SQL Server 2014，完成并除尘，没问题.几周后，当我尝试使用我的存储过程之一从网络上的 bak 文件恢复其中一个数据库时，抛出了以下错误消息:

I have recently applied SP1 to SQL Server 2014, done and dusted, no issue. Few weeks later, when 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 thrown:

错误 RESTORE HEADERONLY 异常终止.错误 3013.

Error RESTORE HEADERONLY is terminating abnormally. Error 3013.

在存储过程中，我有以下两行代码从 bak 文件中获取数据库名称.

Within 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);

经过一整天的摸索，我意识到 SQL Server 2014 sp1 已将 3 个新列添加到 Restore HeaderOnly 的输出中.代码中的临时表是在以前版本的 SQL Server SQL Server 2014 上创建的，没有最后三列，因此插入失败，错误 3013.

After a long day spent scratching my head, I have realized that SQL Server 2014 sp1 has added 3 new columns to the output of Restore HeaderOnly. The temp table in the code was created on a previous version of SQL Server, SQL Server 2014 and didn't have the last three columns therefore, the insert failed with the error 3013.

SQL Server 2014 sp1 中的新列有以下 3 个:

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

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

推荐答案

我们有一个 Windows C/S 应用程序，可以在其中导出、导入和复制数据库.为了测试 SQL Server 2014，我们从 SQL Server 2008 导入了一些数据库，并在前一段时间对 2014 进行了一些(成功的)测试.
现在我们已经安装了 SP1，然后也遇到了其他错误消息的问题(例如无法打开备份设备").然后我也花了一整天的时间，直到我找到了 SP1 添加的三个新列的信息(不幸的是，在我找到这篇文章之前).

We have a Windows C/S application, where databases can be exported, imported and copied. To test SQL Server 2014, we had imported some dabases from a SQL Server 2008 and done some (successful) tests with 2014 some times ago.
Now we have installed SP1 and then also had problems with other error-messages (like e.g. "Cannot open backup device"). I then also have lost a full day until I found the information, that SP1 add's three new columns (unfortunately before I have found this posting).

所以..我不得不将SP中的三个新字段添加到restoreheader的定义中:

So.. I had to add the three new fields in the SP to the definition of the restoreheader:

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)
)

注意事项:
请参阅定义中的最后四个字段.
字段 containment tinyint not NULL 已在 SQL-Server 2012 中添加，因此.. 您还必须添加此字段(如果尚未添加).
我只是在定义的末尾添加了新字段......现在一切都像以前一样(没有进一步的变化).

Notes:
See the last four fields in the definition.
The Field containment tinyint not NULL was added in SQL-Server 2012 so.. you also have to add this field (if not already added).
I simply have added the new fields at the end of the definition.. and now everything works like before (no further changes).

所以..这是对 SQL-Server 2014 SP1 的重大更改，只要您在 SP 中使用恢复标头定义即可.我在 MS 官方发行说明中没有找到任何相关信息(必须更改).只有我找到的信息，见下文...

So.. this is a breaking change to SQL-Server 2014 SP1, as soon, as you use a restoreheader definition in your SP's. I don't have found any information to that (has to be changed) in the official MS release notes. Only information I found, see below...

MS 文本到SQL Server 2014 Service Pack 1 发布信息":

1957464 RESTORE HEADERONLY 用于加密备份文件数据库不显示备份是否加密.后您应用 SP1，RESTORE HEADONLY 的输出将包括三个附加列:KeyAlgorithm、EncryptorThumbprint 和EncryptorType 可以提供有关加密的其他详细信息备份.

1957464 RESTORE HEADERONLY for an Encrypted Backup file of the Database does not show whether the backup is encrypted or not. After you apply SP1, the output of RESTORE HEADONLY will include three additional columns: KeyAlgorithm, EncryptorThumbprint and EncryptorType that can give additional details about the encrypted backup.

这篇关于将 sp1 应用于 SQL Server 2014 后，RESTORE HEADERONLY 错误 3013的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！