April 13, 2012 at 9:45 am
Does anyone know a way to estimate the savings we will get with backup compression? Specifically the backup size. I have a 2005 server and I'm trying to use this as one argument for pushing the upgrade.
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 13, 2012 at 1:16 pm
Probably the best way to figure that out would be to install developer edition, restore your database on it, then do a compressed backup of it.
Keep in mind that a compressed backup will use less I/O resources, but will require more CPU resources than an uncompressed backup.
The Redneck DBA
April 13, 2012 at 1:42 pm
It's a good idea Jason and one I may actually use. I thought I remembered a DMF that actually calculated the compression savings ahead of time, without having to go through the trouble of trying it out.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 13, 2012 at 1:49 pm
There is sp_estimate_data_compression_savings - but that estimates savings you will get if you turn on database compression. Not sure if the numbers there would give you a super accurate estimate of the backup compression you would obtain, but it might be something in the ballpark.
The Redneck DBA
April 13, 2012 at 2:44 pm
Thats what I was remembering, thanks! Unfortunatly it looks like that is for database compression, not backup compression.
Thanks anyway though 🙂
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 13, 2012 at 2:51 pm
The proc is for database compression and unfortunately it's only available in editions of SQL that support database compression (SQL 2008+, Enterprise, Developer and DataCenter edition). So either way, you'll have to restore a copy of the DB to a 2008 instance and test out there, and then it'll be easier just to run a compressed backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply