T-SQL and SQL Server Administration Articles Link List – January 2011

Here’s a quick wrap up of the articles published on www.SQLServerCurry.com in the month of January 2011

T-SQL Articles

Select TOP With TIES in SQL Server - The SQL Server TOP Expression returns only the first set of rows that is either a number or a percent of rows. If you want additional rows to be returned from the base result set if duplicate values exists in the qualified set of results, then use TOP..WITH TIES as shown in this post

Create Date in T-SQL - SQL Server - Here’s a very simple query that shows how to create a Date using T-SQL if you are given the Day, Month and Year

Rollback Transaction in SQL Server - In this article, I have explained how to use a Try..Catch block to commit and rollback transaction

Rollback Nested Transactions in Stored Procedure - SQL Server - In this article, we will use an example to see how to rollback nested transactions in Stored Procedures

MINUS Keyword in SQL Server – Alternatives - MINUS operator (in oracle) is used to subtract the rows which are available in the second result, from the first result set. SQL Server does not have a built-in MINUS keyword, but in SQL Server, it’s equivalent is the EXCEPT operator or using NOT EXISTS

Date Difference in SQL Server in Days, Hours, Minutes and Seconds - A lot of users often ask questions related to finding difference between two date. The results are expected in Days, Hours, Minute and Seconds. Here’s a query that shows how to find the difference between two dates in SQL Server

UNPIVOT example in SQL Server - The PIVOT operator allows you to rotate, or pivot, data between columns and rows, and performs aggregations where they are required on any remaining column values. The UNPIVOT operator performs the reverse operation of PIVOT, by rotating columns into rows.

SQL Server Administration Articles

Backup Compression in SQL Server 2008 - Backup Compression is a new database administration feature in SQL Server 2008 and higher which allows you to compress your database backup. In this article, I have listed some frequently asked questions (FAQ) about Backup Compression in SQL Server 2008.

Deny Delete on a Table to All Users Except a Few – SQL Server - I was speaking to a DBA friend of mine the other day on how do they Deny deletes on a table to all users, including admins. One of the solutions was to use a trigger as shown in this post

Find the Most Used Stored Procedures in SQL Server - Let us see how to use the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

Load Comma Delimited file (csv) in SQL Server - We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file. These are the two easy ways to import data from a CSV file into a table of a SQL Server Database – Using Bulk Insert and Using SQL Server Management Studio.

SQL Server: Export Table to CSV - Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

Rename a SQL Server database - Sometimes we may need to rename a SQL Server database for testing purposes or when a testing database goes online, we may need to rename it on a production database.

SQL Server General Topics

Create a Business Intelligence Solution in SQL Server 2008 R2 – Free Training - The January 2011 update of the SQL Server 2008 R2 Developers Training Kit contains a FREE Training course designed for developers who want to learn how to build their first Business Intelligence solution with SQL Server 2008 R2.

SQL Server Code-Named ‘Denali’ CTP1 – Books Online

I had recently posted on the Next Version of SQL Server ‘Denali’. Microsoft has released the Books Online for SQL Server ‘Denali’ CTP1 which contains the following

  • SQL Server ‘Denali’ Setup and upgrade instructions.
  • New features and descriptions of the technologies used in Denali
  • Tutorials to guide you through common tasks in SQL Server Denali
  • Reference documentation for the tools, utilities and APIs supported by SQL Server.
  • Descriptions of the sample databases and applications that are available with SQL Server.

Download Microsoft Books Online for SQL Server Code-Named "Denali" CTP1

Rename a SQL Server database

Sometimes we may need to rename a SQL Server database for testing purposes or when a testing database goes online, we may need to rename it on a production database.

There are two methods to do this. Let us first create a sample database

CREATE DATABASE testing

Method 1: Use sp_renamedb procedure

EXEC sp_renamedb 'testing','development'

The procedure sp_renamedb will rename the database from ‘testing’ to ‘development’

However this procedure may not be available in future release of SQL Server. So instead make use of the ALTER database command

ALTER DATABASE testing
MODIFY name=development

Alter Database

Method 2: Use SQL Server Management Studio

Right click on database and select rename and type the new name and hit Enter. The database will be renamed. The steps are shown below:

Rename Database

Rename Database

Rename Database

Rename Database

Best practices when renaming a database


1. Do not rename a production database without taking a backup
2. Make sure the database is not accessed by other applications
3. Do not use special characters as part of the database name

SQL Server: Export Table to CSV

Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

Create a test table

CREATE TABLE test(
empid varchar(6),
empname varchar(100),
dob datetime,
salary decimal(12,2)
)

Consider the following data

INSERT INTO test
SELECT 'EMP001','Suresh','19910619',3000
UNION ALL
SELECT 'EMP002','Ramesh','19710103',20000
UNION ALL
SELECT 'EMP003','Nilesh','19800722',4760
UNION ALL
SELECT 'EMP004','Kumar','19680911',42000

Method 1: Use bcp in command prompt

Usually when we use bcp to export data, by default column values are separated by a tab.
So we need to use a format file to make the column delimiter a comma instead of a tab.

Create a file named format.fmt in F drive (or whichever drive is available in your system)
with the following data. You can also download format.fmt over here

format.fmt

Over here, 9.0 refers to the SQL Server Edition (in this case SQL Server 2005) and 4 represents the number of rows (in this case 4 rows).

Now open the command prompt and use the following bcp command in it

bcp yourdb..test out D:\test.csv -T -f D:\format.fmt

The file named F:\test.csv will be created with values separated by comma.

Method 2: Use Management studio

Right click on the database and select Tasks > Export data

Select Data source as SQL Server, select the server name, authentication and database and click Next

Export Csv SSMS

Select Destination as Flat file Destination and browse for a .csv file and click Next

Export Csv SSMS

Export Csv SSMS

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

Export Csv SSMS

At the end it will show the details of the rows which got exported to .csv file.

Export Csv SSMS

Note: If you intend to do the reverse, i.e. Import .csv into a table, check my previous article Load Comma Delimited file (csv) in SQL Server

UNPIVOT example in SQL Server

The PIVOT operator allows you to rotate, or pivot, data between columns and rows, and performs aggregations where they are required on any remaining column values. The UNPIVOT operator performs the reverse operation of PIVOT, by rotating columns into rows.

Note: PIVOT and UNPIVOT operators are available in SQL Server 2005 and onwards.

Here’s an example of using the UNPIVOT operator to convert repeating columns of a table, into a more normalized form

Sample Denormalized Table

CREATE TABLE #Student
(
StudentID int ,
Marks1 float,
Marks2 float,
Marks3 float
)

INSERT INTO #Student VALUES (1, 5.6, 7.3, 4.2)
INSERT INTO #Student VALUES (2, 4.8, 7.9, 6.5)
INSERT INTO #Student VALUES (3, 6.8, 6.6, 8.9)
INSERT INTO #Student VALUES (4, 8.2, 9.3, 9.1)
INSERT INTO #Student VALUES (5, 6.2, 5.4, 4.4)

SELECT * FROM #Student

OUTPUT

Unpivot Example

Let us know normalize this table using an UNPIVOT operator

Here’s the query to try out

SELECT StudentID, MarksNo, MarksRecd
FROM
(SELECT StudentID,
Marks1, Marks2, Marks3
FROM #Student) stu
UNPIVOT
(MarksRecd FOR MarksNo IN (Marks1, Marks2, Marks3)
) AS mrks

As you can see, we have declared two new columns here – MarksNo and MarksRecd. Here the column that will contain the column values (the ones that will be rotated like Marks1, Marks2,...) will be called MarksNo , and the column that will hold the values that currently reside under the rotated columns, will be called MarksRecd. This rotation of columns into rows is carried out by the UNPIVOT operator.

OUTPUT

Create a Business Intelligence Solution in SQL Server 2008 R2 – Free Training

The January 2011 update of the SQL Server 2008 R2 Developers Training Kit contains a FREE Training course designed for developers who want to learn how to build their first Business Intelligence solution with SQL Server 2008 R2.

The highlights of this newly added BI course containing demos, presentations and hands on labs, is as shown below:

  • Build Your First Microsoft BI Solution with SQL Server 2008 R2
  • Introduction to BI Modeling Techniques
  • Introduction to SQL Server 2008 R2 Integration Services
  • Introduction to SQL Server 2008 R2 Reporting Services
  • Publishing and Accessing SQL Server 2008 R2 Reporting Services Reports
  • Introduction to SQL Server 2008 R2 Analysis Services
  • Introduction to SQL Server 2008 R2 Analysis Services Query Syntax
  • Accessing SQL Server 2008 R2 Analysis Services Data
  • Introduction to SharePoint 2010 PerformancePoint Services
  • Introduction to Data Mining with SQL Server 2008 R2 Analysis Services
  • Introduction to Self-Service Reporting and Analysis with SQL Server 2008 R2

Apart from the new BI course stated above, the training kit also contains many other demos, HOL and presentations to understand SQL Server 2008 and SQL Server 2008 R2 improvements from a developer perspective. It’s a great free asset for someone who wants to learn SQL Server 2008 R2 at his/her own pace.

Download SQL Server 2008 R2 Developers Training Kit

Load Comma Delimited file (csv) in SQL Server

We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file.

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

These are the two easy ways to import data from a CSV file into a table of a SQL Server Database – Using Bulk Insert and Using SQL Server Management Studio.

Consider the following data

1,test,89300
2,testing,52801
3,test,1000

Create a file name test.csv in your system and add the data shown above in that file

Create a test table

CREATE TABLE test(
id int,
names varchar(100),
amount decimal(12,2)
)

Method 1: Using Bulk Insert

bulk insert csv

Here’s the same query for you to try out

bulk insert test from 'F:\test.csv'
with
(
fieldterminator=',',
rowterminator='\n'
)

The above code reads data from the file located at F:\text.csv and splits data into different
columns based on the fieldterminator ‘,’ (comma) and into different rows based on the rowterminator '\n' (\n is for newline).

Now if you do a SELECT * FROM test you will get the following output

image

Method 2: Using SQL Server Management Studio

Right click on your database > Tasks > Import data

ssms import data

Select the datasource as Flat file. Select the file using the browse button or type the file path and name directly and click Next

ssms data source

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

csv rowcolumn delimiter

Select Destination as your server and select the database where the table exists. Click Next

ssms destination

The wizard will import the data and show you the details about the data which was imported

import export wizard

Date Difference in SQL Server in Days, Hours, Minutes and Seconds

A lot of users often ask questions related to finding difference between two date. The results are expected in Days, Hours, Minute and Seconds. Here’s a query that shows how to find the difference between two dates in SQL Server

Date Difference in SQL Server

Here’s the query in a readable format to try out

DECLARE @Startdate DATETIME, @Enddate DATETIME
SET @Startdate = '2011-01-02 11:35:26'
SET @Enddate = '2011-01-06 03:15:31'

-- Query by SqlServerCurry.com
-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60;

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate)

SELECT
CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

The query shown above is quite simple. We first calculate the total seconds in a day (86400) and store it in a variable. We then use the DATEDIFF function and calculate the difference between two dates in seconds and store it in another variable. Finally we use the CONVERT function to apply some calculations and get the difference between two dates in days, hours, minutes and seconds

OUTPUT

Date Difference in SQL Server

MINUS Keyword in SQL Server - Alternatives

MINUS operator (in oracle) is used to subtract the rows which are available in the second result, from the first result set. SQL Server does not have a built-in MINUS keyword, but in SQL Server, it’s equivalent is the EXCEPT operator or using NOT EXISTS

Here’s an example. Consider the following tables

DECLARE @table1 table(a int, b int)
DECLARE @table2 table(a int, b int)

TEST DATA

INSERT INTO @table1
SELECT 1 as a, 2 as b
UNION ALL
SELECT 1 as a, 2 as b
UNION ALL
SELECT 11 as a, 12 as b
UNION ALL
SELECT 13 as a, 12 as b

