Appendix A: Back up and restore Microsoft SQL Server databases

a common task for database administrators is backing up and restoring databases but what if the backup is stored in a different location from the original database and needs to be restored there as well, especially when transparent data encryption (tde) is applied? this overview shows how to restore a database backup with tde enabled to a different server from where the original backup was taken overview when you perform a database backup using sql server management studio (ssms), for example, the backup files are typically stored on the same server where the database is running to perform a backup, you can right click on the database instance, select the tasks option, and then choose backup a window opens, prompting you to select the type of backup you can choose whether to save the backup to a disk or specify a url this example stores the backup file on a disk, with ssms automatically configuring the file path the backup file has a bak extension you can view information about the backup file by running the following sql query restore filelistonly from disk = 'c \program files\microsoft sql server\mssql16 mssqlserver\mssql\backup\exampledb bak'; in the output, one of the columns displays the backup file information if tde is enabled for the database instance, you also see the tde thumbprint in that column if you want to restore the backup file on a different database server or engine, you must configure both the original database server or engine and the second server or engine as endpoints on {{ch}} when following the original instructions to set up tde on your primary database, you only need to update the ekm credentials when setting up tde on the second server on your secondary database server or engine, send the following sql query create credential ekmcredential with identity = 'identity name for your secondary db server/ engine’ , secret = 'password for your secondary db server/engine' for cryptographic provider fxclekmprovider; go the key name of the rsa key generated for tde must be identical to the rsa key name used on the primary database where you took the backup use master; go create asymmetric key ekmasym ( > rsa keyname must overlap) from provider fxclekmprovider with algorithm = rsa 1024, provider key name = 'ekmasym'; ( > rsa keyname must overlap) go as per the instructions on {{ch}} , to set up tde on sql server, you must complete all the steps until step 6 in the enable transparent data encryption on the microsoft sql server docid 0rljrfoexybf 2u5yvtxu section when restoring the original database on the secondary database server or engine, you complete the remaining steps additional notes when backing up your database instance, ensure that your secondary database instance has access to the directory where the backup files are stored to verify this, perform the following steps to obtain the name of your secondary database engine or server open the services ( services msc ) locate your database engine or instance and double click it go to the log on tab to find the account name in the account section (for example, nt service\mssql$harisvminstance3 ) right click the folder where your backup files are stored and select properties , go to the security tab and edit the permissions add the sql server service account for the secondary database instance and grant it full access rights