DRAG DROP
You have a SQL Server database server that contains a database named CustomerDB. CustomerDB is protected by using transparent data encryption (TDE) and a certificate named TDE_Cert.
The server fails.
You deploy a new server and restore all of the backups to a folder named C:backups.
You need to restore the database to the new server.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.
Select and Place:
Correct answer should be:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyPassword!’
CREATE CERTIFICATE TDE_Cert FROM FILE = ‘c:\backups\TDE_Cert.cer’ DECRYPTION BY PASSWORD = ‘MyPassword!’
RESTORE DATABASE CustomerDB FROM DISK = ‘c:\backups\CustomerDB.bak’
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server?view=sql-server-2014
— Detach the TDE protected database from the source server.
USE master ;
GO
EXEC master.dbo.sp_detach_db @dbname = N’CustRecords’;
GO
— Move or copy the database files from the source server to the same location on the destination server.
— Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.
— Create a database master key on the destination instance of SQL Server.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘*rt@40(FL&dasl1’;
GO
— Recreate the server certificate by using the original server certificate backup file.
— The password must be the same as the password that was used when the backup was created.
CREATE CERTIFICATE TestSQLServerCert
FROM FILE = ‘TestSQLServerCert’
WITH PRIVATE KEY
(
FILE = ‘SQLPrivateKeyFile’,
DECRYPTION BY PASSWORD = ‘*rt@40(FL&dasl1′
);
GO
— Attach the database that is being moved.
— The path of the database files must be the location where you have stored the database files.
CREATE DATABASE [CustRecords] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf’ ),
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF’ )
FOR ATTACH ;
GO