I have more than 30 dbs which are encrypted with TDE. Do to backup of the dbs you have to turn off the encryption and drop the encryption-key I created a cursor, which print the "ALTER DATABASE SET ENCRYPTION OFF" statement out. But I want to an execution, where I don't have to execute each one separate.
This statement gives you the current encryption status of the DBs.
3 = Encryption "on"
1 = Encryption "off"
SELECT e.encryption_state,d.name, e.percent_complete
FROM sys.databases d
INNER JOIN
sys.dm_database_encryption_keys e ON d.database_id = e.database_id;
the cursor:
DECLARE @dbName nvarchar(MAX) =''
DECLARE @sql nvarchar(MAX) = ''
DECLARE Crs CURSOR LOCAL FOR
SELECT d.name
FROM sys.databases d
INNER JOIN
sys.dm_database_encryption_keys e ON d.database_id = e.database_id
where d.name not like 'tempdb'
OPEN Crs
FETCH NEXT FROM Crs into @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql
SET @sql= 'use ' + @dbName +
'; ALTER DATABASE ' + @dbName + ' SET ENCRYPTION OFF;
use ' + @dbName + '; drop database encryption key; '
print @sql
FETCH NEXT FROM Crs into @dbName
END
CLOSE Crs
DEALLOCATE Crs
So now I need to build in a loop, which checks the active encryption-status. If 1, then drop the key otherwise wait till the status changed to 1. Any ideas??
I hope everything was understandable.
Aucun commentaire:
Enregistrer un commentaire