SQL backup : encryption + NO_COMPRESSION

sakuraime 2,316 Reputation points
2021-10-07T08:24:42.117+00:00

Can I backup a database with encryption , while without COMPRESSION?

seems by default it helps me to compress , even I passed option NO_COMPRESSION.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. tibor_karaszi@hotmail.com 4,301 Reputation points
    2021-10-07T08:33:52.743+00:00

    How did you determine that it is compressed? Did you look at the output from RESTORE HEADERONLY, the Compressed column? If not, please do and let us know.

    0 comments
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-10-08T06:04:32.57+00:00

    Hi sakuraime,
    In addition, you can set the backup compression default server configuration to 0 to make sure the backup is not compressed. Please check this doc. 

    EXEC sys.sp_configure N'backup compression default', 0  
GO  
RECONFIGURE WITH OVERRIDE  
GO

    Best Regards,
    Amelia