July 29, 2010 at 5:29 pm
Hi,
We have SQL Server 2008 SP1 x64 developer edition.
I have backed up a database of Size 30 GB with NO compression and it's created a 25 GB BAK file in 8:30 sec (8 mins and 30 seconds)
Query used:
BACKUP DATABASE [MyDB]
TO DISK = N'Z:\MyDB.BAK'
WITH NOFORMAT, NOINIT, NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MyDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'Z:\MyDB.BAK' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
I have backed up the same database with compression and it's created a 20 GB BAK file in 8:20 sec (8 mins and 20 seconds)
Query used:
BACKUP DATABASE MyDb
TO DISK = N'Z:\MyDb_Compression.bak' WITH NOFORMAT, NOINIT,
NAME = N'MyDb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MyDb' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDb' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDb'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'Z:\MyDb_Compression.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
Comments:
1. I did NOT see any decrease in time with compression (you can see that with compression, the backup finished 10 sec earlier)
2. I did see 75 to 85% of CPU usage while running backup with Compression
3. I did see that the BAK file size is compressed to 20 GB with compression where as with NO compression it was 25 GB. So we saved 5 GB of space using compression.
Questions:
1. With Compression, the backup time should decrease right?
2. Can we mention the Compression percentage like compression = 30% or 50% ?
3. From the link,http://technet.microsoft.com/en-us/library/bb964719.aspx, under Restrictions, it says that Compressed and uncompressed backups cannot co-exist in a media set. But in my case, I have backedup the Compressed and uncompressed backups to the same drive Z.
So is my case, the restriction applies or NOT?
4. How to make sure whether the bak file is compressed backup?
Please provide your inputs
Thanks
July 29, 2010 at 7:59 pm
The main factor driving compression rates (these aren't customisible), is the compressibility of the data. If your database is 25 GB of JPEGs, encrypted data (TDE!), or other such non-repeating data, then compression will be poor.
The main time saving for backup compression is due to reduced I/O activity. Since your backup sizes were about the same, there's little difference in the time. Additionally, if your database has incompressible data, then a lot of CPU cycles will be spent trying to compress something that cannot be compressed.
July 30, 2010 at 8:37 am
Yeah, sounds like there's binary data if you only compressed from 30 GB to 20 GB. I have seen compression rates as great as 90% depending upon the data contained within SQL. That database may not get much bang for the compression buck. But some DBs get some big boosts.
July 30, 2010 at 8:47 am
As the others have said, and adding personal observations. Most of my databases that I backup with compression see a 75% reduction in backup size and the time requirement is significantly faster than the prior method. We saw an improvement from 9hrs down to 30 minutes for our backups.
The reality though is that it will depend on the type of data in the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2010 at 9:48 am
The database that I tested the compression is a Share Point Content Database.
May be it contains data that cannot be compressed much! I will try the compression with non share Point database and see
how that works.
Thank you
July 30, 2010 at 12:11 pm
You're welcome. Sharepoint data would not be able to be compressed very much due to the type of data being stored in sharepoint.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2010 at 8:33 pm
Q3 - under Restrictions, it says that Compressed and uncompressed backups cannot co-exist in a media set.
By media set, the docs mean a backup file. Once a backup file is created containing only a compressed backup set, it cannot contain new uncompressed backup sets. Likewise, once a backup file is created containing a non-compressed backup set, it cannot contain new compressed backup sets.
Interestingly, once you create a media set containing a compressed backup set, newer backup sets will also be compressed even if you don't use the COMPRESSION keyword in your BACKUP command.
Q4 - How to make sure whether the bak file is compressed backup?
Use the RESTORE HEADERONLY command, and look in the 'Compressed' column e.g.
RESTORE HEADERONLY FROM DISK = 'Z:\MyDB.BAK'
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply