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

I would first recommend to do the entire thing in a transaction. Second, I think it should probably be easiest to run it from a script platform like powershell. — Zohar Peled, Nov 29 '18 at 08:03
@MitchWheat I don't know about backups with I think that set encryption do... — Zohar Peled, Nov 29 '18 at 08:51

score 1 · Answer 1 · answered Nov 29 '18 at 08:57

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:)

claud.io · Accepted Answer · 2018-11-29T08:36:15.937

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

First of all, thank you for your help...when i am trying the cursor part, I get following error message: Msg 102, Level 15, State 1, Line 27 Incorrect syntax near '\'. Msg 132, Level 15, State 1, Line 28 The label 'J' has already been declared. Label names must be unique within a query batch or stored procedure. — Mr alex, Nov 29 '18 at 08:28

Create a procedure with Alter Database option

2 Answers2