How to find if SQL server backup is encrypted with TDE without restoring the backup Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Restoring a backup to an older version of SQL ServerCan I recover a TDE certificate by restoring the MASTER database?How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?Backup SQL Server with VMwareRestoring encrypted database on another server (using Backup Encryption)A SQL Server database backup/restore issueIs network traffic encrypted when writing remote backups using SQL Server TDE?Restore SQL Server DB encrypted by EKM - where's the asymmetric key?Always Encrypted after restoring an old database backup using C#Restoring MS SQL TDE database question
2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?
Can an alien society believe that their star system is the universe?
Apollo command module space walk?
How can I make names more distinctive without making them longer?
What would be the ideal power source for a cybernetic eye?
What's the meaning of 間時肆拾貳 at a car parking sign
51k Euros annually for a family of 4 in Berlin: Is it enough?
Should I discuss the type of campaign with my players?
How to find all the available tools in mac terminal?
How to find out what spells would be useless to a blind NPC spellcaster?
How widely used is the term Treppenwitz? Is it something that most Germans know?
Where is the concept of Prapatti/Saranagati mentioned in the mukhya upanishads, as per the Sri Vaishnava interpretation?
Fundamental Solution of the Pell Equation
Can we see the USA flag on the Moon from Earth?
How to react to hostile behavior from a senior developer?
How does the particle を relate to the verb 行く in the structure「A を + B に行く」?
Why do we bend a book to keep it straight?
Delete nth line from bottom
Why do people hide their license plates in the EU?
How do I stop a creek from eroding my steep embankment?
Can I cast Passwall to drop an enemy into a 20-foot pit?
How to overwrite .php file of lib directory?
Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?
What does an IRS interview request entail when called in to verify expenses for a sole proprietor small business?
How to find if SQL server backup is encrypted with TDE without restoring the backup
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Restoring a backup to an older version of SQL ServerCan I recover a TDE certificate by restoring the MASTER database?How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?Backup SQL Server with VMwareRestoring encrypted database on another server (using Backup Encryption)A SQL Server database backup/restore issueIs network traffic encrypted when writing remote backups using SQL Server TDE?Restore SQL Server DB encrypted by EKM - where's the asymmetric key?Always Encrypted after restoring an old database backup using C#Restoring MS SQL TDE database question
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?
Thanks
sql-server
asked Apr 1 at 17:20
yegnasewyegnasew
484
add a comment |
Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?
Thanks
sql-server
asked Apr 1 at 17:20
yegnasewyegnasew
484
add a comment |
Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?
Thanks
sql-server
asked Apr 1 at 17:20
yegnasewyegnasew
484
Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?
Thanks
sql-server
sql-server
asked Apr 1 at 17:20
yegnasewyegnasew
484
asked Apr 1 at 17:20
yegnasewyegnasew
484
asked Apr 1 at 17:20
yegnasewyegnasew
484
asked Apr 1 at 17:20
yegnasewyegnasew
484
asked Apr 1 at 17:20
yegnasewyegnasew
484
484
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Imagine for a second that you've got a 1 terabyte database. Backing it up takes a while, and encrypting it takes a while. So imagine that:
- 9:00 AM - you start taking a full backup
- 9:01 AM - in another window, you start enabling TDE on the database
- 9:05 AM - the backup completes
- 9:10 AM - TDE completes
What would you expect your query to return, given that as soon as you finish restoring the full backup, it's going to continue applying TDE, encrypting the rest of your database?
Conversely, imagine that you start with an already-encrypted database, and:
- 9:00 AM - you remove TDE (which takes some time)
- 9:01 AM - you start a full backup
- 9:05 AM - the data pages are no longer encrypted
- 9:06 AM - your full backup completes
What would you expect the query to return? These are example scenarios of why TDE encryption isn't one of the fields included in msdb.dbo.backupset.
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
add a comment |
I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.
However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."
I looked at some of my backups which were both TDE encrypted and not TDE encrypted.
The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233674%2fhow-to-find-if-sql-server-backup-is-encrypted-with-tde-without-restoring-the-bac%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Imagine for a second that you've got a 1 terabyte database. Backing it up takes a while, and encrypting it takes a while. So imagine that:
- 9:00 AM - you start taking a full backup
- 9:01 AM - in another window, you start enabling TDE on the database
- 9:05 AM - the backup completes
- 9:10 AM - TDE completes
What would you expect your query to return, given that as soon as you finish restoring the full backup, it's going to continue applying TDE, encrypting the rest of your database?
Conversely, imagine that you start with an already-encrypted database, and:
- 9:00 AM - you remove TDE (which takes some time)
- 9:01 AM - you start a full backup
- 9:05 AM - the data pages are no longer encrypted
- 9:06 AM - your full backup completes
What would you expect the query to return? These are example scenarios of why TDE encryption isn't one of the fields included in msdb.dbo.backupset.
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
add a comment |
Imagine for a second that you've got a 1 terabyte database. Backing it up takes a while, and encrypting it takes a while. So imagine that:
- 9:00 AM - you start taking a full backup
- 9:01 AM - in another window, you start enabling TDE on the database
- 9:05 AM - the backup completes
- 9:10 AM - TDE completes
What would you expect your query to return, given that as soon as you finish restoring the full backup, it's going to continue applying TDE, encrypting the rest of your database?
Conversely, imagine that you start with an already-encrypted database, and:
- 9:00 AM - you remove TDE (which takes some time)
- 9:01 AM - you start a full backup
- 9:05 AM - the data pages are no longer encrypted
- 9:06 AM - your full backup completes
What would you expect the query to return? These are example scenarios of why TDE encryption isn't one of the fields included in msdb.dbo.backupset.
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
add a comment |
Imagine for a second that you've got a 1 terabyte database. Backing it up takes a while, and encrypting it takes a while. So imagine that:
- 9:00 AM - you start taking a full backup
- 9:01 AM - in another window, you start enabling TDE on the database
- 9:05 AM - the backup completes
- 9:10 AM - TDE completes
What would you expect your query to return, given that as soon as you finish restoring the full backup, it's going to continue applying TDE, encrypting the rest of your database?
Conversely, imagine that you start with an already-encrypted database, and:
- 9:00 AM - you remove TDE (which takes some time)
- 9:01 AM - you start a full backup
- 9:05 AM - the data pages are no longer encrypted
- 9:06 AM - your full backup completes
What would you expect the query to return? These are example scenarios of why TDE encryption isn't one of the fields included in msdb.dbo.backupset.
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
Imagine for a second that you've got a 1 terabyte database. Backing it up takes a while, and encrypting it takes a while. So imagine that:
- 9:00 AM - you start taking a full backup
- 9:01 AM - in another window, you start enabling TDE on the database
- 9:05 AM - the backup completes
- 9:10 AM - TDE completes
What would you expect your query to return, given that as soon as you finish restoring the full backup, it's going to continue applying TDE, encrypting the rest of your database?
Conversely, imagine that you start with an already-encrypted database, and:
- 9:00 AM - you remove TDE (which takes some time)
- 9:01 AM - you start a full backup
- 9:05 AM - the data pages are no longer encrypted
- 9:06 AM - your full backup completes
What would you expect the query to return? These are example scenarios of why TDE encryption isn't one of the fields included in msdb.dbo.backupset.
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
answered Apr 1 at 17:27
Brent OzarBrent Ozar
35.8k19112243
35.8k19112243
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
add a comment |
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear.
– yegnasew
Apr 1 at 18:43
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it.
– Brian
Apr 8 at 21:50
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
@Brian bingo. It's not feasible given the current state.
– Brent Ozar
Apr 9 at 6:54
add a comment |
I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.
However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."
I looked at some of my backups which were both TDE encrypted and not TDE encrypted.
The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
add a comment |
I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.
However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."
I looked at some of my backups which were both TDE encrypted and not TDE encrypted.
The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
add a comment |
I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.
However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."
I looked at some of my backups which were both TDE encrypted and not TDE encrypted.
The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.
However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."
I looked at some of my backups which were both TDE encrypted and not TDE encrypted.
The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
answered Apr 1 at 17:57
Scott HodginScott Hodgin
18.4k21636
18.4k21636
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233674%2fhow-to-find-if-sql-server-backup-is-encrypted-with-tde-without-restoring-the-bac%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
