Here is the results after I started to use the With Compression option to do the backup
1. Downloaded a 4.4 GB ISO file
2. Clicked on it and Nero started automatically
3. Burned it into a DVD
4. Put the DVD in and Setup.exe was run
5. Clicked on a bunch of screen to confirm and finally Ready to Upgrade
6. Ran SSMS
7. SSMS shows SQL Server 2008 R2 at startup
8. Did some backup tests with the LevelExtreme and UniversalThread databases either at compress mode and non compress mode
The image 1 attached shows the information for the LevelExtreme database.
1. LevelExtreme.mdf is 90 MB
2. The compress backup is 11 MB
3. The normal backup is 84 MB
4. If I zip the compress backup, it doesn't save that much
5. If I zip the non compres backup, we end up with a small file
6. The ratio is almost 8 times smaller
The image 2 attached shows the information for the UniversalThread database.
1. UniversalThread.mdf is 9.4GMB
2. The compressed backup is 2.8 MB
3. The normal backup is 84 MB
4. If I zip the compress backup, it doesn't save that much
5. If I zip the non compress backup, we end up with a small file
6. The ratio is almost 3.5 times smaller
7. Using Winzip to zip the compress backup takes about 1 to 2 minutes
8. Using Winzip to zip the non compress backup takes about 20 to 30 minutes
9. The time to do a compress backup took 4.5 minutes
So, in overall, to obtain the smaller file as possible for the transfer, the previous method is still a winner. However, using the compress method will make it less intensive on the CPU as usually there would be a big process to backup and another one to Zip. For some kind of reason, Winzip detects the compress version and can process the zip file extremely fast.
So, in overall, if we are looking for a backup file, then the compress mode will make the backup file much smaller. But, if we target to use a backup file for transfer, then the non compress backup with WinZip will make the file a little bit smaller.
Despite the fact that the compress backup will make it a little bit longer on the FTP transfer time, it saves a lot on the CPU. Before, doing the regular backup, zipping the file and transferring it to a backup server (direct network disk transfer) was taking about an hour. Now, when using the compress backup, because Winzip recognizes that it is already compressd, the CPU time to achieve the same three steps would be about 40 minutes. But, the direct network disk transfer will be a little bit longer because we end up with a 2.8 GB file instead of 2.1.
Now, just to save about 25 to 28 minutes of CPU, I really do not know if it worths it to use the compress backup. That would have been the way to go if no FTP transfer would be a factor. But, because I have to consider the FTP transfer time, an additional 657 MB makes a big difference.
For those who wish to obtain a backup file on the server, then, I wouldn't even bother of using WinZip. Because, the difference between the WinZip version of the compress backup and the compress backup itself isn't worth mentioning it. So, basically, using the compress backup is good enough for archive purposes.
Maybe someone can add more information to this as to know if there would be a way of using the compress mode and obtain a smaller or even about the same file size as scenario #1, which is to do a non compress backup and zip it.
The tests were done on my Windows 7 Ultimate N 64 bits. It uses SQL Server 2008 R2 64 bits and Winzip 2006. I assume the Winzip 2006 is 32 bits. The PC has 8 GB of RAM. I expect the numbers to be about the same on the server.
