Tuesday, October 21, 2014

How to take compressed backup in SQL Server TIP #64

Problem:-  A good maintenance plan always said to take backup but sometimes it is very difficult to take backup of database just because of space constraint.

We take backup but we don’t have enough space available.

Solutions:  SQL server provided  a best way and give us solution of compressed backup. Now how to take compressed backup let see step by step here.

Step 1:-  Select the database whose backup you want. Just right click and select backup option as shown in below figure

Backup_step1

Step 2:-  Once you click the backup option an new screen will appear as shown below

Backup_step2

Step 3:-  Now click on  Add button as shown in above screen. Once you click on the add button a new screen will appear as shown below where you have to provide the path and file name of compressed backup file.

Backup_step3

Step 4:-   Once you provided the filename click OK to the button now click on the options menu on right hand you will get new tab option here in the last you will get compress option as shown in below figure with arrow. Select compress backup option in dropown

once this done click on OK button now you are good to go. Your compressed backup is done. and you will get backup complete message as shown below

backup_step5

Or else you can use following command

BACKUP DATABASE INDIANDOTNETDB
TO DISK = 'E:\MyCompressedBackup.bak'
WITH COMPRESSION ;

You will get compressed backup.

backup

I hope this will help you somewhere.

Enjoy!!!

RJ!!

Wednesday, October 15, 2014

Grouping Sets–Good to know feature TIP# 60

 

Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution.

Problem:-  Suppose , We want  an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination.

So, to resolve this problem a basic traditional way is to create 3 separate query and combine there result.

Solution:-  Now in SQL Server 2008 onwards we have a new feature for to achieve such problem which is called GROUPING SETS.

Lets understand this by an example.

I am taking here Adventureworks2012 database. Now we want total due amount on different basis  example

1) total due amount on Territory name and sales person basis

2) Total due amount on  Territory name

3) total due amount on sales person basis

4) total due amount on sales order date  basis

To achieve above  results we write following query

SELECT sod.OrderDate,
st.Name,
p.LastName + ','+ p.FirstName  As SalesMan,
SUM(sod.TotalDue) as totalDue
FROM [Sales].[SalesOrderHeader] sod
INNER JOIN [Sales].[SalesPerson] sp ON sp.BusinessEntityID = sod.SalesPersonID
INNER JOIN [HumanResources].[Employee] emp ON emp.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [Person].[Person] p ON p.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [sales].SalesTerritory st ON st.TerritoryID = sod.TerritoryID
GROUP BY GROUPING SETS (
   (st.Name,p.LastName + ',' +p.FirstName ),
   (st.Name),
   (p.LastName + ',' +p.FirstName ),
   (sod.OrderDate)
  
)
ORDER BY  st.Name,sod.OrderDate

Now when we run the query and we get results which we want.

GroupSetResult

I hope this may be help you some where.

Thanks !!!

RJ!!!