June 11, 2015 at 10:57 am
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch?
Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does this sound odd to anyone else or is the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
Kindest Regards,
Just say No to Facebook!June 11, 2015 at 12:25 pm
It can be. It really depends on the sort of data you have. I've created dummy databases with a single table that contained a gigabyte's worth of rows with the same value in them, and gotten hundreds of times reduction on that. It's obviously contrived to get ridiculous compression, but if your data is the right sort, compression can definitely be that effective.
https://msdn.microsoft.com/en-us/library/bb964719.aspx#CompressionRatio gives you an idea of some of the factors that facilitate or hinder backup compression.
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply