April 7, 2010 at 9:13 am
RESTORE HEADERONLY FROM DISK = 'c:\db1.bak'
There's a "compressed" column in here. Not that it matters, a restore is a restore.
April 7, 2010 at 9:26 am
Jason Shadonix (4/7/2010)
Steve Jones - Editor (4/7/2010)
this worked on my SS2K8 Dev editionBACKUP DATABASE db1 TO DISK = 'C:\db1.bak' WITH copy_only, COMPRESSION
I don't have COMPRESSION first in my list of options either and it works.
But that does bring up an interesting point...I don't know that there is a way to tell if a backup is compressed or not, other than comparing the backup size to that of a non-compressed backup and noticing the obvious size difference.
On the other hand, since it doesn't make a difference when it comes time to do a restore on a database, I suppose it doesn't really matter that much.
You could define a standard to use a different suffix with compressed backups (bck?). But that means the GUI will not list them as files available to restore from (you would have to manually enter the file).
But then shame on you for using the GUI! 🙂
Personally, apart from system database backups. either they are all compressed or none are!
---------------------------------------------------------------------
April 7, 2010 at 9:30 am
actually, there is an is_compressed column in msdb..backupmediaset
---------------------------------------------------------------------
April 7, 2010 at 9:31 am
Personally, apart from system database backups. either they are all compressed or none are!
Why do you say that? Is there a reason you wouldn't want to compress a backup of a system database?
The Redneck DBA
April 7, 2010 at 9:35 am
--to enable compressionsp_configure 'backup comp', 1
go
reconfigure
go
Just curious but I don't recall reading anything within the TechNet article that mentioned a hierarchy requirement when doing WITH command. Why compression must come first. I'm fine with it, just funny that it is not mentioned...or am I the exception:w00t:
Not that it matters since you are If you are overriding it in your backup statement, but if you are wanting to default the compression setting, I think it's 'backup compression default', and not 'backup comp'.
The Redneck DBA
April 7, 2010 at 9:37 am
Jason Shadonix (4/7/2010)
Personally, apart from system database backups. either they are all compressed or none are!
Why do you say that? Is there a reason you wouldn't want to compress a backup of a system database?
I know its silly, but I just cannot bring myself to muck about with system database backups in any way. If I ever start using SQL native compression (I don't have 2008 enterprise) maybe I will, but as the system databases are small, is it worth it?
---------------------------------------------------------------------
April 7, 2010 at 9:40 am
Jason Shadonix (4/7/2010)
--to enable compressionsp_configure 'backup comp', 1
go
reconfigure
go
Just curious but I don't recall reading anything within the TechNet article that mentioned a hierarchy requirement when doing WITH command. Why compression must come first. I'm fine with it, just funny that it is not mentioned...or am I the exception:w00t:
Not that it matters since you are If you are overriding it in your backup statement, but if you are wanting to default the compression setting, I think it's 'backup compression default', and not 'backup comp'.
as long as the option specified in sp_configure can be uniquely identified by the entry, you don't have to input the full name.
---------------------------------------------------------------------
April 7, 2010 at 10:06 am
as long as the option specified in sp_configure can be uniquely identified by the entry, you don't have to input the full name.
Neat! I didn't know that!
The Redneck DBA
April 7, 2010 at 10:26 am
I like the fact that you can restore a 2008 compressed backup on any edition of SQL Server 2008.
Much nicer than having to install LiteSpeed on a server in order to restore a database.
April 7, 2010 at 11:25 am
Jason Shadonix (4/7/2010)
Not that it matters since you are If you are overriding it in your backup statement, but if you are wanting to default the compression setting, I think it's 'backup compression default', and not 'backup comp'.
This is actually a shortcut feature in SQL. You do not have to type out the complete feature name.😀
http://sqlserverpedia.com/wiki/How_to_Use_SP_CONFIGURE#A_Shortcut_Tip
Which personally I think Microsoft stole this from Cisco :hehe:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 7, 2010 at 11:32 am
george sibbald (4/7/2010)
Jason Shadonix (4/7/2010)
Personally, apart from system database backups. either they are all compressed or none are!
Why do you say that? Is there a reason you wouldn't want to compress a backup of a system database?
I know its silly, but I just cannot bring myself to muck about with system database backups in any way. If I ever start using SQL native compression (I don't have 2008 enterprise) maybe I will, but as the system databases are small, is it worth it?
For the most part they are "smallish" databases, so you may have a good point about it not buying you much in normal cases. But I've seen some pretty big msdb databases after they have accumulated lots of job/backup history records. And though it probably isn't the best thing in the world, its not unheard of to have user tables in the master/msdb databases that get pretty big.
But more to the point...I think I would probably argue that in a DR situation, the system databases are no more or less important than the user databases, so if you for some reason don't totally trust your compression system (native, 3rd party, or otherwise) on the system databases, I don't think you should trust it for the user databases either.
The Redneck DBA
April 7, 2010 at 11:42 am
Jason Shadonix (4/7/2010)
george sibbald (4/7/2010)
Jason Shadonix (4/7/2010)
Personally, apart from system database backups. either they are all compressed or none are!
Why do you say that? Is there a reason you wouldn't want to compress a backup of a system database?
I know its silly, but I just cannot bring myself to muck about with system database backups in any way. If I ever start using SQL native compression (I don't have 2008 enterprise) maybe I will, but as the system databases are small, is it worth it?
For the most part they are "smallish" databases, so you may have a good point about it not buying you much in normal cases. But I've seen some pretty big msdb databases after they have accumulated lots of job/backup history records. And though it probably isn't the best thing in the world, its not unheard of to have user tables in the master/msdb databases that get pretty big.
But more to the point...I think I would probably argue that in a DR situation, the system databases are no more or less important than the user databases, so if you for some reason don't totally trust your compression system (native, 3rd party, or otherwise) on the system databases, I don't think you should trust it for the user databases either.
totally and utterly agree! As I say I'm just being way over cautious. But whilst I am using 3rd party tools it does give me greater flexibility with my system databases (no licensing issues, no changes to DR procedures mainly), and all of which I manage and keep small
---------------------------------------------------------------------
April 7, 2010 at 11:53 am
george sibbald (4/7/2010)
I know its silly, but I just cannot bring myself to muck about with system database backups in any way. If I ever start using SQL native compression (I don't have 2008 enterprise) maybe I will, but as the system databases are small, is it worth it?
msdb can grow, but it's not so much it's worth it as it doesn't hurt.
June 24, 2010 at 9:47 am
Hi, how are people finding the CPU hit when running compressed backups?
I tried to create a specific login, limited to max cpu 20%, to restrict the affect on CPU, but it seems to be the SQL Server service account that does all the work so my CPU hits 100%
June 24, 2010 at 10:42 am
The compression is a CPU hit, but if you look at what happens with SQL Backup (Red Gate) or Litespeed (Quest), there is a CPU jump as well. SQL uses all the resources it can, and balances that among requests.
The tradeoff is that IO is lower (and quicker) since you're writing less data.
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply