April 19, 2024 at 12:00 am
I've had some backups of my encrypted databases failing with the error "BACKUP 'DBName' detected an error on page (10:12345) in file 'D:\path\DBName_File.ndf'
The databases were originally encrypted on a production server and restored to a DEV environment, which may be relevant. All the databases on the DEV server are encrypted, but only two were restored from another server after being encrypted, and only these 2 restored databases are giving errors during the backup. This is also not consistent as we have been able to do a manual backup of each database, but half an hour later when the automated backup kicked in we get the page error again. Several manual attempts after that also return the page error, and the page number is never the same page.
When we run a DBCC CHECKDB it doesn't return any errors, a DBCC PAGE ('TExxxxxxDEV', 10, 12345, 3) doesn't show any obvious issues like page numbers being wrong.
Also of concern is that these are the only really active databases on DEV, so there's the possibility that there's a problem with short term consistency in an encrypted database (that's been restored from another server).
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 20, 2024 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 24, 2024 at 9:28 pm
The databases are using TDE or column level encryption?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 28, 2024 at 6:21 am
Leo.Miller wrote:All the databases on the DEV server are encrypted, but only two were restored from another server after being encrypted, and only these 2 restored databases are giving errors during the backup. This is also not consistent as we have been able to do a manual backup of each database,
You can try updating the master key on DEV server by restoring the Master Key on DEV from PROD:
-- Create a new master key on the development server using CREATE MASTER KEY
-- Restore the certificate on DEV from the PROD backup using RESTORE CERTIFICATE.
-- Associate the certificate with the new master key using ALTER MASTER KEY on DEV.
Or
-- Open the master key on the DEV server using OPEN MASTER KEY.
-- Provide the password used during the backup process on PROD server.
-- Restore the databases on DEV.
Enable Encryption:
Moreover, enable encryption for the restored database using ALTER DATABASE.
Set the encryption state to ON: ALTER DATABASE <DatabaseName> SET ENCRYPTION ON
Finally, take the backup of database and verify that DBCC CHECKDB completes successfully.
=======================================================================
June 28, 2024 at 8:59 am
You can try updating the master key on DEV server by restoring the Master Key on DEV from PROD:
No! Definitely dont do this, besides the fact you shouldnt restore a prod entity to a dev server as part of security best practice, there are issues when restoring a DMK from another server, the DMK will not be silently protected by the SMK and a restore event would likely fail. See more in my article at this link
https://www.sqlservercentral.com/articles/smks-dmks-certificates-for-tde-and-encrypted-backups
-- Create a new master key on the development server using CREATE MASTER KEY -- Restore the certificate on DEV from the PROD backup using RESTORE CERTIFICATE. -- Associate the certificate with the new master key using ALTER MASTER KEY on DEV.
CREATE CERTIFICATE using the FROM FILE clause
There is no required action to associate a cert with a DMK
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply