Create a procedure with Alter Database option

Question

I have more than 30 dbs which are encrypted with TDE. Now I have to make a backup of each db without encryption. Following step are needed: - Set encryption off - Do a full backup of that db - Set encryption on

(Sry, but I am not so good at coding)

Here an example what I did so far:

use [Testt]
ALTER DATABASE [Testt] 
SET ENCRYPTION OFF
Go


BACKUP DATABASE [Testt] 
TO  DISK = N'J:\Backup\Testt_full.bak ' WITH NOFORMAT,COPY_ONLY, NOINIT,  
NAME = N'J:\Testt', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO 

GO
use [Testt]
ALTER DATABASE [Testt] 
SET ENCRYPTION ON
GO

Is there any easier way to do this for 30 dbs? I thought about a procedure or a cursor

Answer 1

Store in #tmp your database names

DECLARE @dbName nvarchar(MAX) =''
DECLARE @sql nvarchar(MAX) = ''

DECLARE Crs CURSOR LOCAL FOR 
SELECT data
FROM #tmp 


OPEN Crs 
FETCH NEXT FROM Crs into @dbName 

WHILE @@FETCH_STATUS = 0

BEGIN



SET @sql= 'use' + @dbName +
'ALTER DATABASE' + @dbName +
'SET ENCRYPTION OFF'

EXEC(@sql)

DECLARE @dName nvarchar(MAX) = 'J:\\' + @dbName 
DECLARE @dPath nvarchar(MAX) = 'J:\\Backup\\' + @dbName + '_full.bak'

SET @sql='BACKUP DATABASE ' + @dbName +
'TO  DISK = '+ @dPath +' WITH 
NOFORMAT,COPY_ONLY, NOINIT,  
NAME = '+ @dName  +', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

EXEC(@sql)

SET @sql= 'use  ' + @dbName +
'ALTER DATABASE  ' + @dbName +
'SET ENCRYPTION ON'

 EXEC(@sql)

FETCH NEXT FROM Crs into @dbName 


END


CLOSE Crs

DEALLOCATE Crs          

Answer 2

Thank you Claudio Biselli for your help:

I adjusted you cursor part:

DECLARE @dbName nvarchar(MAX) =''
DECLARE @sql nvarchar(MAX) = ''
DECLARE @sql2 nvarchar(MAX) = ''
DECLARE @sql3 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


SET @sql= 'use ' + @dbName +
' ALTER DATABASE ' + @dbName + ' SET ENCRYPTION OFF'
select @sql


SET @sql2='BACKUP DATABASE ' + @dbName +
' TO  DISK = ''J:\Backup\' + @dbName + '_full.bak'' WITH NOFORMAT, COPY_ONLY, NOINIT, NAME = ''J:\'+ @dbName + ''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
select @sql2


SET @sql3= 'use ' + @dbName +
' ALTER DATABASE ' + @dbName + ' SET ENCRYPTION ON'
select @sql3

FETCH NEXT FROM Crs into @dbName 
END
CLOSE Crs
DEALLOCATE Crs

And it works:)