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

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