INSERT INTO @table2
SELECT 1 as a, 21 as b
UNION ALL
SELECT 1 as a, 12 as b
UNION ALL
SELECT 1 as a, 112 as b
UNION ALL
SELECT 13 as a, 12 as b

Using EXCEPT

SELECT * FROM @table1
EXCEPT
SELECT
* FROM @table2

Using NOT EXISTS

SELECT DISTINCT t1.* FROM @table1 as t1
WHERE NOT EXISTS
(
SELECT * from @table2 as t2
WHERE t1.a=t2.a and t1.b=t2.b)

NOT EXISTS also has the same functionality of EXCEPT operator i.e. retrieving the rows from the first table, which are not available in the second result set

OUTPUT

SQL Server MINUS 

You can also give specific columns in your query

OUTPUT

image

Read some more T-SQL Tips over here

Rollback Nested Transactions in Stored Procedure - SQL Server

In a previous article Rollback Transaction in SQL Server , l explained how to use a Try..Catch block to commit and rollback transaction in stored procedure. In this article, we will use the same example to see how to rollback nested transactions in Stored Procedures.

CREATE TABLE TT (num int)
GO
INSERT INTO TT(num) VALUES (50)
GO

-- Code from SQLServerCurry.com
-- Create First Proc with Transactions
CREATE PROC SP1
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (100)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

-- Create Second Proc with Transactions
-- Raise an Error in this Proc
CREATE PROC SP2
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (200)
RAISERROR('Manually raised error', 17, 1)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

-- Create Main Procedure
CREATE PROC MainProc
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (300)
EXEC SP1 -- Execute StoredProc 1
EXEC SP2 -- Execute StoredProc 2
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @@trancount
IF @@trancount > 0 ROLLBACK TRAN
-- Error Message
--DECLARE @Err nvarchar(1000)
--SET @Err = ERROR_MESSAGE()
RAISERROR ('Error in Proc',16,1)
END CATCH
END

GO

EXEC MainProc

SELECT * FROM TT

DROP TABLE TT
DROP PROC SP1
DROP PROC SP2
DROP PROC MAINProc

Shown above are two Stored Procedures (SP1 and SP2) with transaction processing enabled, nested within another stored procedure (Main Proc) which also has transaction processing. This leads to nested transaction. The proc SP2 manually raises an error, causing its transaction to rollback. On running the code above, you will get the following error

Transaction Error

which shows that an error in the nested stored procedure, causes a rollback, which in turn will roll back everything. Doing a SELECT * FROM TT shows that no new rows were inserted in the table.

Points to consider:

  • When you are using nested transactions, you must execute a COMMIT TRAN statement for each BEGIN TRAN statement issued, for the transaction to complete successfully
  • Although I haven’t done so, in complex nested transactions, you can check the value of @@trancount to see if it is active, before using another BEGIN TRAN. If it is active, you can use SAVE TRAN instead. If @@trancount is 0, you are no more in a transaction. You can print the value of @@@trancount in these stored procedures to see how its value changes.
  • If you are using SAVEPOINTS, I saw a very interesting point about nested transactions discussed in this forum. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

Note 1: Although the code example shown above runs, what I was originally attempting is to do is access the ERROR_MESSAGE() in the main procedure

However when I tried so, SQL Server fired an error

Msg 50000, Level 16, State 1, Procedure MainProc, Line 19
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I tried a couple of solutions for this misleading error message, including SET XACT_ABORT ON , however nothing worked! Finally removing the ERROR_MESSAGE() solved it. If anyone knows a solution to this problem, please post it in the comments section.

If you want to see a simpler example of using a Try..Catch block to commit and rollback transaction, check my article Rollback Transaction in Stored Procedures in SQL Server

Rollback Transaction in SQL Server

Commit and Rollback Transactions in SQL Server is a huge topic in itself. In this article, I will explain how to use a Try..Catch block to commit and rollback transaction. In subsequent articles, we will explore how to rollback nested transactions too.

Consider this example, where we will first write a T-SQL code which commits the transaction and adds new record in a table

