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


=== Prérequis ===

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 .
\\
=== 1. Importer le certificat dans l'instance de destination ===
<code TSQL>
	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')
</code>	

  * vérification du  contenu du backup:

 <code TSQL>
	RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'
	GO
</code>
\\  

=== 2. Restore ===
  * localisation des datafile de la DB de destination
<code TSQL>
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
</code>

  * RESTORATION, SQL SERVER sait que le backup est crypté, il va utiliser le certificat précédemment importé
<code TSQL>
	USE master;
	GO
	ALTER DATABASE [DBWQ1Z81]
	SET SINGLE_USER
	WITH ROLLBACK IMMEDIATE;
	GO
</code>

<code TSQL>
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
</code>

  * renommage des noms logiques des datafile de la DB
<code TSQL>
	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')
</code>
  * On repasse la BDD en multi user donc ouverte au service
<code TSQL>
	ALTER DATABASE [DBWQ1Z81]
	SET MULTI_USER
	GO
</code>

==== 	3. Correction du certificat en relation avec son environnement ====
 
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
 <code TSQL>
select * from sys.certificates
</code>


  * Assignation du bon certificat
<code TSQL>
	use [xxx]
	GO
	ALTER DATABASE ENCRYPTION KEY
	ENCRYPTION BY SERVER CERTIFICATE [xxx_TDECertificate]
	GO

	Use master
	go
	ALTER DATABASE DBWQ1Z81 SET ENCRYPTION ON
	GO
</code>
L'encryption va donc être relancée


  * Vérification du statut d'encryption
<code TSQL>
-- 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 
</code>