Reputation: 71
I have a sql job that executes a stored procedure that has multiple dynamic sql statements.
There are 3 steps:
When I run this job, this script executes but the job always fails.
I get no error if I run each statement manually.
After some try, I was able to figure out that
Step 3 runs before the Step 2 is finished. This creates a copy of database on the destination server before the decryption key is dropped.
How to force SQL to finish the decryption process before the next step is initiated?
Why is the second transactions initiated on the same database while first is in process. my understanding was the database is locked until one transaction is completed. Am I missing something?
STEP 1: Turn off encryption
set @statement1 = 'ALTER DATABASE '+@db+'
SET ENCRYPTION OFF;
WHILE NOT EXISTS
(
SELECT encryption_state
FROM sys.dm_database_encryption_keys
where encryption_state = ''1''
and database_id = (
select database_id
from sys.databases
where name ='''+@db+'''
)
)
BEGIN
WAITFOR DELAY ''00:00:30''
END'
exec (@statement1);
STEP 2: Drop Encryption
set @statement2 = 'IF EXISTS
(SELECT encryption_state
FROM sys.dm_database_encryption_keys
where encryption_state = ''1''
and database_id=
(select database_id
from sys.databases
where name ='''+@db+''')
)
use '+@db+'
EXEC sp_changedbowner ''sa'';
DROP DATABASE ENCRYPTION KEY;
while exists
(SELECT * FROM sys.dm_database_encryption_keys
where database_id=
(select database_id
from sys.databases where name
='''+@db+'''
)
)
BEGIN
WAITFOR DELAY ''00:00:30''
END
ELSE
print ''Decryption Error'''
exec (@statement2);
Step 3: Copy decrypted database to different server
set @statement3 = 'use master
ALTER DATABASE '+@db+' SET RECOVERY simple;
BACKUP DATABASE '+@db+'
TO DISK = ''\Shared_drive_Across_Servers\database.bak''
WITH INIT, DESCRIPTION = N''DECRYPTED'', COMPRESSION
WAITFOR DELAY ''00:00:50'';'
exec (@statement5);
Upvotes: 0
Views: 60
Reputation: 1565
Missing a begin and end in middle block, and typo in last exec:
--STEP 1: Turn off encryption
set @statement1 = 'ALTER DATABASE '+@db+'
SET ENCRYPTION OFF;
WHILE NOT EXISTS
(
SELECT encryption_state
FROM sys.dm_database_encryption_keys
where encryption_state = ''1''
and database_id = (
select database_id
from sys.databases
where name ='''+@db+'''
)
)
BEGIN
WAITFOR DELAY ''00:00:30''
END'
exec (@statement1);
--STEP 2: Drop Encryption
set @statement2 = 'IF EXISTS
(SELECT encryption_state
FROM sys.dm_database_encryption_keys
where encryption_state = ''1''
and database_id=
(
select database_id
from sys.databases
where name ='''+@db+'''
)
)
BEGIN
use '+@db+'
EXEC sp_changedbowner ''sa'';
DROP DATABASE ENCRYPTION KEY;
while exists
(SELECT * FROM sys.dm_database_encryption_keys
where database_id=
(select database_id
from sys.databases where name
='''+@db+'''
)
)
BEGIN
WAITFOR DELAY ''00:00:30''
END
END
ELSE
print ''Decryption Error'' '
exec (@statement2);
--Step 3: Copy decrypted database to different server
set @statement3 = 'use master
ALTER DATABASE '+@db+' SET RECOVERY simple;
BACKUP DATABASE '+@db+'
TO DISK = ''\Shared_drive_Across_Servers\database.bak''
WITH INIT, DESCRIPTION = N''DECRYPTED'', COMPRESSION
WAITFOR DELAY ''00:00:50'';'
exec (@statement3);
Upvotes: 1