locked
SQL Server 2008R2 Backup encryption RRS feed

  • Question

  • Hi,

    I'm on SQL server 2008R2 standard Edition and looking to upgrade to SQL server 2008R2 Enterprise edition, if do so can I create a maintenance plan for the backup with the encryption(TDE) and schedule the task.

    I know that SQL 2014 and SQL 2016 will have an option to choose the encryption on the backup wizard but I'm not sure about the SQL server 2008R2 Enterprise edition. 

    I just want to know before upgrading to Enterprise edition whether the backup encryption can be achievable with the SQL server 2008 R2 Enterprise edition or not.

    Thanks,


    venu

    Thursday, March 15, 2018 1:45 PM

All replies

  • Backup encryption is not available in 2008R2, regardless of edition.

    But if you encrypted the whole database, using TDE, then the backups will also be encrypted, since backup just writes the (already encrypted) pages to the backup media.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Shanky_621MVP Thursday, March 15, 2018 2:54 PM
    Thursday, March 15, 2018 2:00 PM
  • you can take backup with password

     BACKUP DATABASE [Leave_Management] TO

    '                  DISK = 'E:\Leave_Management_file1.bak'

                         WITH MEDIAPASSWORD='Securepassword'

    Same way you restore database with secure password.

    RESTORE DATABASE Leave_Management] FROM DISK= 'E:\Leave_Management_file1.bak'' WITH MEDIAPASSWORD=

    Securepassword'

    Refer following article

    https://www.mssqltips.com/sqlservertip/1108/using-passwords-with-sql-server-database-backup-files/


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Thursday, March 15, 2018 2:11 PM
  • @Tibor Karaszi, Of course I will go with the TDE only, If I do so can I create backup with a TDE encryption through the GUI. Here my aim is to create a backups with TDE encryption and schedule the task so I just want to know whether this is possible with SQL server 2008R2 enterprise edition or not.

    venu

    Thursday, March 15, 2018 2:50 PM
  • @Tibor Karaszi, Of course I will go with the TDE only, If I do so can I create backup with a TDE encryption through the GUI. Here my aim is to create a backups with TDE encryption and schedule the task so I just want to know whether this is possible with SQL server 2008R2 enterprise edition or not.

    venu

    Yes if your database is TDE encrypted each backup you would take would be encrypted no matter you take it via TSQL or SSMS or powershell. Hope this is what you are asking

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, March 15, 2018 2:55 PM
  • See Shanky's reply. Don't confuse the encryption setting that is in the backup dialog for newer version with the encryption that you automatically get with a TDE database. 

    The encryption setting in the backup dialog means that SQL Server will read the "clear-text" (assuming non-tde) pages in the database files and encrypt them before writing to the backup media. This is more light-weight than TDE if you are *only* interesting in extra protection for your backups (since you don't have encryption/decryption for your general I/O for the database).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, March 15, 2018 6:08 PM
  • Password is not the same as encryption. The backup data data isn't really protected (it is in "clear text"), and this option is considered unsecure - which is the reason that MS have removed it in later versions!

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, March 15, 2018 6:09 PM