Backup of encrypted databases failing

  • 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.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • The databases are using TDE or column level encryption?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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.

    =======================================================================

  • Emperor100 wrote:

    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

     

    Emperor100 wrote:

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