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 – Find the correct certificate select * from sys.certificates

– Assignation du bon certificat – Assign the correct certificate

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 Encryption will be therefore relaunched

– Vérification du statut d'encryption – Check of the status of the 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