使用Alter选项创建过程

Question

我有30多个dbs是用TDE加密的。现在，我必须在没有加密的情况下对每个db进行备份。需要执行以下步骤：- Set加密关闭-对数据库集加密执行完整备份。

(Sry，但我不太擅长编码)

下面是我迄今所做的一个例子：

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

对于30 dbs来说，有什么更简单的方法可以做到吗？我想过一个程序或者一个光标

Answer 1

将数据库名称存储在#tmp中

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

谢谢克劳迪奥·比塞利的帮助：

我调整了你的光标部分：

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

它的作用是：)