CREATE TABLE TT (num int)
GO
CREATE PROC SP1
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TT(num) VALUES (630)
INSERT INTO TT(num) VALUES (890)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
-- Error Message
DECLARE @Err nvarchar(1000)
SET @Err = ERROR_MESSAGE()
RAISERROR (@Err,16,1)
END CATCH
GO
EXEC SP1

Run the code shown above and query the table

SELECT * FROM TT

You should get the following output

CommitTransaction

Let us now manually raise an error to see if it does a transaction rollback. Change the T-SQL Code as shown below to manually raise an error in the TRY block, which will cause an error and the transaction to rollback:

ROllBack transaction

On running this query, you get the following error

ROllBack transaction

Doing a SELECT * FROM TT still returns only two records, since the transaction was rollback and the new row never got inserted

Points to Consider:

  • You can set a savepoint/marker within a transaction using SAVE TRANSACTIONS. The savepoint defines a location in your code, to which a transaction can rollback if part of the transaction is conditionally canceled
  • If there are no savepoints defined, then in case of an error, a ROLLBACK TRANSACTION rolls back to the beginning of the transaction.
  • You can find out if a transaction is still active using SELECT @@trancount. The @@trancount function is used to monitor the current status of a transaction. When @@trancount > 0, this means that the transaction is still open and in progress. That is why we check the value of @@trancount > 0 in the catch block to make sure it is open and we can roll back the transaction.
  • The value of @@trancount is initially 0 to start with. When BEGIN TRANSACTION is executed, @@trancount gets incremented. When COMMIT TRANSACTION is executed, @@trancount gets decremented. When ROLLBACK TRANSACTION is executed, the transaction is canceled and @@trancount returns to 0.
  • A transaction cannot be rolled back once the COMMIT TRANSACTION statement is execute

Rollback Nested Transactions

You can have nested transactions in SQL Server. For example you can have a stored procedure with a BEGIN TRANSACTION statement, which invokes a stored procedure also containing a BEGIN TRANSACTION statement and so on. Now if an error occurs inside a child stored procedure, what happens to the parent transaction?

Stay tuned for my next article to know the answer! Update: Check my next article Rollback Nested Transactions in Stored Procedure - SQL Server

Create Date in T-SQL - SQL Server

Here’s a very simple query that shows how to create a Date using T-SQL if you are given the Day, Month and Year

Create Date T-SQL

OUTPUT

image

If you are given only the Day and Month, then here’s a nice query written by Peter Larsson:

DECLARE
@Day smallint = 24,
@Month smallint = 4

SELECT DATEADD(MONTH, 12 * YEAR(GETDATE())
+ @Month - 22801, @Day - 1)
As [Date]

OUTPUT

image

Find the Most Used Stored Procedures in SQL Server

In one of my earlier posts, I had mentioned about some Dynamic Management Views (DMV) for SQL Server Performance and Tuning. Out of these DMV’s, I find the sys.dm_exec_query_stats very useful. This DMV returns aggregate performance statistics for cached query plans.

One of the suggested methods to get information of the most executed code is to create a trace or use a tool that does that, and then query the results. However since SQL Server caches information over time, you can extract such information using the sys.dm_exec_query_stats

Let us see how to use the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

-- Query by SQLServerCurry.com
SELECT TOP 3 dest.text, deqs.execution_count,
deqs.total_worker_time, dest.objectid
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) dest
ORDER BY deqs.execution_count desc

Top Stored Procedures

As you can see, the DMV extracts the 3 most used stored procedure based on its execution count.

Note: If you execute this query on a live databases, the results may be inaccurate in the first run. The BOL says, “The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.” Hence is is advised to run the same query 2 or 3 times.

You can also see some more uses of the sys.dm_exec_query_stats DMV over here Find the Most Time Consuming Code in your SQL Server Database and Find Unused Objects in your SQL Server Database

Deny Delete on a Table to All Users Except a Few – SQL Server

I was speaking to a DBA friend of mine the other day on how do they Deny deletes on a table to all users, including admins. One of the solutions was to use a trigger as shown below:

SAMPLE TABLE

CREATE TABLE SitesTable (
ID int,
Name nvarchar(20)
)

