SQL 2008 compressed backup

hi all,

know SQL 2008 enterprise introduce compressed backup, any encryption for backup?

can we have compression and encryption for SQL 2008 backup by sql 2008 itself natively ?

what version above SQL 2008 can do both if 2008 can't ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
I have used SQL 2008 compressed back with Standard.
It may have been R2, though, so let me double check real quick.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Sorry.  I have SQL 2008 R2, which is why.
According to Microsoft, SQL 2008 Enterprise only supports backup compression.
SQL 2008 R2 extended support down to Standard.

Ref: https://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx
0
marrowyungSenior Technical architecture (Data)Author Commented:
"SQL 2008 R2 extended support down to Standard."

you mean SQL 2008 R2 standard can do compress backup too ?

but not compress and encrypt at the same time, right?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Kevin CrossChief Technology OfficerCommented:
Yes that is what I meant - they now support compressed backups on Standard edition as well as Enterprise.  It should work but you likely will not get the same compression (see documentation's compression ratio).
0
marrowyungSenior Technical architecture (Data)Author Commented:
it seems since SQL 2014, that one is acceptable, do you have SQL 2014 ?
0
Kevin CrossChief Technology OfficerCommented:
Reminder: you have to set backup to new media set before the encryption option enables.  Therefore, if you are asking because the option is grayed out, try changing the backup media options then go back to set compression and encryption ones.
0
Kevin CrossChief Technology OfficerCommented:
I do have SQL 2014 developer on my machine, which is why I had to check because my options are that of Enterprise edition.  However, I manage production instances on SQL 2008, 2008 R2 and 2012 standard editions as well.  On 2008 R2 standard, you can compress and encrypt but yes you are correct that the backup encryption uses transparent data encryption --- a feature of SQL 2008 enterprise.
0
marrowyungSenior Technical architecture (Data)Author Commented:
" Therefore, if you are asking because the option is grayed out, try changing the backup media options then go back to set compression and encryption ones. "

no. just some rearch if we need to purchase redgate/litespeed backup solution for SQL server 2008. but we are going to upgrade it to SQL server 2014 with SP1 in the first quanter next year.

but in SQL 2014, is that means that we have to add a backup media/backup device first for the encryption option to be available ?

"On 2008 R2 standard, you can compress and encrypt but yes you are correct that the backup encryption uses transparent data encryption "

you mean since SQL server 2008 R2 we can compress and encrypt DB backup already ?I heard that SQL server 2012 one is rubblish, it make the backup even bigger !
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I heard that SQL server 2012 one is rubblish, it make the backup even bigger !
I already told you before, you should stop to hear things. Who's telling so much rubbish?

When you have doubts just go and check MSDN. Here are the Features Supported by MSSQL Edition where you can see that Data Encryption is available for any SQL Server 2008R2 Edition but Transparent database encryption is only available for Datacenter and Enterprise Editions.

You can also see that Backup compression for Standard edition of SQL Server is supported only in SQL Server 2008 R2 or higher versions.

So, you can backup a encrypted database but there's no such encrypt a backup from SQL Server. You'll need to do that with a 3rd tool.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I already told you before, you should stop to hear things. Who's telling so much rubbish?"

sorry, I always say google thing can find a lot of rubblsih.

"So, you can backup a encrypted database but there's no such encrypt a backup from SQL Server. You'll need to do that with a 3rd tool. "

but SQL 2014 has this, right?

SQL 2012 gives a lot of trouble I keep hearing that.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"
 So, you can backup a encrypted database but there's no such encrypt a backup from SQL Server. You'll need to do that with a 3rd tool"

sorry I am justifying it before I buy Redgate/litespeed but we are going to upgrade SQL server 2008 SP3/sP4 to SQL server 2014 with SP1
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
sorry I am justifying it before I buy Redgate/litespeed
Why using 3rd party software when the real one can do it? Or your company have a lot of money to spend? :)


SQL 2012 gives a lot of trouble I keep hearing that.
Hearing or reading? If reading, do you have any link to share with us?


but SQL 2014 has this, right?
I admit that didn't know but went to check and it really has. So, I never used but like always, you can just check MSDN for an article about Backup Encryption.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Why using 3rd party software when the real one can do it? Or your company have a lot of money to spend? :)
"

that's why I keep asking, if SQL server 2014 can do the compressed and encrypted backup on the SAME backup file I just wait for it.

I think you misunderstand me

"Hearing or reading? If reading, do you have any link to share with us?"

both! I forget.

but this one now is the only one I can searched out:

http://jasonbrimhall.info/2015/08/11/compressing-encrypted-backups/

that say MS fix the shit in SQL server 2014 and 2014 is good to go or the compressed size of the backup file is even bigger than non compressed
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
They are talking about using compressed backup on an encrypted database (database is already encrypted and you want to perform a compress backup on it).
You're talking about encrypting a compressed backup. That's different (you compress first and then encrypt after).

But thanks for the article. I usually don't have encrypted databases so was not aware of that issue.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"They are talking about using compressed backup on an encrypted database (database is already encrypted and you want to perform a compress backup on it).
 You're talking about encrypting a compressed backup. That's different (you compress first and then encrypt after)."

basically is what I want, compress and encrypt it during the backup time, I use litespeed a lot so I have this concern as my new company is going to convert all backup job to Ola solutlon, and I am not sure Ola can make use of the native compressed and encrypted backup by SQL server 2014.

SQL server 2014 can do this , right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you'll need SQL Server 2014. It's the only way to achieve what you want without using a 3rd party software.
0
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, good , very clear victor.

any Christmas plan ? I will keep taking photo and go with my boy.

what phone you are using ? last night I go to concert and test lumia 1520 and iphone 5s, I am not sure when iphone 5 s release, last year ? I buy my 1520 last year too !
0
Yashwant VishwakarmaSQL DBACommented:
I also get a clear idea about the compressed backup and encryption from this post.
Thank You Marrowyung & Vitor.
Have a great day ahead :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
yeah.. seems move to SQL 2014 with SP1 is a good decision .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.