mercredi 5 décembre 2018

how to build a if-statement/ loop in a cursor

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