ShruS
ShruS

Reputation: 71

Why is stored procedure starting multiple transactions at the same time? Or is is skipping steps to execute?

I have a sql job that executes a stored procedure that has multiple dynamic sql statements.
There are 3 steps:

  1. Turn off encryption
  2. Drop encryption
  3. backup database to destination server

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

Answers (1)

Programnik
Programnik

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

Related Questions