Compressing SQL Server Backup Files

Hello Database Professionals,

Starting with SQL Server 2008 Enterprise Edition we have the option of compressing backup files (this includes both BACKUP DATABASE and BACKUP LOG statements which mean not only database backups but log file backups will be compressed) to save disk space and also decrease the duration of the backup and restore operations. The backup compression was an enterprise edition only feature with SQL Server 2008 but with SQL Server 2008 R2 this feature is supported by standard and higher editions. So that was welcome news for me since I support number of SQL Server 2008 R2 Standard Edition.

I have been using T-SQL script to backup databases. The script checks the edition of the SQL Server and then builds the backup command to compress the backup file if it’s enterprise edition or just backups the database if it was a standard edition.

Before the end of 2011 I have completed deploying/upgrading to SQL Server 2008 R2. That meant that my script no longer needed to check the edition of SQL Server 2008 R2 since the compression is already supported in standard and higher editions. So I turned to global configurations and enable the backup compressions. I would like to remind the readers that enabling the backup compression creates additional CPU overhead which depends on you workload might impact your server performance. I suggest that you test your backup process and if necessary using Resource Governor (this is Enterprise Edition only feature) create a low priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs.

Let’s start with checking the backup compression setting for the server by using the script below.

USE master

SELECT *

FROM sys.configurations

WHERE name = 'backup compression default'

ORDER BY name

Let’s check the returned result for the value_in_use column. This shows us the running value curently in effect for this option and is_dynamic column tells us the changes take effect after the RECONFIGURE statement executed. Now it’s time to execute the below script to enable the backup compression since the value_in_use is 0 for the server I am currently working.

USE master

EXEC SP_CONFIGURE 'backup compression default',1

RECONFIGURE

Let’s check the value_in_use by executing the first select statement.

USE master

SELECT *

FROM sys.configurations

WHERE name = 'backup compression default'

Now we should see that the value_in_use is 1 and we don’t need to use the optional keyword COMPRESSION in the backup database command.

By default when backup is compressed checksums are performed to detect media corruptions but if for any reason you need, you can explicitly disable the compression by using NO_COMPRESSION keyword in your backup statement.

I have seen anywhere from 35% to 80% compression ration and backup duration decrease from several hours to 30-45 minutes for the databases I administor. Your mileage will vary and you should TEST, TEST, TEST and deploy your changes for your environments. Here is the link to find out more about backup database command in MSDN.

HTH,

Bulent

SQL Function QUOTENAME()

Hello all,

I support third party software databases at work. During the implementation of the application it needed to create a database to store the data. However the application created a database with a name that does not follow the rules for the format of identifiers. In my case we ended up with a database name '20120101_Survey'.

On the same server I have a custom built script that backups up all the databases at night. However, following the database creation my backup process failed and I got an alert about the process. Further looking into the problem I found out that there was a problem with the syntax. As soon as I see the statement I knew what the problem was but never thought about it while deploying the application and database. To keep the story short I used the SQL Server function called QUOTENAME() to fix the problem. Using the function in my custom script help me return the database names with delimiters so that backup process completes without error. Since then I have been using the function with all the object names. This simple function can prevent some headaches if a DBA needs to support 3rd party databases (SharePoint is also a good example because the SharePoint database names don't follow the rules of the identifier format). Here is the link to the msdn and below is simple statement that returns all the databases in your system with delimiters.

SELECT QUOTENAME(name) AS DelimitedDbName

FROM sys.databases

HTH,

Bulent