Refresh d'une DB à partir d'un backup crypté (TDE)

Si on fait un restore sur le même environnent que le backup, cette étape n'est pas nécessaire Importer les certificats d'origine (PROD en général) .cert et . key sur le serveur de destination .

	CREATE CERTIFICATE "FR0-VSIAAS-7003_INWP1Z81_TDECertificate"
	FROM FILE = 'F:\Local\BackupSQL\INWQ1Z81\Backup\Certificates\TDE\PROD\TDECertificate_FR0-VSIAAS-7003_INWP1Z81.cert'
	WITH PRIVATE KEY (FILE = 'F:\Local\BackupSQL\INWQ1Z81\Backup\Certificates\TDE\PROD\EncryptPrivateKey_FR0-VSIAAS-7003_INWP1Z81.key',
	DECRYPTION BY PASSWORD = 'b_N566H__2Q3h_iv_RfH')

vérification du contenu du backup:

	RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'
	GO

localisation des datafile de la DB de destination

SELECT
	db.name AS DBName,
	type_desc AS FileType,
	Physical_Name AS Location
	FROM sys.master_files mf
	INNER JOIN  sys.databases db ON db.database_id = mf.database_id

RESTORATION, SQL SERVER sait que le backup est crypté, il va utiliser le certificat précédemment importé

	USE master;
	GO
	ALTER DATABASE [DBWQ1Z81]
	SET SINGLE_USER
	WITH ROLLBACK IMMEDIATE;
	GO

RESTORE DATABASE [DBWQ1Z81] FROM  DISK = N'F:\Local\BackupSQL\INWQ1Z81\Backup\DBWP1Z81_full_CHG000299572_WO0000002342881.Bak' WITH  FILE = 1,  
	MOVE N'DBWP1Z81' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81.mdf',  
	MOVE N'DBWP1Z81_2' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_2.ndf',  
	MOVE N'DBWP1Z81_3' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_3.ndf',  
	MOVE N'DBWP1Z81_4' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_4.ndf',  
	MOVE N'DBWP1Z81_5' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_5.ndf',  
	MOVE N'DBWP1Z81_6' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_6.ndf',  
	MOVE N'DBWP1Z81_7' TO N'E:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_7.ndf',  
	MOVE N'DBWP1Z81_log' TO N'G:\Local\SQLServer\Data\INWQ1Z81\DBWQ1Z81_log.ldf', 
	REPLACE,  STATS = 2
	GO

renommage des noms logiques des datafile de la DB

	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81', NEWNAME=N'DBWQ1Z81')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_2', NEWNAME=N'DBWQ1Z81_2')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_3', NEWNAME=N'DBWQ1Z81_3')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_4', NEWNAME=N'DBWQ1Z81_4')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_5', NEWNAME=N'DBWQ1Z81_5')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_6', NEWNAME=N'DBWQ1Z81_6')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_7', NEWNAME=N'DBWQ1Z81_7')
	ALTER DATABASE [DBWQ1Z81] MODIFY FILE (NAME=N'DBWP1Z81_log', NEWNAME=N'DBWQ1Z81_log')

On repasse la BDD en multi user donc ouverte au service

	ALTER DATABASE [DBWQ1Z81]
	SET MULTI_USER
	GO

La base étant restorée, elle n'est plus encryptée avec le certificat de l'instance de destination, il faut donc le changer

– Trouver le nom du certificat

select * from sys.certificates

Assignation du bon certificat

	use [xxx]
	GO
	ALTER DATABASE ENCRYPTION KEY
	ENCRYPTION BY SERVER CERTIFICATE [xxx_TDECertificate]
	GO
 
	Use master
	go
	ALTER DATABASE DBWQ1Z81 SET ENCRYPTION ON
	GO

L'encryption va donc être relancée

Vérification du statut d'encryption

-- 0 = No database encryption key present, no encryption 
-- 1 = Unencrypted 
-- 2 = Encryption in progress
-- 3 = Encrypted 
-- 4 = Key change in progress 
-- 5 = Decryption in progress
-- 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)
 
Use master
GO
SELECT DB_NAME(database_id) AS DatabaseName ,
dek.Encryption_State AS EncryptionState,
CASE Encryption_State
	WHEN 0 THEN 'No database encryption key present, no encryption'
	WHEN 1 THEN 'Unencrypted'
	WHEN 2 THEN 'Encryption in progress'
	WHEN 3 THEN 'Encrypted'
	WHEN 4 THEN 'Key change in progress'
	WHEN 5 THEN 'Decryption in progress'
	WHEN 6 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
	END AS DescriptionState ,
dek.key_algorithm AS Algorithm ,
dek.key_length AS KeyLength,
dek.percent_complete AS PercentComplete,
c.name AS Certificate,
c.expiry_date AS ExpiryDate
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
GO