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