How to find if SQL server backup is encrypted with TDE without restoring the backupRestoring 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
How would I stat a creature to be immune to everything but the Magic Missile spell? (just for fun)
How badly should I try to prevent a user from XSSing themselves?
What is the most common color to indicate the input-field is disabled?
What do you call someone who asks many questions?
What's the in-universe reasoning behind sorcerers needing material components?
Watching something be piped to a file live with tail
Is there a hemisphere-neutral way of specifying a season?
Should I cover my bicycle overnight while bikepacking?
Why is this clock signal connected to a capacitor to gnd?
Why is consensus so controversial in Britain?
Dreadful Dastardly Diseases, or Always Atrocious Ailments
Would Slavery Reparations be considered Bills of Attainder and hence Illegal?
In 'Revenger,' what does 'cove' come from?
Why can't we play rap on piano?
Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?
Zip/Tar file compressed to larger size?
ssTTsSTtRrriinInnnnNNNIiinngg
What is a romance in Latin?
How can saying a song's name be a copyright violation?
Little known, relatively unlikely, but scientifically plausible, apocalyptic (or near apocalyptic) events
What are some good books on Machine Learning and AI like Krugman, Wells and Graddy's "Essentials of Economics"
Arrow those variables!
A category-like structure without composition?
Mathematica command that allows it to read my intentions
How to find if SQL server backup is encrypted with TDE without restoring the backup
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
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 2 days ago
yegnasewyegnasew
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 2 days ago
yegnasewyegnasew
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 2 days ago
yegnasewyegnasew
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 2 days ago
yegnasewyegnasew
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
yegnasewyegnasew
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
yegnasewyegnasew
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
yegnasewyegnasew
483
asked 2 days ago
yegnasewyegnasew
483
483
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
yegnasew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 2 days ago
Brent OzarBrent Ozar
35.8k19110241
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
2 days ago
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 2 days ago
Scott HodginScott Hodgin
18.2k21635
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
);
);
yegnasew is a new contributor. Be nice, and check out our Code of Conduct.
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 2 days ago
Brent OzarBrent Ozar
35.8k19110241
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
2 days ago
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 2 days ago
Brent OzarBrent Ozar
35.8k19110241
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
2 days ago
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 2 days ago
Brent OzarBrent Ozar
35.8k19110241
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 2 days ago
Brent OzarBrent Ozar
35.8k19110241
answered 2 days ago
Brent OzarBrent Ozar
35.8k19110241
answered 2 days ago
Brent OzarBrent Ozar
35.8k19110241
answered 2 days ago
Brent OzarBrent Ozar
35.8k19110241
35.8k19110241
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
2 days ago
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
2 days ago
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
2 days ago
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
2 days ago
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 2 days ago
Scott HodginScott Hodgin
18.2k21635
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 2 days ago
Scott HodginScott Hodgin
18.2k21635
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 2 days ago
Scott HodginScott Hodgin
18.2k21635
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 2 days ago
Scott HodginScott Hodgin
18.2k21635
answered 2 days ago
Scott HodginScott Hodgin
18.2k21635
answered 2 days ago
Scott HodginScott Hodgin
18.2k21635
answered 2 days ago
Scott HodginScott Hodgin
18.2k21635
18.2k21635
add a comment |
add a comment |
yegnasew is a new contributor. Be nice, and check out our Code of Conduct.
yegnasew is a new contributor. Be nice, and check out our Code of Conduct.
yegnasew is a new contributor. Be nice, and check out our Code of Conduct.
yegnasew is a new contributor. Be nice, and check out our Code of Conduct.
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