INSERT SitesTable VALUES(1, 'SqlServerCurry.com')
INSERT SitesTable VALUES(2, 'DevCurry.com')
INSERT SitesTable VALUES(3, 'DotNetCurry.com')
GO

TRIGGER

-- Trigger by SqlServerCurry.com
CREATE TRIGGER noSitesDelete ON SitesTable
FOR DELETE
AS
IF USER_NAME() <> 'Sam'
ROLLBACK
RAISERROR('You cannot delete SitesTable!',16,1)
RETURN
GO

Now just fire the DELETE Query

DELETE FROM SitesTable
WHERE ID = 2

and you will get the following output

Deny Delete

As you can see, only Sam can delete rows from the table. Everyone else gets the error.

Note: Remember that the trigger will not be fired if a user truncates the table, instead of deleting rows. Moreover an admin can always delete the trigger itself. In case of Admins, the trigger shown above only prevents them from ‘accidentally’ deleting rows.

What approach do you adopt to deny all users from deleting data in a table?

Select TOP With TIES in SQL Server

The SQL Server TOP Expression returns only the first set of rows that is either a number or a percent of rows. If you want additional rows to be returned from the base result set if duplicate values exists in the qualified set of results, then use TOP..WITH TIES as shown in the example below:

USE Northwind
GO
-- TOP vs TOP WITH TIES
-- By SqlServerCurry.com

-- Using TOP Expression
SELECT TOP 3
OrderID, UnitPrice, Quantity
FROM [Order Details]
ORDER BY Quantity DESC

-- Using TOP WITH TIES
SELECT TOP 3 WITH TIES
OrderID, UnitPrice, Quantity
FROM [Order Details]
ORDER BY Quantity DESC

SQL TOP WITH TIES

Note: TOP..WITH TIES expression is available in SQL Server 2005 and later versions and can be specified only in SELECT statements and only if an ORDER BY clause is specified.

Backup Compression in SQL Server 2008

All SqlServerCurry.com readers, welcome to the year 2011!

I thought of starting this year with a relatively new and exciting topic – Backup Compression in SQL Server 2008! Backup Compression is a new database administration feature in SQL Server 2008 and higher which allows you to compress your database backup. In this article, I will list some frequently asked questions (FAQ) about Backup Compression in SQL Server 2008.

Advantages and Disadvantages of Backup Compression

Backup compression reduces the amount of data SQL Server 2008 has to write, thus leading to lower disk space consumption, faster backup speeds, quicker restores and quicker mirroring. Backup compression is a boon especially while mirroring your live databases!

However implement Backup compression with caution. The CPU usage becomes very high at times while the backup is being compressed. So always remember to run the backup in a low-priority session. Check this article for more info How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

Note: As of now, you cannot use Backup compression and Encryption at the same time. This is probably because encrypted data is already compressed. However RedGate’s SQL Backup is an awesome alternative.

Which Edition of SQL Server 2008 supports Backup Compression?

There seems to be some confusion amongst SQL Server users as to which version of SQL Server 2008 supports Backup Compression. Here are some pointers:

- If you are using SQL Server 2008, then Backup Compression is available only in SQL Server 2008 Enterprise Editions. However a compressed backup can be restored in any edition of SQL Server 2008.

- If you are using SQL Server 2008 R2, then Backup Compression is available in SQL Server 2008 R2 Standard, Enterprise and DataCenter Editions. A compressed backup can be restored in any edition of SQL Server 2008 and higher versions (R2).

How to Enable Backup Compression?

By default, backup compression is off i.e. ‘backup compression default’ is set to 0. However you can configure the server instance to compress all backups by default by using the following code Here we are using 1 to enable backup compression.

SQL Server Backup Compression

Once you have configured the server instance to compress all backups by default, you can override this option for specific backups using either WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement.

How to calculate Compression Ratio of Backups?

To calculate the compression ratio of a backup, divide the original ‘backup_size’ by the ‘compressed_backup_size’ column of the backupset history table, as shown below:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

The compression ratio of a compressed backup depends on the type of data you have compressed.