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

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

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é – RESTORE, SQL SERVER knows that the backup is encrypted, hence he will use the certificate previoulsy imported

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

1. - renommage des noms logiques des datafile de la DB
2. - renaming of the logical name of the DB datafiles

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

ALTER DATABASE [DBWQ1Z81]
SET MULTI_USER
GO

3. Correction du certificat en relation avec son environnement / fixing the certificate within its environment

La base restorée, elle n'est plus encryptée avec le certificat de l'instance de destination, il faut donc le changer The DB being restore, it is not anymore encrypted with the destination instance certificate, so we need to change it

– 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