---
title: "SQL Server TDE stuck encryption state 4"  
description: "SQL Server TDE stuck encryption state 4"  
author: "Anonymous User"  
published: 2015-09-11  
updated: 2015-09-11  
canonical: https://www.mindstick.com/forum/33457/sql-server-tde-stuck-encryption-state-4  
category: "mssql server"  
tags: ["database", "mssql server"]  
reading_time: 11 minutes  

---

# SQL Server TDE stuck encryption state 4

I'm trying to create a robust script that runs [backups](https://www.mindstick.com/forum/159789/what-are-the-different-types-of-database-backups), backs up current [certificate](https://www.mindstick.com/blog/122/viewing-client-certificate), creates a new certificate, backs up new certificate and regenerates [database](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax) [encryption keys](https://www.mindstick.com/forum/157779/how-do-you-generate-and-manage-encryption-keys-in-asp-dot-net) with the new certificate. Obviously to do all this you're talking about a pretty complicated script! i've tried to make it as robust as possible, however when running the script the [databases](https://www.mindstick.com/startup/34/airbyte-the-open-source-platform-simplifying-data-integration-for-warehouses-lakes-and-databases) have gotten stuck in encryption state 4. (this has happened before which is why i'm [testing](https://www.mindstick.com/articles/1849/role-of-testing-in-software-development) this to destruction.) now before i delete and recreate these databases is there any way to force them out of state 4? It will not allow you to turn encryption off you get the following error : Cannot [disable](https://www.mindstick.com/forum/12901/how-to-disable-enable-input-box-in-aspx-with-value-from-sql) database encryption while an encryption, decryption, or key change scan is in [progress](https://yourviews.mindstick.com/view/284/devotees-arrived-in-prayagraj-while-work-is-still-in-progress).\
I'm not sure what happened to get them into this state but want to prevent it at all costs.\
Please see my script. You should be able to test this easily by creating a couple db's.\
Any [improvements](https://www.mindstick.com/blog/306937/claude-4-explained-features-improvements-and-use-cases) would be greatly appreciated, and this will be extremely useful to anyone in a TDE environment.\

```
-- *** CREATE CERTIFICATE FROM CERTIFICATE BACKUPS ON LOCAL MACHINE IF REQUIRED ***--------------------------------------------------------------------------------------------------------------------------------------------------------USE master;--  CREATE CERTIFICATE PCI_Compliance_Certificate_201301032240 --      FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PCI_Compliance_Certificate_201301032240.cer' --      WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PCI_Compliance_Certificate_201301032240.pvk', --      DECRYPTION BY PASSWORD = '*** SERVER MASTER KEY ***');--  GO------------------------------------------------------------------------------------------------------------------------------------------------------ --This script will create new certificates, back them up, back up your old certificates and rotate  --the encryption using your new key. This is ideal in PCI DSS and other regulated environments  --where regular key rotation is neccessary. The script will determine which databases are encrypted --on your server instance and only re-generate the encryption for them. --Backup databases on server USE master DECLARE @Name NVARCHAR(50) , -- Database Name    @Path NVARCHAR(100) , -- Path for backup files    @FileName NVARCHAR(256) , -- Filename for backup    @FileDate NVARCHAR(20) , -- Used for file name    @BackupSetName NVARCHAR(50) ,    @SQLScript NVARCHAR(MAX) ,    @CurrentCertificateName AS NVARCHAR(100) ,    @CertificateBackupFile AS NVARCHAR(256) ,    @KeyBackup AS NVARCHAR(256) ,    @KeyStore AS NVARCHAR(256) = 'E:\SQL Data\Local Backups\' ,    @SecurePass AS NVARCHAR(MAX) = '*** PASSWORD ***' ,    @Live AS NCHAR(3) = 'No' -- *** MAKE SURE YOU CHECK THIS BEFORE RUNNING ***--   specify database backup directory SET @Path = 'E:\SQL Data\Local Backups\'--   specify filename format SET @FileDate = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120),                                         ':', ''), '-', ''), ' ', '') IF CURSOR_STATUS('global', 'db_cursor') >= -1     DEALLOCATE db_cursor DECLARE db_cursor CURSOR FOR    SELECT  Name    FROM    sys.databases    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )            AND is_encrypted = 1 OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @Name    WHILE @@FETCH_STATUS = 0     BEGIN TRY        SET @FileName = @Path + @Name + '_' + @FileDate + '.bak'          SET @SQLScript = 'BACKUP DATABASE ' + @Name + ' TO DISK = '''            + @FileName + ''' WITH NOFORMAT, INIT, SKIP, STATS = 10        RESTORE VERIFYONLY FROM  DISK = ''' + @FileName            + ''' IF (SELECT recovery_model_desc FROM sys.databases WHERE Name = '            + CHAR(39) + @Name + CHAR(39) + ') = ''FULL'' BACKUP LOG ' + @Name            + ' TO DISK = ''' + @Path + @Name + '_log.ldf'''        PRINT '*** STEP ONE Backing up Databases ***'        PRINT @SQLScript        IF @Live = 'Yes'             EXEC (@SQLScript)        FETCH NEXT FROM db_cursor INTO @Name    END TRY    BEGIN CATCH         PRINT 'Error Completing Backups'         SELECT  ERROR_NUMBER() AS ErrorNumber ,                ERROR_SEVERITY() AS ErrorSeverity ,                ERROR_STATE() AS ErrorState ,                ERROR_PROCEDURE() AS ErrorProcedure ,                ERROR_LINE() AS ErrorLine ,                ERROR_MESSAGE() AS ErrorMessage;        RETURN    END CATCH CLOSE db_cursor DEALLOCATE db_cursor   PRINT '*** STEP ONE Complete ***'-- Get current certificate statuses SELECT DB_NAME(database_id) AS DatabaseName ,        Name AS CertificateName ,        CASE encryption_state          WHEN 0 THEN 'No database encryption key present, no encryption'          WHEN 1 THEN 'Unencrypted'          WHEN 2 THEN 'Encryption in progress'          WHEN 3 THEN 'Encrypted'          WHEN 4 THEN 'Key change in progress'          WHEN 5 THEN 'Decryption in progress'        END AS encryption_state_desc ,        create_date ,        regenerate_date ,        modify_date ,        set_date ,        opened_date ,        key_algorithm ,        key_length ,        encryptor_thumbprint ,        percent_complete ,        certificate_id ,        principal_id ,        pvt_key_encryption_type ,        pvt_key_encryption_type_desc ,        issuer_name ,        cert_serial_number ,        subject ,        expiry_date ,        start_date ,        thumbprint ,        pvt_key_last_backup_date FROM   sys.dm_database_encryption_keys AS e        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint         -- TDE cannot be started while backup is running WHILE EXISTS ( SELECT  *                FROM    master.dbo.sysprocesses                WHERE   dbid IN ( DB_ID('*** DATABASE ***'))                        AND cmd LIKE 'BACKUP%' )      BEGIN        PRINT 'Waiting for backups to complete'        WAITFOR DELAY '00:01:00'    END --Code for backing up certificate and generating new certificate-- Get current certificate name SELECT @CurrentCertificateName = c.name FROM   sys.dm_database_encryption_keys AS e        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint WHERE  DB_NAME(e.database_id) = @Name-- backup the current certificate SET @CertificateBackupFile = @KeyStore + @CurrentCertificateName + '.cer' SET @KeyBackup = @KeyStore + @CurrentCertificateName + '.pvk' SET @SQLScript = 'BACKUP CERTIFICATE ' + @CurrentCertificateName    + +' TO FILE = ''' + @CertificateBackupFile + ''' WITH PRIVATE KEY'    + ' (FILE = ''' + @KeyBackup + ''',' + ' ENCRYPTION BY PASSWORD = '''    + @SecurePass + ''')' PRINT '*** STEP TWO Backing up current certificate: ' + @SQLScript + ' ***' IF @Live = 'Yes'     BEGIN TRY        EXEC ( @SQLScript )    END TRY    BEGIN CATCH        PRINT 'Could not back up existing Certificate. Job Cancelled'        SELECT  ERROR_NUMBER() AS ErrorNumber ,                ERROR_SEVERITY() AS ErrorSeverity ,                ERROR_STATE() AS ErrorState ,                ERROR_PROCEDURE() AS ErrorProcedure ,                ERROR_LINE() AS ErrorLine ,                ERROR_MESSAGE() AS ErrorMessage;        RETURN    END CATCH PRINT '*** STEP TWO Complete ***'-- Generate the new certificate. DECLARE @Now AS NVARCHAR(12) = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120),                                                        ':', ''), '-', ''),                                        ' ', '') DECLARE @NewCertificateName AS NVARCHAR(50) = 'PCI_Compliance_Certificate_'    + @Now -- Manually set certificate name --SELECT @NewCertificateName = 'PCI_Compliance_Certificate_201312231546'-- Generate a new certificate DECLARE @NewCertificateDescription AS NVARCHAR(100) = 'PCI DSS Compliance Certificate for 2014' SET @SQLScript = 'CREATE CERTIFICATE ' + @NewCertificateName    + ' WITH SUBJECT = ''' + @NewCertificateDescription + '''' PRINT '*** STEP THREE Creating New Certificate: ' + @SQLScript + ' ***' IF @Live = 'Yes'     BEGIN TRY        EXEC ( @SQLScript        )    END TRY     BEGIN CATCH        PRINT 'Could not create the new Certificate. Job Cancelled'        SELECT  ERROR_NUMBER() AS ErrorNumber ,                ERROR_SEVERITY() AS ErrorSeverity ,                ERROR_STATE() AS ErrorState ,                ERROR_PROCEDURE() AS ErrorProcedure ,                ERROR_LINE() AS ErrorLine ,                ERROR_MESSAGE() AS ErrorMessage;        RETURN          END CATCH PRINT '*** STEP THREE Complete ***'--  Back up the new certificate SET @CertificateBackupFile = @KeyStore + @NewCertificateName + '.cer' SET @KeyBackup = @KeyStore + @NewCertificateName + '.pvk' SET @SQLScript = 'BACKUP CERTIFICATE ' + @NewCertificateName    + +' TO FILE = ''' + @CertificateBackupFile + '''' + ' WITH PRIVATE KEY'    + ' (FILE = ''' + @KeyBackup + ''',' + ' ENCRYPTION BY PASSWORD = '''    + @SecurePass + ''')' PRINT '*** STEP FOUR Backing up New Certificate: ' + @SQLScript + ' ***' IF @Live = 'Yes'     BEGIN TRY        EXEC ( @SQLScript        )    END TRY    BEGIN CATCH          PRINT 'Error: Could not back up New Certificate.'        SELECT  ERROR_NUMBER() AS ErrorNumber ,                ERROR_SEVERITY() AS ErrorSeverity ,                ERROR_STATE() AS ErrorState ,                ERROR_PROCEDURE() AS ErrorProcedure ,                ERROR_LINE() AS ErrorLine ,                ERROR_MESSAGE() AS ErrorMessage;        RETURN    END CATCH PRINT '*** STEP FOUR Complete ***'--Encrypt database with new certificate WHILE EXISTS ( SELECT  *                FROM    master.dbo.sysprocesses                WHERE   dbid IN ( DB_ID('*** DATABASE ***'))                        AND cmd LIKE 'BACKUP%' )     BEGIN        PRINT 'Waiting for backups to complete'        WAITFOR DELAY '00:01:00'    END DECLARE db_cursor CURSOR FOR    SELECT  Name    FROM    sys.databases    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )            AND is_encrypted = 1 OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @Name    WHILE @@FETCH_STATUS = 0     BEGIN TRY        SET @SQLScript = 'USE ' + @Name            + ' ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE '            + 'PCI_Compliance_Certificate_' + @Now        PRINT '*** STEP FIVE Encrypting Databases ***'        PRINT @SQLScript        IF @Live = 'Yes'             EXEC (@SQLScript)        FETCH NEXT FROM db_cursor INTO @Name    END TRY    BEGIN CATCH         PRINT 'Error Encrypting Databases'         SELECT  ERROR_NUMBER() AS ErrorNumber ,                ERROR_SEVERITY() AS ErrorSeverity ,                ERROR_STATE() AS ErrorState ,                ERROR_PROCEDURE() AS ErrorProcedure ,                ERROR_LINE() AS ErrorLine ,                ERROR_MESSAGE() AS ErrorMessage;        RETURN    END CATCH CLOSE db_cursor DEALLOCATE db_cursor   PRINT '*** STEP FIVE Complete ***' -- Inspect the new state of the databases SELECT DB_NAME(e.database_id) AS DatabaseName ,        e.database_id ,        e.encryption_state ,        CASE e.encryption_state          WHEN 0 THEN 'No database encryption key present, no encryption'          WHEN 1 THEN 'Unencrypted'          WHEN 2 THEN 'Encryption in progress'          WHEN 3 THEN 'Encrypted'          WHEN 4 THEN 'Key change in progress'          WHEN 5 THEN 'Decryption in progress'        END AS encryption_state_desc ,        c.name ,        e.percent_complete FROM   sys.dm_database_encryption_keys AS e        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint
```

## Replies

### Reply by Anonymous User

Trace flag 5004 is useful for starting/stopping TDE. Might be worth giving it a go in case it helps.\

```
DBCC TRACEON(5004)GODBCC TRACEOFF(5004)GO
```


---

Original Source: https://www.mindstick.com/forum/33457/sql-server-tde-stuck-encryption-state-4

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.