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

Could gravitational lensing be used to protect a spaceship from a laser?

Twin primes whose sum is a cube

In Romance of the Three Kingdoms why do people still use bamboo sticks when papers are already invented?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

Has there ever been an airliner design involving reducing generator load by installing solar panels?

AES: Why is it a good practice to use only the first 16bytes of a hash for encryption?

Combinations of multiple lists

How to model explosives?

Should I tell management that I intend to leave due to bad software development practices?

1960's book about a plague that kills all white people

Where does SFDX store details about scratch orgs?

90's TV series where a boy goes to another dimension through portal near power lines

Does a druid starting with a bow start with no arrows?

Does casting Light, or a similar spell, have any effect when the caster is swallowed by a monster?

Stopping power of mountain vs road bike

prove that the matrix A is diagonalizable

Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?

Can I use a neutral wire from another outlet to repair a broken neutral?

Is "remove commented out code" correct English?

How to take photos in burst mode, without vibration?

Etiquette around loan refinance - decision is going to cost first broker a lot of money

What killed these X2 caps?

Why does Kotter return in Welcome Back Kotter

Why can't we play rap on piano?



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






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








9















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










share|improve this question







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.


























    9















    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










    share|improve this question







    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.






















      9












      9








      9


      1






      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










      share|improve this question







      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






      share|improve this question







      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.











      share|improve this question







      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.









      share|improve this question




      share|improve this question






      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 Apr 1 at 17:20









      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.




















          2 Answers
          2






          active

          oldest

          votes


















          8














          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.






          share|improve this answer























          • 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


















          22














          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.






          share|improve this answer























            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.









            draft saved

            draft discarded


















            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









            8














            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.






            share|improve this answer























            • 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















            8














            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.






            share|improve this answer























            • 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













            8












            8








            8







            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.






            share|improve this answer













            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.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Apr 1 at 17:27









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
















            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













            22














            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.






            share|improve this answer



























              22














              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.






              share|improve this answer

























                22












                22








                22







                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.






                share|improve this answer













                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.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Apr 1 at 17:57









                Scott HodginScott Hodgin

                18.2k21635




                18.2k21635




















                    yegnasew is a new contributor. Be nice, and check out our Code of Conduct.









                    draft saved

                    draft discarded


















                    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.




                    draft saved


                    draft discarded














                    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





















































                    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







                    Popular posts from this blog

                    រឿង រ៉ូមេអូ និង ហ្ស៊ុយលីយេ សង្ខេបរឿង តួអង្គ បញ្ជីណែនាំ

                    Crop image to path created in TikZ? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Crop an inserted image?TikZ pictures does not appear in posterImage behind and beyond crop marks?Tikz picture as large as possible on A4 PageTransparency vs image compression dilemmaHow to crop background from image automatically?Image does not cropTikzexternal capturing crop marks when externalizing pgfplots?How to include image path that contains a dollar signCrop image with left size given

                    Romeo and Juliet ContentsCharactersSynopsisSourcesDate and textThemes and motifsCriticism and interpretationLegacyScene by sceneSee alsoNotes and referencesSourcesExternal linksNavigation menu"Consumer Price Index (estimate) 1800–"10.2307/28710160037-3222287101610.1093/res/II.5.31910.2307/45967845967810.2307/2869925286992510.1525/jams.1982.35.3.03a00050"Dada Masilo: South African dancer who breaks the rules"10.1093/res/os-XV.57.1610.2307/28680942868094"Sweet Sorrow: Mann-Korman's Romeo and Juliet Closes Sept. 5 at MN's Ordway"the original10.2307/45957745957710.1017/CCOL0521570476.009"Ram Leela box office collections hit massive Rs 100 crore, pulverises prediction"Archived"Broadway Revival of Romeo and Juliet, Starring Orlando Bloom and Condola Rashad, Will Close Dec. 8"Archived10.1075/jhp.7.1.04hon"Wherefore art thou, Romeo? To make us laugh at Navy Pier"the original10.1093/gmo/9781561592630.article.O006772"Ram-leela Review Roundup: Critics Hail Film as Best Adaptation of Romeo and Juliet"Archived10.2307/31946310047-77293194631"Romeo and Juliet get Twitter treatment""Juliet's Nurse by Lois Leveen""Romeo and Juliet: Orlando Bloom's Broadway Debut Released in Theaters for Valentine's Day"Archived"Romeo and Juliet Has No Balcony"10.1093/gmo/9781561592630.article.O00778110.2307/2867423286742310.1076/enst.82.2.115.959510.1080/00138380601042675"A plague o' both your houses: error in GCSE exam paper forces apology""Juliet of the Five O'Clock Shadow, and Other Wonders"10.2307/33912430027-4321339124310.2307/28487440038-7134284874410.2307/29123140149-661129123144728341M"Weekender Guide: Shakespeare on The Drive""balcony"UK public library membership"romeo"UK public library membership10.1017/CCOL9780521844291"Post-Zionist Critique on Israel and the Palestinians Part III: Popular Culture"10.2307/25379071533-86140377-919X2537907"Capulets and Montagues: UK exam board admit mixing names up in Romeo and Juliet paper"Istoria Novellamente Ritrovata di Due Nobili Amanti2027/mdp.390150822329610820-750X"GCSE exam error: Board accidentally rewrites Shakespeare"10.2307/29176390149-66112917639"Exam board apologises after error in English GCSE paper which confused characters in Shakespeare's Romeo and Juliet""From Mariotto and Ganozza to Romeo and Guilietta: Metamorphoses of a Renaissance Tale"10.2307/37323537323510.2307/2867455286745510.2307/28678912867891"10 Questions for Taylor Swift"10.2307/28680922868092"Haymarket Theatre""The Zeffirelli Way: Revealing Talk by Florentine Director""Michael Smuin: 1938-2007 / Prolific dance director had showy career"The Life and Art of Edwin BoothRomeo and JulietRomeo and JulietRomeo and JulietRomeo and JulietEasy Read Romeo and JulietRomeo and Julieteeecb12003684p(data)4099369-3n8211610759dbe00d-a9e2-41a3-b2c1-977dd692899302814385X313670221313670221