SQL 2005 Vs 2008 Backups

I was searching through SQL central the other day and I came a cross a fantastic article on ‘SQL 2005 vs. SQL 2008 Part 1 (backup File Sizes & Times)’

So I thought for those that don’t subscribe this is fantastic information to share.

If you do subscribe find the article here:

http://www.sqlservercentral.com/articles/Compression/62746/

(I also urge to to subscribe its free and there are some fantastic resources.)

By Kevin van der Merwe, 2008/05/05

“Backup File Sizes

Databases today are growing bigger and bigger by the day and this seems to only be the start of what we can see in the near future with more content being accumulated and stored than ever before.

In SQL 2005 there was no capability of the engine to compress database backup files to disk at the time of the backup being executed.

In SQL 2008 there are 3 options while backing up databases, these are:

- No Compression (same as SQL 2005)
- Server Setting (if server backup compression setting is on/off – use this setting)
- Compression (Compress backup file during backup)

Scenario

We have a 3.5GB Database Backup File from a SQL 2005 Server Backup.

881

This 3.5GB Database was then restored to the SQL 2008 Server
(Note: No additional transaction activities on this database after restore)

After the Restore:

871

SQL 2008 Backup Options:

Backup without Compression:

This will be the same as the SQL 2005. The General Page does not have any visible changes as can be seen below:

879 The second Page – under Options has a new section titled “Compression” – here we get our first look at the options available:- Use the default server setting
- Compress Backup
- Do not compress backup

878 For this first option we are going to be using “Do not compress backup” CPU Activity during Backup without Compression:

877

Zoom in  |  Open in new window

CPU activity is for the entire backup process – averaging approximately 18% CPU usage. SQL 2005 BAK File Database SQL 2008 Backup File – no compress:

876 Note these are virtually identical in size – as the backup method used is the same for:
SQL 2008 and SQL 2005
Backup WITH Compression:The Options Page now looks like the following:

875 For this first option we are going to be using “Compress backup” The SQL 2008 Backup File Size – using the above setting:SQL 2008 – No Compression SQL 2008 with Compression

874 CPU Activity during Backup WITH Compression:

873 Note the CPU usage during backup with Compression is on average 35% – approximately double

File Size Summary

The SQL 2005 .BAK file that we created the Database with was 3.5GB
The SQL 2008 Backup without compression was 3.5GB
The SQL 2008 Backup with compression was 986MB. For this database the reduced space is 2.5GB; the compressed file is only 28% of the original size!

Note: Not all databases will have this compression factor or CPU usage due to systems and data types etc.

Backup Time Summary

The SQL 2008 Backup without compression took approx 6 minutes and the SQL 2008 Backup with compression took approx 3 minutes. You might be asking – how is that possible?

Well the CPU is used to compress the data before it is written to disk, so LESS data is written to disk. The less data that is required to be written to disk the faster this can be done by SQL Server and the operating system. 

Summary

With databases growing daily, the knock-on effect of this additional data has various cost and time factors. If you are managing a 100GB database, the speed of the backup window, which can now be reduced, is excellent news for DBAs.The size of the actual backup file that needs to be stored on disk and then archived to tape or preferred method is using up less space, which reduces costs ultimately.The quicker backups as well as smaller backup files are more than enough to warrant an investigation into SQL 2008, especially for VLDBs.

Until next time.

Kevin”

By Kevin van der Merwe, 2008/05/05

One Response to “SQL 2005 Vs 2008 Backups”

Leave a Reply